Exporting Data from SQL Server Express to CSV (Need Quoting and Escaping)

Batch export SQL database to CSV with escaping quotes

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.

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

You can run something like this:

sqlcmd -S MyServer -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)

How to export SQL Server 2005 query to CSV

In Management Studio, select the database, right-click and select Tasks->Export Data. There you will see options to export to different kinds of formats including CSV, Excel, etc.

You can also run your query from the Query window and save the results to CSV.

---

Related Topics