text file import

[Guest]

Hello.
I have a .TXT file to import to a DB. The file is Space delimited. The
problem is that the file has more than 255 columns, and the number of columns
can vary. Is it possible to create a routine that imports this file spliting
it into multiple tables?

Thanks

Luis

 

[John Nurick]

Hi Luis,

Access's standard file import system can't handle files with more than
255 fields, and AIUI it's not designed for files where the number of
fields may vary from record to record. So you'll need _either_ to write
Access VBA code that reads the file line by line, parses each line and
appends the resulting record to the table(s), _or else_ to pre-process
the file before importing it.

When I had to import data from text files with more than 1000 fields I
developed a utility "txtnrm.pl" that converts the "wide" file into a
"tall, narrow" one. Queries can be then used to import data from the
latter into normalised tables in an Access database. You can download
txtnrm.pl from http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm ,
but to use it you will also need to install Perl.