How to export data as CSV format from SQL Server using sqlcmd?

Question

I can quite easily dump data into a text file such as:

sqlcmd -S myServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.txt"

However, I have looked at the help files for sqlcmd but have not seen an option specifically for CSV. Is there a way to dump data from a table into a CSV text file using sqlcmd?

Answer 1

I don't know it very well, but I doubt you can.

You can use a simple script to do that, though.

Try this: http://dev-notes.com/code.php?q=18

Answer 2

You can run something like this:

yServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.csv" -h-1 -s"," -w 700

-h-1 removes column name headers from the result
-s"," sets the column seperator to ,
-w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

Answer 3

sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

The last line contains CSV-specific options.

• -W   remove trailing spaces from each individual field
• -s","   sets the column seperator to the comma (,)
• -w 999   sets the row width to 999 chars

scottm's answer is very close to what I use, but I find the -W to be a really nice addition: I needn't trim whitespace when I consume the CSV elsewhere.

Also see the MSDN sqlcmd reference. It puts the /? option's output to shame.