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

[winxmun]

I have a text file with 267 coloumns. When trying to import via access, the coloumn will be auto truncated at from 256 coloumn onwards. Anyone can advise on how to import all coloumns from these text file into 2 tables in the Access? tks in advance. :doh:

[OTWarrior]

Firstly, why would you have columns in a text file? Why not in Excel?

Also, why do you have so many columns? How many rows are there in this text file?

Have you tried copying the information into excel and importing it?

[winxmun]

Firstly, why would you have columns in a text file? Why not in Excel?
Because this is an auto downloaded file from an application system.

Also, why do you have so many columns? How many rows are there in this text file?
Actually it is my mistake for not knowing that the columns requested is more than 255. It is too late to change the request to reduce the columns. As for rows is unpredictable, may be will hit up to 10000 rows.

Have you tried copying the information into excel and importing it?
Sorry, dont really understand your question. However i've tried using the import text wizard in excel to import the data. I need to import 2 times, ie manually exclude the fields that is more than 255 for the 1st import, then exclude the first 255 fields for the 2nd import.

[Carl A]

Another way of doing this is to make two copies of your text file and link to the first text file retrieving the fields you want to the first table and then link to the second text file and retrieve the remainder for the second table. HTH

[Charlize]

Use two tables who are relative to each other (if possible). Let's say column 1,2,3 is customerid, customername, customer address and 4, 5, 6 is some other data. If the customerid is not present in first table, add 1,2 and 3 to first table and the second table add only the customerid, skip 2 and 3 and add 4, 5, 6.

Charlize

[stanl]

If the text file has a header row and the fields are delimited why not use Jet 4.0 and write 2 SELECT INTO.... queries using the Text Driver... this permits selecting both the fields and order they appear in the output tables. .02 Stan

[winxmun]

Hi all, tks for all the suggestion. But pls excuse me for being asking stupid question, ie how can i automate this importing via access? Reason: this will be my daily process so it will be good if the process is automated. tks. :doh:

Hi Stanl, may i know how to use Jet 4.0 that you've mentioned? Can you guide me because i am not very good in Access programming. tks a lot. :doh:

[OBP]

You can use VBA to automate the import of the text File.
But you need to create 2 "Import Specifications" to handle which Fields you actually want. It sound like you do not actually want all of the data (Actually it is my mistake for not knowing that the columns requested is more than 255).
Do you actually need all the Fields?
Have you looked at "Import Specifications for Text Files"?

[winxmun]

Yes, i need all the fields.
May i know how to create "Import Specifications"?

tks!

[OBP]

Look at the Main Menu>File>Get External Data>Import and when you select File Type "Text" and then you select a File and it shows the data, there is an "Advanced" button on the bottom left that takes you to the Specification where you can "Skip" fields. You can also save the specification it this point for future use with VBA code or manual Import.

[winxmun]

ok, will try. tks OBP...

[winxmun]

Hi OBP,

I hit problem.

Thought of import the text file 2 times, ie 1st file import the max 255 fields & 2nd file import the remaining fields. However, i cannot view all fields when trying to import. Then how do i skip fields? I hv attached the sample text file & error screenshot. Hope to hear fm you soon! tks.

[OBP]

winxmun, I have looked at your data.
Import it into one field and I will create you some VBA code to split out what you need from the Memo field that will be created.

[winxmun]

Hi OBP, sorry to reply so late because was very busy recently.
Are you saying that to copy all the fields fm the text file into one cell in Excel?
Currently I am manually importing the text file into 2 worksheet via Excel. However i would prefer to auto import the text file via Access because i have all my reporting/ programming is in the Access file. tks.

[OBP]

No, not Excel.
Import the whole file in to one Access Memo field.

[winxmun]

Hi OBP,
I've imported the 2 text files into one Access Memo field. Tks.

[OBP]

Ok, which parts do want to keep and which parts discarded?

Are you sure that the data has been transferred, it only appears to have headings in those Fields?

can you email me the fiel or files?

[winxmun]

Hi OBP,
I need the whole files. Can you split into 2 tables during the automation.
Currently, the file contains header only. No data has been transferred yet.
Do you mean the db file or text file?. tks.

[OBP]

I have analyzed the Post_Approval_Accepted Headers and there are 264, you could split this in to 2 tables of 132 each. There are 267 field headers in the Post_Approval_Disbursed Table. They could be split 133 and 134.
Do you want to create those tables manually, or have VBA create them?
I now need the data to work with to create the VBA to put values in the Table's Fields.

[winxmun]

Hi OBP,

I've re-attached a new db file with data. There are 267 fields for both tables. No problem to split into 2 tables of 133 and 134. I would prefer to have VBA code to create them.

tks a lot!:thumb

[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.:doh:

[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!!! :beerchug:
You are great! :thumb

[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 19101 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?

[andycl]

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?

[andycl]

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.

[OBP]

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.

[andycl]

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.

[andycl]

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?

[OBP]

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.

[andycl]

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

[andycl]

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.

[OBP]

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.

[andycl]

I added the above and the msgBox gave me this 19203 I would say the delimiter is a comma. It is running past the add record portion.

Any idea why?

[OBP]

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)

[andycl]

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.

[OBP]

OK, I have downloaded the DB and will get back to you when I have had a look.

[OBP]

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.

[OBP]

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.

[andycl]

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.

[OBP]

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.

[winxmun]

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

[OBP]

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.

[jonh]

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/Forums/en-US/d5b29496-d6a1-4ecf-b1a4-5550d80b84b6/microsoftjetoledb40-32bit-and-64bit?forum=adodotnetdataproviders


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.

[OBP]

jonh, I thought I had tried vblf on it's own, but I obviously didn't, :crying: so thanks for the heads up.:clap:
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.

[andycl]

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?

[OBP]

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"

[OBP]

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.

[andycl]

Keeping it as one field is appropriate.

[andycl]

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.

[andycl]

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.

[OBP]

Try this version, I think it incorporates what you discussed.

[andycl]

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.

[OBP]

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.

[andycl]

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

[OBP]

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

[andycl]

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?

[OBP]

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.

[andycl]

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.

[OBP]

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

[andycl]

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

[OBP]

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.

[OBP]

You could try a short cut and make the first table's transfer
For i = 1 To 199
or less than 199.

[SamT]

Also see thread File Dialog-Browse/Save/Append (http://www.vbaexpress.com/forum/showthread.php?59949-File-Dialog-Browse-Save-Append)