5

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?

Sam
  • 28,421
  • 49
  • 167
  • 247

4 Answers4

4

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

wmz
  • 3,645
  • 1
  • 14
  • 22
  • Yes, this kind of quoting is exactly what I am looking for! But of course my field contents contain line breaks and are longer than 128 chars - ain't there something like this for longer columns, too? – Sam Jun 14 '14 at 06:56
  • I thought SSIS would handle this correctly, but obviously id does not. It's possible to get proper output, but a custom [column transform] function is needed, so effectively it's the same as what @ElectricLlama proposed. If you could define custom function, maybe this would work (I have not tested it): http://nosqlsolution.blogspot.com/2012/07/problems-with-quotename.html – wmz Jun 14 '14 at 08:39
  • I found a solution using powershell, which contains a csv writer that can create properly encoded csv files, which works with large strings, too: http://stackoverflow.com/a/23976218/7021 – Sam Jun 14 '14 at 20:02
  • 1
    You should post this as an answer, @Sam – Nick.Mc Jun 15 '14 at 08:34
3

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • The batch export is not allowed to change the database to add views. I'll try to use the replace in sqlcmd, though, thanks. – Sam Jun 12 '14 at 12:58
  • You can use a query instead, but it'll get messy. You can also create a view in a different database that references your source database if this doesn't have to be portable. Maybe you should explain all the constraints. – Nick.Mc Jun 12 '14 at 22:45
  • If no field seperator is ever acceptable or reliable, the only option is fixed width. – Nick.Mc Jun 12 '14 at 22:56
  • If the cell content is properly quoted it works from every other program. Fixed width has problems with line breaks, and the receiving app expects proper csv, not fixed width. – Sam Jun 14 '14 at 06:53
2

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.

Community
  • 1
  • 1
Sam
  • 28,421
  • 49
  • 167
  • 247
-1

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/

Narti
  • 181
  • 7
  • 1
    txt would do nicely, too, but your solution has the same Problem: What happens if the delimiter (|), the item Separation (,) or the row Separation (\n) exists in the table items? BCP will not escape these characters so your solution will have the same problems as mine, it just will fail on different characters. – Sam Jun 12 '14 at 11:25
  • You could chose a filed seperator which is most unlikely to appear in your data. Have seen solutions using #*# as field delimiter – Narti Jun 12 '14 at 12:23
  • 1
    the receiving end does only work with single-char delimiters - and "unlikely" usually breaks next tuesday :/ – Sam Jun 12 '14 at 12:42