I have a requirement to export some SQL Server 2000 tables to a CSV file.
Since some of the fields contain commas, I need to use double-quotes as
text qualifiers. I tried using DTS first but I ran into the following
bug:

BUG: DTS Imports Empty Strings for Character Strings That Were Exported as
NULLs
http://support.microsoft.com/?kbid=300640

Essentially, DTS will put an zero length string into the text file (,"",)
if the source field (varchar) was null or if it contained a zero length
string. The export file needs to have just the two commas (,,) to
represent a null value.

The suggested workaround in this article was to use BCP. So for my next
attempt I set up an export using a format file. In order to get the
double quote text qualifiers, I must add them to the field terminator in
the format file. However I essentially run into the same issue. If one
of the varchar fields is null, then I end up with another zero length
string (,"",).

Is there something I am missing in setting up the BCP export? Is there
some other way to do this with DTS? If I must, I can see if the user will
accept a file using a different field separator (such as the vertical bar)
and no text qualifier.

Thanks,
Brian Baker
--
Microsoft MVP -- ASP / ASP.NET
Please post responses to the group

You could bcp from a query and use the query to do the
formatting - i.e. adding the quotes.
Could also create a single column global temp table with
the correctly formatted rows and bcp that. This is very
flexible and easy to develop as you can look at the table
rather than having to look at the text file every time but
is limitted to 8000 char rows.

Brian,

Why use commas? How about using tabs, which probably do not exist in your
data. That gets you out of wrapping the text strings in a delimiter like ".
Will your target be able to read a tab-delimited file?

Of course, you could run SED or some other tool across the CSV to change all
"" to NULL, but that is probably not smart enough for your needs.

Russell Fields
"Brian Baker [MVP]" <news (AT) plproductions (DOT) biz> wrote