Import table with 369 fields

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi Everyone,

I am trying to import a text file pipe delimited into
access. I have set up a specs in the import bit in access
but it only allows up to 255 fields....I have 369. Does
anybody know how I might get round this. There is not a
single unique identifier for each record but I guess a
combination of fields could be used to indentify each
record.

Does anybody know a way rounf this problem?? Please
help....and with as much info as you can please..

Thanks very much
 
Hi Daniel,

There's no way of having more than 255 fields in a record in Access -
and a text file with 369 fields will almost certainly normalise into two
or more much narrower tables.

There are many different ways of getting from one to the other but they
all involve a fair amount of fiddling. Here are three:

1) Get whoever sent you the text file to send you something friendlier.

2) Import about half the fields (including a field or fields that
uniquely identify each record) into one table, and the rest (plus the
identifying field or fields) into a second table. Create a 1:1
relationship between the tables, and then use queries on one or both of
them to extract the data in to a normalised structure. To import half
the fields, you can either

a) Download the Gnu utilities from http://unxutils.sourceforge.net/ .
Use "nl" to add a line number to the text file (thus creating a
convenient primary key) and then "cut" to create two text files each
containing the line number and half the fields. These can then be
imported - or

b) Create the two tables each containing half the fields. Write VBA code
that reads the file line by line, splitting each line into fields (using
the Split() function) and appending the relevant fields to each table.

3) I have a utility that takes a wide textfile and converts it into a
tall narrow one that Access can import, after which it can be
restructured into a proper relational form. If you have (or are willing
to install) Perl on your computer I can send you a copy of this.
 
Back
Top