Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 80

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

  1. #41
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I added your comments and recommendations and unfortunately nothing changed. I can't seem to find a way to attached a copy of the db and text file. At least I don't I see an add attachment button, in this reply section. How can I get you the db and files?

  2. #42
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    FYI, there was a typo in the code. The line If intCount >= 2 Then SHOULD read If intCount >= 256 Then. I thought this was capture the first 255 columns and anything after would be inserted into the second table.

  3. #43
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Next to the "Post Quick Reply" Button under the quick reply box should be a Button called "Go Advanced", when you click that there should be a Section called "Additional Options" which has "Manage Attachments" with a "Manage Attachments" Button.

    I won't be able to handle an Access file with 2010 advanced veatures as I only have Access 2007.

  4. #44
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Ok let me see how I can get it in a previous format. I dont think you need a mdb format so I will see how to save as a 2007 accdb. I thought as long the db was a .accdb file 2007 and newer versions could open it.

  5. #45
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    OBP, good news by adding what you suggested to the code I see via the msgBox the data is being read from the text file. So, the file is opening, it is looping through each record, but nothing is being adding to the table. It loops to the end and exits the code. I have an ADO reference set in my library. Any idea what is preventing the records from being added?

  6. #46
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes that is good news indeed.
    So we now have 2 things to check the division of StrInput in to individual fileds and then the creation of the record.
    But before we do so, do the fields actually exist in the tables
    "tblImport1"
    "tblImport2"

    Do they have names?

    Is the text file definitely Tab delimited?
    Which is what Varsplit is looking for.

    You could also now try just putting StrInput straight in to a table with a memo field to ensure the data actually gets into the database.

  7. #47
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Yes, the tables have the true field headers. I imported the text file into two tables and deleted the records leaving only field names. tblImport1 having the first 200 fields and tblImport2 having the remaining 62.


    The file is a .txt extension but every record is separated by a comma. Is the file actually a .csv with .txt extension? Should I make it a csv instead of txt. I've been back and forth with it but like I said the txt format works just fine when < 255 fields.

    Thanks

  8. #48
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I did change this line varSplit = Split(strInput, vbTab, , vbBinaryCompare) to...

    varSplit = Split(strInput, ",") That was never going to work. Thanks for pointing out it was written for a delimited file.

    I think it is narrowed down to adding the records to the table.

  9. #49
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I don't think that will necessarily work either, it is not the Record Delimiter that Split is working with it is the Field Delimiter.
    So now we need to know what the Split Function is actualy doing.
    I would suggest that you now move the msgbox to below this line

    varSplit = Split(strInput, vbTab, , vbBinaryCompare).

    You can then add

    & " - " & varSplit

    to the end of the Msgbox

    I would then add another msgbox below that one and use this just for comparison

    Msgbox Left(strInput, 25)

    That will provide you with the lefthand first 25 characters to see if you can identify the Field Delimiter.
    You can of course make the 25 any value that you want to find the Delimiter.

    ps You could also use Msgbox Left(strInput, 25) & " - " & Len(strInput)
    The Len function will tell you how many characters are in each record found by strInput.

  10. #50
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I added the above and the msgBox gave me this Capture.JPG I would say the delimiter is a comma. It is running past the add record portion.

    Any idea why?

  11. #51
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I think that you are correct, which is probably why it is not adding anything.
    try changing this

    varSplit = Split(strInput, vbTab, , vbBinaryCompare)

    to

    varSplit = Split(strInput, ", ", , vbBinaryCompare)

    or

    varSplit = Split(strInput, ",", , vbBinaryCompare)

    or

    varSplit = Split(strInput, ", ", , CompareMethod.Text)

    or

    varSplit = Split(strInput, ",", , CompareMethod.Text)

  12. #52
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I made the changes you suggested. It still seems to get to the portion of code to start the loop and insert records but it skips it. I was able to make save a copy of import portion of the db in Access 2007. If the format doesn't work for you, just let me know.

    Thanks for any help.
    Attached Files Attached Files

  13. #53
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I have downloaded the DB and will get back to you when I have had a look.

  14. #54
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have identified the problem after making a few corrections to the VBA and have it putting data into the table.
    However the lineinput statement is importing the whole txt file as one record, it is not recognising the Record delimiter, so it puts in just one record of 40,000+ characters.
    It also crashes on field 151 with "record too large".
    I am not sure how to overcome this issue of just one record, other than to try and split it up, to do so I need to identify the record delimiter.
    Last edited by OBP; 05-22-2017 at 07:45 AM.

  15. #55
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have not found any reference to the line input not seperating the records, the problem being that if you have sufficient records it will even excede the character limit for string.
    However I may have a solution although a bit round about.
    Excel 2007 and above will import the file directly into Excel without any problems, there are 46 records.
    So depending on how often you or the users need to do this it is possible to import the data into Excel split the data on to 2 worksheets and then import them in to your Access tables.

    ps Interestingly the Excel version of VBA Line Input also cannot detect the Record Delimiters, even though the Data Import works fine, wierd indeed.

  16. #56
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Yes, this is strangely weird. Currently I can take the same file provided to you and it will import correctly into Access no issues whatsoever. The problem is it only inputs up to 255. Thus is why I was attempting to split the file into two tables and then combine the two tables by deleting out what isn't needed. The file is generated from an external interface as a .dat file. I have a import form setup. Using select file dialog syntax the user selects the file and the vba takes the .dat file and creates a temporary text file for import. I had to do those because unless I'm missing something Access can not import .dat files. This was all well and good until the file exceeded 255 columns. I can't uses .xlsx extension because it doesn't separate the records.

    Unfortunately importing to Excel, splitting it out to two worksheets, and then importing to Access is not an option for this user application. Is it possible to open an instance of Excel, import the data, and specify ranges of the excel worksheet I would like to import to Access? Can you upload what you have working from what you referenced in your previous post? I'd like to see what you did. Thanks for the feedback. I've got to get this sorted out very soon because I'm running out of time.

  17. #57
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, here is the database and the Excel workbook.
    The plot thickens as far as Excel is concerned, it happily imports the data manually and as a Recorded Macro, it also Manually Parses the data OK, but if you try and Macro record the Parsing of the data it crashes at 132 columns, which again is wierd.
    Why would it Parse manually but not allow a VBA Version?

    Te database form now has 3 buttons all doing the same thing with minor mods.
    The original button uses this code
    TestPos = InStr(strInput, Chr$(32))
    MsgBox intCount & " = " & Len(strInput) & " = " & TestPos & " = " & Left(strInput, TestPos)
    strInput = Right(strInput, Len(strInput) - TestPos)
    to trim the strinput (I have been trying to find what the file uses as the Record delimiter without success)
    It import the data in to table starting at character 3338 and then tells the second attempt has field 151 too long, but I fon't try to import 151?

    The second button uses the same code without those 3 lines and imports the Table headings.

    The third button tries to skip the first record to go on to the second, there is no second record, all the data is held in one.
    Attached Files Attached Files

  18. #58
    Hi andycl, sorry for the late reply. Please find code below (provided by OBP previously) that I am using currently and hope that it is helpful to you. Btw, I am using Access 2003 only.
    Private Sub Cmd_Trf_Record_Accepted_Click()
    Dim data As String, count As Integer, count2 As Integer, Start As Integer, finish As Integer, rstable As Object
    Dim recount As Integer, fname As String, start2 As Integer, records As Integer, rs As Object, x As Integer
    Dim first As Integer, fieldcount As Integer, rs2 As Object
    On Error GoTo errorcatch
    Set rstable = CurrentDb.OpenRecordset("PA_Accepted")
    Set rs = CurrentDb.OpenRecordset("tblone")
    fieldcount = rs.Fields.count
    rstable.MoveLast
    recount = rstable.RecordCount
    rstable.MoveFirst
    For records = 2 To recount
    x = x + 1
    rs.AddNew
    rstable.MoveNext
    count2 = 1
    data = rstable.Field1
    Start = 1
    For count = 1 To Len(data)
    If Mid(data, count, 1) = "|" Then
    fname = Mid(data, Start, (count - Start))
    Start = count + 1
    count2 = count2 + 1
    If Not IsNull(fname) And fname <> "" Then
    rs(count2 - 1).Value = fname
    Else
    rs(count2 - 1).Value = Null
    End If
    End If
    If count2 = fieldcount Then Exit For
    Next count
    rs.Update
    rs.Bookmark = rs.LastModified
    Set rs2 = CurrentDb.OpenRecordset("tbltwo")
    rs2.AddNew
    first = InStr(1, data, "|")

    count2 = 1
    rs2(count2 - 1).Value = Left(data, first - 1)
    start2 = Start
    For count = start2 To Len(data) - 1
    If Mid(data, count, 1) = "|" Then
    fname = Mid(data, start2, (count - start2))
    start2 = count + 1
    count2 = count2 + 1
    If Not IsNull(fname) And fname <> "" Then
    rs2(count2 - 1).Value = fname
    Else
    rs2(count2 - 1).Value = Null
    End If
    End If
    Next count
    'add Last Fields data
    fname = Right(data, ((count + 1) - start2))
    If Not IsNull(fname) And fname <> "" Then
    rs2(count2).Value = fname
    Else
    rs2(count2).Value = Null
    End If
    rs2.Update
    rs2.Bookmark = rs2.LastModified
    Next records
    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
    rstable.Close
    Set rstable = Nothing
    MsgBox "added " & x & " records"
    Exit Sub
    errorcatch:
    MsgBox records & " " & count & " " & count2 & " " & Err.Description & " " & fname
    Resume Next
    End Sub

  19. #59
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Winxmun, thanks for digging up my old code.
    Unfortunately I don't think it will help in this case as the "|" is not used in this txt file, it uses the comma for Field seperators, the biggest problem is that the Line Input does not bring the records in as records, just one lump of data, with no recognisable Record seperators, I have tried most of the usual ones
    newline
    linefeed
    newline + linefeed
    "¦"
    "|"
    Tab
    without success.
    But Excel can import it successfully, so it is recognising something that I can't find for the record delimiter.

  20. #60
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    testfile.txt in post #52 uses line feeds (vblf).


    But it also contains text data surrounded by double quotes that also contain commas, so to split the fields you would need to parse each line.

    You could try this...
    https://msdn.microsoft.com/en-us/library/ms974559.aspx

    ADO should be able to hold all of the fields but it still uses the Jet engine to do the reading so may not work.

    And getting Jet to run might be a pain.

    https://social.msdn.microsoft.com/Fo...tdataproviders


    I would go back and ask for the file to be changed to only include the required fields.
    Or use SQL Express or something to do the import and transfer data from there.

Posting Permissions

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