Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 80

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

  1. #21
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This will take me a While.

  2. #22
    no prob! tks!

  3. #23
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Hello, I have the Table Creation routine working fine.
    Are the names of the Tables that you provided the names you want to use for the tables?
    Is the data going to be different in the 2 tables, as it appears to be the same in those that you supplied.

  4. #24
    Quote Originally Posted by OBP
    Hello, I have the Table Creation routine working fine.
    Are the names of the Tables that you provided the names you want to use for the tables? Yes

    Is the data going to be different in the 2 tables, as it appears to be the same in those that you supplied.
    Yes, sometime the data may be same.

  5. #25
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Ok, here is the database, it has a Form with 2 Command Buttons.
    The first Button takes the data from the table "Post_Approval_Accepted" and it uses the first record to create 2 new tables called tblone and tbltwo.
    The second Button then transfers the second record which is data and splits it between the 2 tables as record 1 in each.

    The 2 records are on the form so that you can see the data held in the 2 records it is going to work with.
    The tables have already been created in this version, so you need to delete them before clicking the first Button.

  6. #26
    Hi OBP,
    I've added one more record to test the program. However, I've encountered error message when trying to transfer records, the error msg: "Object variable or With Block variable not set". Attached a new database for your assistance. tks a lot!

    Btw, can i check the following:
    1. How to have the 1st field (Loan No) to appear in the 2 new tables? Because 1st field is my primary key.
    2. How to have Null value (instead of "-") if the original field is empty? I've tried to change your coding from "-" to "", it doesnt work.

  7. #27
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I deliberately used the "-" instead of Null values as it shows that all of the Fields have been translated, but I have now set it to "".
    I have added the LoanNo field to the 2nd table in place of the Autonumber field.

  8. #28
    Hi OBP,
    Yeh! Successfully downloaded! tks a lot!!!
    You are great!

  9. #29
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    9 years after the fact when you posted this and I run across it. I'm in the same situation as the subject of this thread. You stated in this post you uploaded a db. Where can I access that? I'm in need of performing the same type of import. 262 columns in a delimited text file. Looking to to split into two tables and once in Access delete out what I do not need. I've tried and tried and am coming up short. I hope you receive this. Thanks for any help.

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Hello, I haven't been on VBAX for quite some time, I have reviewed the origiinal request and my responses.
    I no longer have a copy of that database
    Do you understand the gist of my answers?
    Can you do the same thing with your file and split it?
    What version of Access are you working with?

    ps I have found a couple of Importing and parsing databases, but I am not sure if one of them is the one that you want.
    Last edited by OBP; 05-05-2017 at 06:32 AM.

  11. #31
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    OBP, thanks for getting back with me. I wasn't expecting you to have a copy of the db but I figured I'd ask in hopes to get lucky.
    I'm working with Access 2010. From what I have read in this thread, it seems you performed exactly what I'm trying to do. I created two tables containing only field names. tblImport1 - 200 fields and tblImport2 - 62 fields. Ive tried modifying an existing solution I found and the code runs to the end as the msgBox is executed; however, no data is imported into the tables. When I step through it, I believe the file is being opened but maybe it isn't. Here is the code below. Any comments or suggestions are appreciated. Thanks

    Option Compare Database
    
    
       Public Sub Import()
       ' to use the ADODB.Recordset, be sure you have a reference set to ADO
       Dim rst As ADODB.Recordset
       Dim rst2 As ADODB.Recordset
       Dim strFile As String
       Dim strInput As String
       Dim varSplit As Variant
       Dim intCount As Integer
    
    
       Set rst = New ADODB.Recordset
       Set rst2 = New ADODB.Recordset
       ' CHANGE THE TABLE NAME HERE
       rst.Open "tblImport1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
       rst2.Open "tblImport2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
       ' CHANGE THE TEXT FILE NAME AND LOCATION HERE
       strFile = "D:\Users\Cox\Desktop\testfile.txt"
    
    
       Open strFile For Input As #1
    
    
       Dim i As Integer
       Dim n As Long
    
    
       Do Until EOF(1)
           ' This counter is just to get to the applicable line before importing
           intCount = intCount + 1
           ' reads the text file line by line
           Line Input #1, strInput
           ' starts importing on the second line.  Change the number to match which line you
           ' want to start importing from
           If intCount >= 2 Then
           n = n + 1
               ' creates a single dimension array using the split function
               varSplit = Split(strInput, vbTab, , vbBinaryCompare)
               ' adds the record
               With rst
                   .AddNew
                   .Fields(0) = n
                    For i = 1 To 200
                        If Nz(varSplit(i - 1), "") = "" Then
                        .Fields(i) = Null
                        Else
                        .Fields(i) = varSplit(i - 1)
                        End If
                    Next i
                   .Update
                   '.MoveNext 'I don't think you should need this
               End With
               With rst2
                    .AddNew
                    .Fields(0) = n
                    .Fields(1) = varSplit(0)
                    For i = 201 To 264
                        If Nz(varSplit(i - 1), "") = "" Then
                        .Fields(i - 199) = Null
                        Else
                        .Fields(i - 199) = varSplit(i - 1)
                        End If
                    Next i
                    .Update
                End With
           End If
       Loop
       ' garbage collection
       Close #1
       rst.Close
       Set rst = Nothing
       rst2.Close
       Set rst2 = Nothing
    
    
    MsgBox "Successful file import.", vbInformation
    
    
    End Sub

  12. #32
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The interesting thing is you can import the data first using this kind of code (change the table & file names obviously)

    DoCmd.TransferText acImportDelim, , "temptable2", "C:\Access\TEST.TXT", True
    temptable2 is a table with just one memo field in it.

    Once the data is in the table you can then use your VBA to parse the data in to the fields into the new table(s)

    Can you try that code to see if it works for you.

  13. #33
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I created temptable with only one field (Import) set to memo. Used the command statement provided but import failed. Received run-time error 2391 - table does not contain....It listed out header names in the text file. Set "hasfieldnames" to false in the command statement but same error but listed out F1, F2, and so on. Any thoughts?

    If this does work, what is the field size for a memo field?

  14. #34
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The memo field will hold 65,536 characters.
    I have no idea why it won't work for you when it did for me and other users.
    So let's see if we can find out if your VBA code is working on the Import part.
    After this line add this line of code

    Line Input #1, strInput

    Msgbox strInput

    That should attempt to display the Data in the string.

    If that doesn't work we can input a character at a time to see if that works using

    Input #1, strInput

    instead if Line input.

  15. #35
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The memo field will hold 65,536 characters.
    I have no idea why it won't work for you when it did for me and other users.
    Did you try

    DoCmd.TransferText acImportDelim, , "temptable2", "C:\Access\TEST.TXT", False


    So let's see if we can find out if your VBA code is working on the Import part.
    After this line add this line of code

    Line Input #1, strInput

    Msgbox strInput

    That should attempt to display the Data in the string.

    If that doesn't work we can input a character at a time to see if that works using

    Input #1, strInput

    instead if Line input.

  16. #36
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I get this error message Error.JPG when trying to import everything into one memo field.

    As far as the VBA import, I added the msgBox and a string of data was displayed. It displayed the column header names in the text file. It didn't have any of the data from the file only column headers.

    Thanks for your help thus far. It can't be too far away if its reading the file. Something just off in the loop portion.

  17. #37
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, so when you press OK does the Msgbox display the next line of input data?

    You could also add your counter n to the msgbox ie

    Msgbox n & " = " & strInput

  18. #38
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, so when you press OK does the Msgbox display the next line of input data?

    If intCount >= 2 Then

    You could also add your counter n to the msgbox ie
    Msgbox n & " = " & strInput

  19. #39
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    When I click ok, the code runs to end and displays the last msgBox "Successful Import". I added the counter n as you suggested. With that being added, the msgBox displays the column headers as it previously was but now there is 0 = followed by the column header names. It's almsot like it's reading the header names but nothing further. I don't know what to try.

  20. #40
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    If you only click once and it exits the loop something is triggering it.
    Can I suggest making a few simple changes, first of all add an Error trap.

    After your Dim Statements add

    On Error goto errortrap

    before End Sub add

    Exit sub

    errortrap:

    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description



    Now add intCount to your Msgbox

    Msgbox n & " - " & intCount & " = " & strInput

    Now move the n = n+1 to above the line


    If intCount >= 2 Then

    At the moment intCount should increment, but n will only increment if intCount is 2 or greater so by moving it we will see if both of the increment..

    Would it be possible to send me a copy of the database and the text files?

Posting Permissions

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