question on importation

[bob]

I have a text file with data. the values are comma separated, quoted values
(double quotes).

one of the fields that i'm outputting in my text file is, in essense, a memo
field. this field COULD potentially contain CRLFs.

When I go to import the data in to Access 2003, it is assuming that a CRLF
is an indicator of an end of row of data.

Because my memo data has CRLFs in it, even though it's quoted, comma
separated, it hits that CRLF and the importation starts processing it as if
it's the next record.

How can I tell Access to NOT use CRLF as an end of row marker? For example,
let's say I output my text file with a character (say the pipe | character)
as the indicator that it is the end of the row of data.

In other words, I have memo textual data that I need to put into a memo
field that might contain CRLFs that are screwing up the import.

how do i handle this?

i know I could strip the CRLF's out of my memo data. but then it makes the
format of my memo data incorrect.

 

[John Nurick]

Hi Bob,

Access usually has no problems importing CSV files that include line
breaks (CRLF pairs) within records. I've just tested this with Access
2003.

It's essential that the text and memo fields be "qualified" with double
quotes: the import routine relies on counting the double quotes to tell
whether a CRLF is part of the data (i.e. between double quotes) or the
end of the record.

Consequently there are problems if the data contains double quotes, e.g.
a field whose contents are
"Legs" O'Reilly

If this is surrounded by double quotes it appears in the text file as
""Legs" O'Reilly"
there's obvious room for confusion. To avoid this, any double quotes in
the data have to be doubled, so this in the text file
"""Legs"" O'Reilly"
will import as
"Legs" O'Reilly

One thing that will cause the problem you're having is if your import
settings don't specicy " as the text qualifier. Otherwise, the problem
is much more likely to be in the text file than in Access.

Access's built-in text import routine doesn't allow anything except CRLF
at the end of a record.

 

[John Nurick]

You have double quotes in the data. As I explained in my previous post,
these must be escaped by doubling them, or they will be interpreted as
ending a field and cause the sorts of problem you are experiencing.

For example, at several places, starting at line 57 of the sample file
(i.e. within a multiline memo field) you have
:its settings by typing "ipconfig /registerdns" at the command prompt.

I suggest you overcome this by munging the log file before trying to
import it. Here's a Perl one-liner that does the job on the sample file,
though it's far from being a generalised solution:

perl -pe"s/((?<= )\042)|(\042(?= .+$))/\042\042/g" eventlogdata.txt

 

[bob]

sorry dood! i got so wrapped up into thinking it was the CRLF problem that i
really didn't look at whether it was a " issue.

thanks for you help, John!