Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only edit from post 3 you should do is the SetWarnings line. Disregard everything else in that post. After info in post 4 I basically started over. Sorry did not make that clear.



    Everything else that has to be done is in post 9.

    The two Option lines are 'should be' but the code will work without.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    removed to make the thread easier to read (misunderstanding).

  3. #18
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    The only edit from post 3 you should do is the SetWarnings line. Disregard everything else in that post. After info in post 4 I basically started over. Sorry did not make that clear.

    Everything else that has to be done is in post 9.

    The two Option lines are 'should be' but the code will work without.
    OK, I start again from the original file then.

  4. #19
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    Revised getdataintotable:
    Code:
    Public Function getdataintotable(rs1 As ADODB.Recordset, tablename1 As String)
    Dim strquery As String
    Dim no1 As Integer
    Dim count1 As Integer
    Dim count2 As Integer
    Dim count3 As Integer
    Dim temp() As String
    Dim strval As StringstrValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "',"
    For i = 0 To rs1.fields.count - 1
        no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
        If no1 > 0 Then
            count1 = count1 + 1
            strFields = strFields & "Valeur" & count1 & ","
            strValues = strValues & "'" & rs1.fields(i).Name & "',"
        End If
    Next
    strFields = Left(strFields, Len(strFields) - 1)
    strValues = Left(strValues, Len(strValues) - 1)
    CurrentDb.Execute "Insert into [" & tablename1 & "](ID, " & strFields & ") VALUES(" & strValues & ")"
    count1 = 0
    count2 = 0
    rs1.MoveFirst
    Do While Not rs1.EOF
        strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0).Value & "_" & rs1.fields(1).Value & "',"
        For i = 0 To rs1.fields.count - 1
            count1 = count1 + 1
            no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
            If no1 > 0 Then
                count2 = count2 + 1
                strval = Str(rs1.fields(count1 - 1).Value)
                temp = Split(strval, ".")
                If temp(0) <> " " Then
                    strquery = strquery & CInt(temp(0)) & ","
                Else
                    strquery = strquery & CInt(strval) & ","
                End If
            End If
        Next
        
        strquery = Left(strquery, Len(Trim(strquery)) - 1)
        count2 = count2 + 1
        count3 = 7 - count2
        For j = 1 To count3
            strquery = strquery & ", Null"
        Next
        strquery = strquery & ")"
        CurrentDb.Execute strquery
        count1 = 0
        count2 = 0
        count3 = 0
        rs1.MoveNext
    Loop
    createdb (tablename1)
    End Function
    When I replace the existing Public Function getdataintotable with your code, this line gets red :
    Dim strval As StringstrValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "',"


    Quote Originally Posted by June7 View Post
    Again, the public variable fields is not needed and can be removed from the header. Also, remove the fields argument from call to getdataintotable.
    z = getdataintotable(RS, tablename)
    Sorry, I don't know what this means.
    These are 2 different changes? Can you tell me precisely what I should paste and where to paste it please? (sorry, i am a newbie in access...)

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like carriage return got dropped when I pasted code in post. should be two lines:

    Dim strval As String
    strValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "',"

    Look at the very first line in the module. It is Dim fields As String, delete that line. Would be a good idea to put Option Compare Database there instead. This is the module header section.

    Use the Find tool to search for z. Change the line as instructed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #21
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    I did the changes and launched the test, but I got this error message :


    The problem is not the title length because I have also tested csv files with longer titles, and they were processed.
    I can't see any problem with invalid characters or punctuation.

    When I press the debug button, this line is yellow :
    RS.Open sqlquery, cN, adOpenKeyset, adLockOptimistic

    (in Function getData(path As String, fileName As String, tablename As String) As Integer)

  7. #22
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    In Valeur1... Valeur6, currently data is imported after removing ".".
    But the 2 decimals digits remain, so for example "15.63" becomes "1563" (it was supposed to become "15").
    Anyway, it's not a big deal, but in such a case, I'd rather keep "15.63", except if the access file size gets huge, but you seem to say it won't impact the size too much.

    I have tried this :
    Quote Originally Posted by June7 View Post
    If you want to save original value, code in red would be changed: strquery = strquery & rs1.fields(count1 - 1) & ","
    But it seems that the result is the same : the 2 decimals digits remain, so for example "15.63" becomes "1563".

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So close.

    Don't know why it doesn't like that file name. I made a copy of one of the files and renamed it with that name. I get the same error. I removed every space from the file name and don't get the error. I put all the spaces back and get the error. Then I tested removing one space at a time until the error did not appear. I had to remove 4 spaces and doesn't seem to matter which ones. That leaves 8 spaces in the name. No error.

    Used to be a time when spaces were not allowed in file names. Even though they are allowed now doesn't mean they are a good idea.

    Don't know why you are getting 1563. I get 15.63.

    Provide a copy of your revised db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Here is the converter with the changes I made

    I am reading and rereading your post regarding the spaces in the name, trying to understand what might happen... and i don't get it.
    You think it's because of the number of spaces in the name?
    Other files have been successfully processed with names that look like this one.

    Maybe one formula in the code of the converter causes this issue chen there are many spaces?
    What about this line in yellow : doesn't it tell where the issue occurs?
    Attached Files Attached Files
    Last edited by nicoboss; 09-26-2017 at 03:41 AM.

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Downloaded the db. Open and run the code. All values have decimal point. I used the CSV files you provided. Deleted a bunch of data so process was faster.

    Yes, that is the code that is complaining about the file name. Again, no idea why.

    I don't get it either. Can't explain why that file name causes issue. As I said, spaces bad regardless that they are allowed. BTW, I see 10 spaces in that new example. Did a test with that name and no error. Tried adding the vs Fish in the name and it errors. Removed vs Fish and added 2 random spaces - no error. Left the spaces and put vs Fish back - error. Tried XXXX in place of Fish, still errors.

    Code is fixed. It runs properly for the data provided. I can only suggest changing file name structure to eliminate spaces.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #26
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    Downloaded the db. Open and run the code. All values have decimal point. I used the CSV files you provided.
    I don't get it, I have just done the same : downloaded the attached file, open and run and I get the data with the "." missing.

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Neither do I. Think I've done all I can from here.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Thank you very much June7 !

    You helped me a lot!

  14. #29
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    I realize it takes a long time for each file, like 10 mn for a 50 MB csv file. If I import manually such a table in access, it would take a few seconds.
    Do you know why?

    Is there a process (like for example try to round the data or trying to remove the ".", etc... or converting from number to text...) which might explain that?

  15. #30
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code does not round nor remove "." (still don't understand why that happens for you).

    As for conversion, the data is text in the CSV and text in the table. Could try changing one line to include apostrophe delimiters and see if that helps but I doubt it:

    strquery = strquery & "'" & rs1.fields(count1 - 1) & "',"

    It's the nature of manipulating text file in looping code that is slow.

    It is possible to automate the 'manual' import. Then code can modify the tables.

    Why do you want the CSV column headers as a record in table and not as field names for table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-22-2016, 02:37 AM
  2. Replies: 20
    Last Post: 10-13-2015, 09:05 AM
  3. Replies: 3
    Last Post: 10-16-2014, 08:49 AM
  4. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  5. Replies: 1
    Last Post: 05-04-2013, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums