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

[OBP]

This will take me a While.

[winxmun]

no prob! tks!

[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?
Is the data going to be different in the 2 tables, as it appears to be the same in those that you supplied.

[winxmun]

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.

[OBP]

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.

[winxmun]

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.

[OBP]

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.

[winxmun]

Hi OBP,
Yeh! Successfully downloaded! tks a lot!!!
You are great!

[andycl]

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.

[OBP]

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.

[andycl]

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

[OBP]

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.

[andycl]

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?

[OBP]

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.

[OBP]

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.

[andycl]

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.

[OBP]

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

[OBP]

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

[andycl]

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.

[OBP]

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?