Importing textfile with too many fields

[bobfin]

I have a textfile that I want to import into Access 2003. It is comma-delimited with double quotes as the text identifier. The first row has field names. Unfortunately, the number of fields is higher than the maximum Access can handle (255). What can I do to this file so I can get it into Access?

I had a similar situation with another vendor's file. Since it was a fixed-width, I was able to create 2 import specs. Each one imported half the fields into separate tables. But with a delimited file, I can't stop Access from determining where the fields begin and end.

 

[norman]

This was discussed not long ago on this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=62751

The concensus of opinion was that it was not easily possible


Quote from Pat Hartman
"If you can have your import changed to fixed width you'll be able to automate the import by using several Import Spec's and the TransferText Method. This will allow you to import sections of the file at a time. Delimited files don't give you this option because Access uses the delimiters to determine the field breakdown. If you must use delimited files, you're going to have to brace yourself for a lengthy coding session to parse the input record via VBA." - Pat.

Norman

 

[bobfin]

Can you show me some sample VBA code that indicates how to parse a delimited textfile with VBA? Upto now, I've only used string functions in Access to parse single fields. Also, it looks like I'll have to convert the textfile to fixed-width before importing into Access. So how can I use VBA in that case?

 

[norman]

This link has example code to parse comma delimited text.

You will need to play around with it to import.
http://www.mvps.org/access/strings/str0003.htm

HTH
Norman