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

Answer 2

Either make it fixed width or manually add quote delimiters. Both of these can be achieved with a view

For example your view would be

SELECT C1, '"' + REPLACE(C2,'"','\"') + '"' As C2 FROM YourTable

Then you select from this view in your BCP and C2 will be quote delimited, and quotes in the data will be escaped with \ (mostly)

To make it fixed width is just another string expression that concatenates the fields with appropriate padding.

You can use a query in BCP but I'm not sure how you escape the quotes (!) No matter what you do those quotes are a pain.

Answer 3

I found a solution which properly encodes csv files in another Stackoverflow answer by Iain Elder:

He uses PowerShell to Export proper csv:

Import-Module -Name SQLPS
$cd = Get-Location
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
              -Database AdventureWorksDW2012 `
              -Server localhost |
Export-Csv -NoTypeInformation `
           -Path "$cd\DimDate.csv" `
           -Encoding UTF8

His solution properly encodes delimiters, line breaks, quotes and works with long content, too.

I still find it strange that no other export seems to properly support csv. It's not that complicated.

Answer 4

Does it have to be csv? I usually prefer txt files to avoid this kind of problem.

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

If you have the possibility to use other delimiters try

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

Other ways to achieve well formed csv are decribed here: https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/