How to handle/strip out Quotes around string columns that can be null when using bcp to import csv files?

From: David E Herbst (dherbstemail-ng_at_yahoo.com)
Date: 08/25/04 

Date: 25 Aug 2004 12:24:46 -0700

I am trying to import data from third party CSV files that contain
quotes around string ([varchar] and [char]) columns. To allow BCP
(SQL 2000 version) to handle/strip out the quotes I am using format
files that specify quotes as part of the column delimiters. I can't
just preprocess the text to remove the quotes because the string
columns may include commas.

Example format file:
1 SQLCHAR 0 0 ",\"" 1 c1 ""
2 SQLCHAR 0 0 "\",\"" 2 c2 ""
3 SQLCHAR 0 0 "\"\n" 3 c3 ""

This works pretty well for the following text:
111,"Winken,Blinken,Nod","aaa"

The problem is that if one of the string columns values is null\empty
[,,] bcp doesn't find the delimiter since it's expecting a quote-comma
[",] delimiter. E.g.:
111,,"aaa"

This results in a bcp error due to not finding the quote-comma
delimiter! Is there something I can specify in the format file to
make the quote an optional part of the delimiter? Can I specify an
alternate delimiter for a column? Is there a better way to do this?
This is part of a larger automated process located on a different
server than the DB so if possible I would like to continue to use bcp…

Thanks,
David

Relevant Pages