Hi Chris,
There are many different kinds of text file.
Access/Jet's built-in text file import system requires a rigid structure
in which one line of the text file = one row of the table.
Beyond that, the file must either be fixed-width or delimited. In a
fixed-width file, every line must contain the same number of characters,
and each field must contain the same number of characters in every line.
Moreover, each line of the text file must be independent of each other
line. In a text file, the lines are in a certain fixed order; in a
relational database table the order of the records is (in effect)
undefined and the only way to see them in a particular order is to sort
them by the values of one or more fields.
In a delimited file, the lines and fields can be different lengths, but
one character (most often a comma, a tab or a semicolon) is reserved to
indicate the end of one field and the beginning of the next. If the
separator character also has to appear in the data, the field value has
to be "delimited" or "qualified", usually with quote marks:
1,Tom, Dick and Harry = 3 fields: <1> <Tom> <Dick and Harry>
1,"Tom, Dick and Harry" = 2 fields: <1> <Tom, Dick and Harry>
The only exception to the one line = one row equation is that there are
circumstances in which Access/Jet can import a text file that has
linebreaks embedded in the data.
In a delimited file, the linebreak must be in a "qualified" text field.
This in the text file
1,Tom, Dick
and Harry,100
is interpreted as two rows of three and two fields respectively:
<1> <Tom> <Dick>
<and Harry> <100>
But this
1,"Tom, Dick
and Harry",100
is intepreted as a single row of three fields of which one contains a
linebreak:
<1> <Tom, Dick <100>
and Harry>
In a fixed-width file, a text field can contain a linebreak (but the two
characters of the linebreak count towards the fixed number of characters
in the field).
I hope this makes sense. If your file doesn't fit one of these patterns,
you won't be able to use the standard import routines. Instead you will
either need to restructure the file before importing it or write a
custom import routine in VBA.
