Double quotes within text cause unparsable record error

[nitrams]

I am troubleshooting an issue importing records from a CSV file into Access
2007 (latest patches installed).

The overall goal is to import 480K records into a table from a CSV file.
When utilizing the import wizard for text files and specifying all the
appropriate paramters I receive the "unparsable record" error.

After some troubleshooting, I isolated some samples of the records causing
the issue.

The CSV contains records with the following field list:
"DocID","ParentID","DocType","MimeType","Subject","From"

The problem lies within the "Subject" field. Below are some sample records
(information scrabled, yet format intact):

"0.7.198.14492","0.7.198.14492","ikzzl3k","liiuillyiqn/6nd.iz-quyuqqk","n7:
yyiz qnk iz l "kkkikr"","(e-mail address removed) <[email protected]>
"0.7.177.729849","0.7.177.729849","ikzzl3k","liiuillyiqn/6nd.iz-quyuqqk","Yqur
Innqrilyiqn yq Jqin l 7kj ikkyin3 - "2008 nikud ludiy Rk6ik7
zkzziqn"","(e-mail address removed) <[email protected]>
"0.7.177.486973","0.7.177.486973","ikzzl3k","liiuillyiqn/6nd.iz-quyuqqk","Rk: QJD rquyink "kxluuziqnz"","(e-mail address removed) <[email protected]>

When I use the import wizard for text files, I specify the following:
1) CSV file
2) Import the source data into new table in the current database (tried all
other options with the same results)
3) Delimited
4) Delimitar that separates your fields: Comma
5) check - first row contains field names
6) Text Qualifier: "

When doing the above, the record preview window displays the records
correctly.

7) Leave defaults on all other screens
8) Create the table name
9) Click Finish

For some reason the actual import cuts off each of the subject lines and
ingnores the rest of the record.
So suddenly the subject line looks like this:
Record #1: n7: yyiz qnk iz l
Record #2: Yqur Innqrilyiqn yq Jqin l 7kj ikkyin3 -
Record #3: Rk: QJD rquyink

Plus, the "From" field is not even parsed out and completely ignored.

Has anyone seen this behavior before and found a workaround/fix for it? It
looks to me that the import feature has trouble handling double quotes within
text fields.

Any thoughts are appreciated.

- Martin

 

[Jerry Whittle]

See if you can have the CSV file sent to you without the text qualifier of "
or have it changed to someing like a single quote.

In a similar situation I had to open the CSV file in Word then modify
things. Basically I'd first do a find and replace on the " with something
like a ~. Then I would find and replace all the ~,~ back to ",". Next I'd
find all the ~^p (paragraph returns) or ~^l (line returns) and replace them
with "^p or "^l as needed.

After importing the file, I'd then use update queries and the Replace
function to change the remaining ~ back to ".

This would work well for a one-time import, but not day to day.

 

[nitrams]

Jerry -

thanks for your feedback. I was thinking about replacing the quotes. The
challenge is that I don't know in what format or configuration the quotes may
appear in my data set. I am looking at 480K records I am receiving
frequently. My last option may very well be to do a find and replace.

However, I feel that this is a fundamental problem/bug with the import
wizard and should be corrected. Since the the preview is parsing the records
just fine, I think there is a bug in actual creation of DB records.

Does anyone know how to get Microsoft's attention to a potential bug like
this?

- Martin