Text File Import

[jstevens]

I am having a challenge importing a text file. What I would like to do is import each line of text without any parsing.

The two import format options are delimited or fixed width. I believe Access is looking for a search key for parsing and when it does not find one a error message appears: The search key was not found in any record.

Your suggestions are appreciated,
John

[Pat]

What that error normally means is a space is preceding a column name In the Excel sheet you are importing.

[jstevens]

Is it possible to import a text file where the delimiter is the "end-of-line"?

Each records are not delimited by a tab, comma and etc..

Regards,
John

[Rudi]

Hi John,

Delimiters are usually characters such as tabs, semi-colons, commas, space character, or any custom character you specify. The result of specifying a delimiter is that the data will be split into multiple columns for each row of text when that delimiter is encountered. When you say end of line that is not a character, or maybe there is a specific character that the end of each line that you can identify which can act a s a delimiter. You don't provide much for us to go on here to assist. Maybe you can upload a sample of your text file and help us understand what you plan to import.

A link that could be of some help? http://www.datawright.com.au/access_res ... _files.htm

[jstevens]

Rudi,

Here is a sample of the text I'm trying to import.

EL_Sample.png

I have also attached a text file.

EL_Sample.txt

Regards,
John

[Rudi]

I assume that you want to import it into a new table?
If I import it as text and fixed width, and then remove all the breaks, it will import each string into its own row (record)
Is this ultimately what you want to achieve?

1.jpg

[jstevens]

Rudi,

Yes that is what I would like to achieve. The number of line records is quite large and there must be something else in the file that is causing the import challenge.

Thanks for taking the time. I'm going to break the file down into smaller files and process each one to determine where the issue is.

Regards,
John

[Pat]

How many records are in the text file?

[jstevens]

Pat,

Around 22,000,000 records are in the file

Regards,
John

[Pat]

What you could do is to read from the text file one record at a time and write them to a table.
This takes a bit of VBA code to accomplish.

On further investigation it is probably too large for an access database of 2Gb.

22,000,000 x 255 = 5,100,000,000 or 5.1Gb