Access 2007 says text file has too many rows to import

[gcgoode]

I am trying to import a text file into excel and keep getting the message "One or more rows of data in your file contain too many characters to import. The maximum characters per row is 65000".

These are very large files column wise, but none of the rows come anywhere close to 65,000 characters. I think what is causing this error is the null values in the text file. Microsoft has a post claiming to fix for that issue here: https://support.microsoft.com/en-us/kb/872914. The only problem is that I have many text files to import and that code ask you to specify the exact path of the specific text file you are importing and the new one to create here:

strOrigFile = "<Full path of your original text file>"
    strNewFile = "<Full path of the new text file>"

.

Doesn't seem very time efficient.
Another way I can get around this is to import them to excel first and then to access. But, with all the large text files I need to import, this not only doubles my workload but also some of the files have too many columns to fit into a excel spreadsheet.


Has anyone else had a similar problem? Any ideas on what I should do here?

[ranman256]

how many columns are you importing?

[gcgoode]

Well it varies but some are easily 20,000+

[ssanfu]

Well it varies but some are easily 20,000+

You have text files with 20,000+ columns???

An Access table has a limit of 255 fields. Don't see how you can import a text file that has 20,000+ columns (fields).

What does you table structure in Access look like?
Can the text file data be broken up into multiple tables? (ie normalized?)

You may be forced to use VBA to open the text file and process the data.