export proper csv from SqlServer management studio query result

Question

in SqlServer management studio, you can export query results to a csv file by right click -> "Save Results as" -> csv. However, the csv file does not contain column headers and does not escape any commas in the data itself, which often leads to a malformed file.

How can I export query results with headers and commas properly handled?

Solution 1

See Options > Query Results > SQL Server > Results to Text

Check "include column headers". My text results are being enclosed in doublequotes so that should take care of the comma issue. Take a look at the "Results to grid" tab as well - I see a "Quote strings containing list separators when saving .csv results"

If not through SSMS then you can also use the sqlcmd command line utility which includes more options than the powershell equivalent.

Solution 2

Not through Management Studio, but you can do this with the powershell integration in 2008. right-click in the object explorer and click "Start Powershell", then:

Invoke-Sqlcmd -ServerInstance '.\sqlexpress' -Database 'temp' -Query "select * from t" | Export-Csv file.csv -notype

Comments

[Admin]

See also: How to get export output in “real” CSV format in SQL Server Managment Studio? (SO)

[Giscard Biamby]

This method works a little better than the accepted answer, though it requires use of PowerShell. Using this from now on to export CSV.