Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 80 of 80

Thread: How to import a text file with more than 255 fields into Access

  1. #61
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    jonh, I thought I had tried vblf on it's own, but I obviously didn't, so thanks for the heads up.
    The solution that I provided to Winxmun will work using vblf instead of "|".
    I hope the OP comes back.
    The only problem I can foresee is if there are more than 65,000 characters in the file, because the file comes across as one Record when using Line Input.

  2. #62
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I'm hesitant to go down the path of using ADO to query the text file because of the Jet engine not playing nice on 64 bit machines and this application will be running on that. However it does look like I could query the fields I only desired and that would cut my field count less than 255 avoiding the import error we are facing. Thoughts? Thanks for the comments jonh

    I tried altering what winxmun provided (thanks by the way) and could not get the record delimiter working.

    If you change the file extension from .txt to .csv and open using it splits fields appropriately. Using DoCmd.TransferText acImportDelim doesn't all you to specify ranges for the import. Is there another way or does that go back to using ADO to query the file?

  3. #63
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    andycl, do not worry I will provide you with the VBA code today.
    There are 2 methods to do this, the first is to use a combination of Winxmun's code and yours, Winxman's to segment the data in to it's Records and your Split routine to parse it into fields. I do not like this method because of the limit it puts on the number of records in the text file exceeding the 65,000 character string length.
    Method 2, using Input takes me back to my original BASIC training in the 80s when I built databases where all data was input and output 1 character at a time.
    It might be a bit slower than line input but it will do it.

    If the owners of the data were to change the vblf to Carriage & returnlinefeed the line input would work OK.

    ps do you want to keep this data as 1 field "BFN-2-XM-046-0018/16, RFWCS NODE BUS INTERFACE" as jonh says, this combines " and , in one field and parsing with Split would split it in to
    "BFN-2-XM-046-0018/16,
    &
    RFWCS NODE BUS INTERFACE"

  4. #64
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    Here is what you want, it is quite fast considering it is bringing in the file a character at a time.
    You will have to review the key & indexed fields as I had to set some of them to Duplicates allowed.
    I think that the RecordID that I added should probably be an Autonumber field, otherwise at the moment each import will duplicate those numbers.
    Attached Files Attached Files

  5. #65
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Keeping it as one field is appropriate.

  6. #66
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I'm downloading what you posted right now to take a look. Thank you for doing this. I'll get back with you once I have went through what you have provided.

  7. #67
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    OBP, this is fantastic. I was able to use what you provided and incorporate it with the file dialog picker to allow the user to browse for to their file. Everything looks great and pulls in nicely to the tables. The only thing I noticed was tblImport1 is off by one column. Everything needs to shift one column to the right. I couldn't get it worked out. As far as a primary key goes, I tried setting RecordID to AutoNumber - No Duplicates and as you said that limits you to one import. The "location" number in tblImport1 is a unique number. There will never be a duplicate. Can I include that field in the tblImport2 import and use it as my primary key? I am already using the location number as a primary key in other tables. If I should have mentioned that earlier and it throws things off I apologize.

    Thanks again. I would have spent many more days coming to a solution.

  8. #68
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    Try this version, I think it incorporates what you discussed.
    Attached Files Attached Files

  9. #69
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    OBP, what you provided works great. It's functioning exactly how it should. I am going through the vb and trying to comment everything so I understand it thoroughly. I need to understand it line by line so I can revise it or explain it if necessary. I may end up up pinging you for some clarification on a few lines if that will work.

    I have one other text file with 282 fields so I would think I can use the same code and only have to change the keyfields number from 63 to 83 for the primary key in table 2.

    I appreciate your help.

  10. #70
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    andy, no problem, as you say you should be able to use the 83 column count.
    Let me know if you don't understand any of the code.
    There may be a few redundant Dim statements as I modified the original code quite a lot.

  11. #71
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I added comments to what I think is going and wanted to get your response for those as well as the lines not commented. Once again, thanks.

      Dim i As Integer
        Do Until EOF(1)
            xchar = Input(1, #1) 'Get one character
            If xchar = vbLf Then 'Linefeed start at next line
                reccount = reccount + 1 'not sure
                fieldcount = 1 'not sure
                reclength = 0 'not sure
                'MsgBox reccount
                'GoTo skip
                If reccount > 1 Then
                    With rst
                        .AddNew
                        For i = 1 To 200
                            .Fields(i) = fieldstring(i) 'not sure
                            fieldstring(i) = "" 'double quotes are around some of the records
                        Next i
                   .Update
                    End With
                    
                    With rst2
                        .AddNew
                        .Fields(63) = keyfield 'set primary key in tblImport2
                        For i = 1 To maxfields - 200 'write records to remaining fields storedd in tbl2
                            .Fields(i) = fieldstring(i + 200)
                            fieldstring(i + 200) = ""
                        Next i
                   .Update
                    End With
            End If
    'skip:
        Else
            If reccount > 0 Then
            x = x + 1
                If xchar = Chr(34) Then 'hand "" records
                    If first = 0 Then
                        first = 1 'not sure
                    Else
                        first = 0
                    End If
                    'MsgBox x
                End If
                'MsgBox x & " " & fieldcount & " - " & "first - " & first
    
    
                If first = 1 Then
                        fieldstring(fieldcount) = fieldstring(fieldcount) & xchar
                Else
                    If xchar <> "," Then fieldstring(fieldcount) = fieldstring(fieldcount) & xchar
                    If xchar = "," And first = 0 Then
                        fieldcount = fieldcount + 1
                        If fieldcount = 25 Then keyfield = fieldstring(24)
                End If
                End If
            End If
            maxfields = fieldcount
       End If

  12. #72
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    Dim i As Integer
    Do Until EOF(1)
    xchar = Input(1, #1) 'Get one character
    If xchar = vbLf Then 'Linefeed end of current record, process the transfer of data in to the table
    reccount = reccount + 1 'counts the number of records to ensure we actually get to the end of the file
    fieldcount = 1 'reset the count of how many fields have been processed
    reclength = 0 ' was used to measure the length of the record can be removed
    'MsgBox reccount for testing no longer needed
    'GoTo skip for testing no longer needed
    If reccount > 1 Then ' process if not heading record
    With rst ' process first table
    .AddNew
    For i = 1 To 200 ' count fields
    .Fields(i) = fieldstring(i) 'set table field to constructed array string (i)
    fieldstring(i) = "" 'resets all the fields back to nothing
    Next i
    .Update
    End With

    With rst2 'process second table
    .AddNew
    .Fields(63) = keyfield 'set primary key in tblImport2
    For i = 1 To maxfields - 200 'write records to remaining fields stored in tbl2
    .Fields(i) = fieldstring(i + 200) 'set table field to constructed array string (i plus the 200 already prcessed)
    fieldstring(i + 200) = "" ' reset all the fields back to nothing
    Next i
    .Update
    End With
    End If
    'skip: no longer used
    Else ' if no linefeed has been found add the single characters to the current array for the current record
    If reccount > 0 Then
    x = x + 1
    If xchar = Chr(34) Then 'test for a quote inside the array
    If first = 0 Then ' has the "first quote" flag already been set which means the quotes have now been opened ignore following commas
    first = 1 'if it has not been set then set it
    Else ' so the first quote flag has been set so reset it back to zero, this means the quote in the current array is closing the quotes process the following commas
    first = 0
    End If
    'MsgBox x was used to count character not needed
    End If
    'MsgBox x & " " & fieldcount & " - " & "first - " & first display processing data not needed


    If first = 1 Then ' quotes are now open ignore comma which is a Field Seperator
    fieldstring(fieldcount) = fieldstring(fieldcount) & xchar ' add character to the current array
    Else ' quotes not opened so take in to account commas
    If xchar <> "," Then fieldstring(fieldcount) = fieldstring(fieldcount) & xchar 'add character if not a comma, ie end of current field
    If xchar = "," And first = 0 Then ' if it is a comma and quotes are not open the increement the array counter
    fieldcount = fieldcount + 1 'increement the array counter
    If fieldcount = 25 Then keyfield = fieldstring(24) ' if the number of fields is now 25 it means the previous field was the keyfield so set a string with it's value
    End If
    End If
    End If
    maxfields = fieldcount ' store the number of fields process no longer needed
    End If

  13. #73
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Thanks for taking the time to do that. It helped out. For variable fieldstring(1000), is there a string limit? What actually is the limit of characters it can read and successfully import?

  14. #74
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    The string capacity is between 65,000 and 2billion characters so using the current import VBA this should be no problem at all as the strings are only used for the individual Fields, not the records or the overall file as the original one was.
    As there is a limit on the field size (255) when set to text if you do come across any larger than that you will have to change the field to type Memo.

  15. #75
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    A file with 500 plus records should even work if I understand what your are saying correctly. I am working on the second file to import. The primary key will be in the second table. Shouldn't I move .fields(PK record location) = keyfields up to the Add rst of the first tblImport? But by doing that there in nothing to add because the vba has not gotten to PK in the second set of records.

  16. #76
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    500 records will be fine.
    You will have to change the line of code that sets the key field string (fieldcount = 25) to reflect the number of the key field in the second table and then add the string to the first table key field after processing the second table.
    So you would need to move line

    .Fields(63) = keyfield 'set primary key in tblImport2

    to after the table 2 processing with this code

    With rst ' process first table
    .AddNew
    .Fields(whatever the key field is in table 1) = keyfield 'set primary key in tblImport1
    .Update
    End With

  17. #77
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Hey OBP, I am having an issue with importing a the second file (same data structure as the first). I moved the keyfield to after rst2 but it actually crashes in the first rst. I have tried a few different things but I receive Run-Time error 3265: Item cannot be found in the collection corresponding to name or ordinal. I've checked to make sure all field names in the file are also in the tables I am importing and do not see an issue. I will look again because the run-time error is usually because of omissions like that. If it is meaning something else, please inform me. Here's the code I have so you can see if I have something wrong. I appreciate any input.

    With rst 'process first table
    .AddNew
    For i = 1 To 200
    .Fields(i) = fieldstring(i)
    fieldstring(i) = ""
    Next i
    .Update
    End With

    With rst2 'process second table
    .AddNew
    For i = 1 To maxfields - 200
    .Fields(i) = fieldstring(i + 200)
    fieldstring(i + 200) = ""
    Next i
    .Update
    End With

    With rst
    .AddNew
    .Fields(200) = keyfield 'set primary key in tblImport2
    .Update
    End With
    End If
    Else
    If reccount > 0 Then
    x = x + 1
    If xchar = Chr(34) Then
    If first = 0 Then
    first = 1
    Else
    first = 0
    End If
    End If


    If first = 1 Then
    fieldstring(fieldcount) = fieldstring(fieldcount) & xchar
    Else
    If xchar <> "," Then fieldstring(fieldcount) = fieldstring(fieldcount) & xchar
    If xchar = "," And first = 0 Then
    fieldcount = fieldcount + 1
    If fieldcount = 84 Then keyfield = fieldstring(83)

    End If
    End If
    End If
    maxfields = fieldcount
    End If

  18. #78
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    Hello again.
    I had that a lot when I was working on the first version.
    I assume that you still have the error capture in place, if so before this line
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


    add in

    MsgBox "record - " & reccount & " field - " & i



    this will tell you what record and field is causing the crash.

  19. #79
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,059
    Location
    You could try a short cut and make the first table's transfer
    For i = 1 To 199
    or less than 199.

  20. #80
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,962
    Location
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •