Importing text file with carriage returns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am trying to import a text file using Access 97. If I open the file
using notepad, it looks fine. When I import it into an Access table,
sometimes, 2 lines are in 1 row of the table. I don't see all of the data,
but if I keep moving through the row, the other data becomes visible.
Is there any way to import it, and keep the same number of rows that I see
in notepad? I need the data to stay in the same relative positions within
each row.

Hope this makes sense.

Thanks
 
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.
 
Back
Top