BCP export to CSV with double qoutes around data

[Dave DiNatale]

I want to use bcp to export a query to a .csv file. The
problem is that my data has commas in it so I need to use
double quotes around each field of data that has a comma
in it. unfortunately ab delimting is not an option.

How do I get quotes around my data fields using bcp?

Thanks.

[lindawie]

Dave,

You need to use a format file for this. Using the pubs..authors
table as an example, we'll bcp out of a view that looks like this:

use pubs
go
create view authors_csv as
select null first_quote, * from authors

Note that we are including a dummy column called first_quote
that just returns NULL. It's just a little trick to get the leading
quote on the first column.

The format file looks like this:

8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""

That dummy column is also in the format file to get the leading quote on
au_id.

Here's the command line:

bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ml_tg -T

If you only need to enclose some of the columns in quotes,
you can remove the extraneous one from the format file.

Another alternative is to create a view that concatenates the quotes to
the column values.

Linda