problems parsing CSV file

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

Guest

What exactly does Access use as a flag for the end of a record when importing
from a CSV file?

I have a CSV file that I need to import into Access '97. At unpredictable
points in the recordset, (I.E. no discernable pattern to these errors) The
next record is appended 3-6 fields in from the end of the previous record.
Out of 250 records, only about 50 actually import "properly." The others are
piggybacked onto one another. This file is generated by an Oracle query, so
if there's a typo somewhere, it SHOULD be consistant for each record.

My first guess was that there was a comma, quote mark or carriage return
missing from the oracle query - but ...

A) it imports into Excel just fine - nothing piggybacked, no fields out of
place... nothing.

B) like I said - there's no rhyme or reason that I can find for the behavior
of these records and which ones piggyback and which ones don't.

HELP!

:-)
-Amanda
 
Hi Amanda,

In general there should be a carriage return and linefeed (i.e. \0x0d0a,
or Chr(13) & Chr(10)). Access 2000 onwards seem to insist on this.
Earlier versions tend to be more accommodating, but I'd check it all the
same. Excel is different again.

In my experience this sort of problem virtually always comes down to an
irregularity in the CSV file. One possibility is quote marks within the
data; this
123,"Text field","Text field with "quote" marks",889
needs to have the quote marks escaped by doubling them VB-style:
123,"Text field","Text field with ""quote"" marks",889
 
John -

I'm working in '97, and I do have 3-4 fields where the strings contain
quotes. (worse, the quotes are functioning as an inch indicator, so there's
only 1, not a set of them...)

Last time I ran into a problem with quotes in strings, I ended up with data
landing in the wrong fields (one field parsed into 3 for example) so I was
thinking this was a different problem... but maybe not.

I'll see if I can eliminate the quotes and test the theory. I'll let you
know what I find.

-Amanda
 
John -

That was it!

See - the field is a concantenated string of values that describe (in this
case) a truck chassis. in that string I had the length of the wheelbase and
the distance from cab to axle in inches notated with a quote mark for
shorthand. Those records that didn't have those values imported fine - those
that had one (or both!) were the ones thrown off (my guess is the two dark
vertical lines I was seeing where the records spliced were the carriage
return and line feed characters (of course, not displayable as a valid
character)).

Many Many thanks! I'd have hated to trash a program I was halfway through
writing because I couldn't get the data to cooperate!

-Amanda
 
Amanda,

I'm glad we found it. This confirms my belief that text import problems
are almost always down to what's in the file.

One way one can sometimes avoid this particular problem is to use the
prime and double prime for feet and inches (or minutes and seconds)
instead of apostrophe and quote. They're in Unicode as U+2032 and U+2033
respectively, though most general-purpose fonts don't have them (Lucida
Unicode and Arial Unicode do). I believe that this is typographically
more correct anyway.
 
Back
Top