Importing .txt file with >255 columns into Access

[RLG]

Hi Guys,

Can anyone tell me how to get a large .txt file into Access. I know it has too many columns so I selected about 30 columns that I don't need to be 'skipped'. However it is just giving me the error that my file has more than 255 columns - with the 30 selected for skip - it should have about 230 columns.
Any ideas?
Thanks


RLG

[June7]

Options:

1. edit the text file to remove the unneeded data (maybe use Excel) then use import wizard

2. use VBA code to open the text file as an object and manipulate the object - read in one line at a time, parse the string to an array, loop the array, save what you want

[ssanfu]

error that my file has more than 255 columns

In addition to what June said, this is an Access limit. A table cannot have more than 255 fields. Apparently, Access sees the total number of columns (fields) before the columns are skipped and won't allow the import.

[RLG]

Unfortunately I can't do that - too big for XL and can't do it in the .txt - 1.3G file with probably 1.5m+ rows.
It's dumb to have Access read the number of columns and then not be able to see that it won't import them all.
Oh well
thanks for suggestions guys.
RLG

I might be able to copy it in SQL and delete the columns there I suppose?

[RLG]

Mind you that may be beyond my SQL because it is a table made from a bunch of other linked tables to collect all the relevant info. So.. I'd need to find everything I want and nothing I don't want and there are lots of calculations involved to gather the information. hmm I think I may be stuck.
RLG

[JoeM]

June's second option may be the best option:

2. use VBA code to open the text file as an object and manipulate the object - read in one line at a time, parse the string to an array, loop the array, save what you want

This is not an uncommon method used to import text files into Access for a myriad of reasons, when the normal manual Import or TransferText just won't do.

[RLG]

OK. Thanks all appreciate the help.
RLG

[rpeare]

That file is not too large to parse with vba, I've processed 4.5gb files without any issue, just takes a while to run (in my case it was about 30 minutes). You may also be able to use powershell if this is a fairly common occurrence and have it run on the server instead of a workstation. You've already stated you can identify the columns you want and the columns you don't, as long as there is an identifiable delimiter that is 100% reliable you can load each line into an array then build a SQL statement around the elements of the array you want and append those to your target table.