Batch export SQL database to CSV with escaping quotes

Question

I'm looking for a way to batch Export a SQL Server table to a csv file.

There are some solutions using sqlcmd or bcp, but so far I found none which properly escapes quotes, commas or line-breaks.

For example this command creates a nice csv but does ignore quotes and commas which renders the csv file unusable:

bcp MyDatabase..MyTable out c:\test.csv -c -T -t, -r\n -S MYPC

From my sample data of four rows each containing some other special character this would create a file like this:

1,contains " quote
2,contains , comma
3,contains ; semi
4,contains ' single quote

Due to the quotes and the comma this is not importable by other programs. Of course I could change the separator to tab or the pipe symbol, but this does not fix the real problem: Whatever the separator is, if it exists in the data it will render the export file unusable.

So how do I bulk export data in a batch to a working csv file using standard SQL tools like BCP, sqlcmd or similar?

Answer 1

Using quotename should properly escape quotes (but it's limited to max 128 chars, no line-breaks):

BCP " select quotename(quotedCol,CHAR(34)),quotename(secondCol,CHAR(34))from 
testdb.dbo.table_1" queryout temp.csv -c -T -S. -t","

given values this is "between quotes" and def it produces:
"this is ""between quotes""","def" which is I believe properly quoted/escaped according to csv quidelines.

Source: http://social.technet.microsoft.com/wiki/contents/articles/4666.sql-server-bcp-utility-experts-guide.aspx#Use_Text_Qualifier_on_BCP_Output