Text Import Problem (Quoted Identifier and Comma-delimited)

[DCM Fan]

Access 2K, SP3 on Windows 2K, SP4

All,

I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.

This has been working for 2 years, until today, when one of the data fields
contained quotes in the data itself.

At that spot in the data file, it looks like this:

...... ,"", "WHITE", 34.6, "USES A "1"", "09897", .....

The data typed by the users was: USES A "1", and the export routine oblidgingly
but quotes around all of it to get: "USES A "1""

Here's the crazy part: The Import Text Wizard preview window CORRECTLY parses
the file (well, the vertical lines are in the right place!), but upon import,
the row in question has no data imported after the word "A", and there exists
an ImportErrors table showing that row as "Unparsable Record."

Excel correctly parses the file, FWIW.

Can anyone shed any light on this? Why can the wizard draw the lines in the
right place, but Access' import engine can't handle it? It seems to that the
users should be able to use quotes whenever they want...after all, with quoted
identifiers AND comma-separation, the import engine should be able to handle
this. I "might" understand if quotes and commas were mixed, but this doesn't
have that.

Yes, I know I can write my own parser...I'd just like to know why Access is
confused by this data.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access isn't confused - the exported text data is incorrectly
formatted. It should be formatted like this:

"USES A ""1""",

The quotes need to be escaped when inside a string delimited by quotes
- - so they will be evaluated as data characters instead of delimiter
characters. Escaping quotes is usually accomplished by a preceeding
the character quote with an escape quote.

But you'll say "Why does Excel import the file OK?" Don't know -
probably 'cuz it ignores the quotes and only recognizes the commas as
field delimiters.

You may want to change your import spec to have {none} as the Text
qualifier and the comma as the field delimiter. But, then you'll have
to watch out for fields that contain commas - they will parse out as
multiple fields instead of one field.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5hf8oechKqOuFEgEQInxwCeKDCkPXZJOtGLHd7MYrRU4b IBjN8AoMUn
ISK3+GAATe1tNvTrzFZjQYTy
=ISRV
-----END PGP SIGNATURE-----

DCM Fan wrote:

Access 2K, SP3 on Windows 2K, SP4

All,

I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.

This has been working for 2 years, until today, when one of the data fields
contained quotes in the data itself.

At that spot in the data file, it looks like this:

..... ,"", "WHITE", 34.6, "USES A "1"", "09897", .....

The data typed by the users was: USES A "1", and the export routine oblidgingly
but quotes around all of it to get: "USES A "1""

Here's the crazy part: The Import Text Wizard preview window CORRECTLY parses
the file (well, the vertical lines are in the right place!), but upon import,
the row in question has no data imported after the word "A", and there exists
an ImportErrors table showing that row as "Unparsable Record."

Excel correctly parses the file, FWIW.

Can anyone shed any light on this? Why can the wizard draw the lines in the
right place, but Access' import engine can't handle it? It seems to that the
users should be able to use quotes whenever they want...after all, with quoted
identifiers AND comma-separation, the import engine should be able to handle
this. I "might" understand if quotes and commas were mixed, but this doesn't
have that.

Yes, I know I can write my own parser...I'd just like to know why Access is
confused by this data.