How to export output in "real" CSV format in SQL Server Management Studio(SSMS)?

Dealing with special characters

If you have data that includes special characters like commas, newline characters, or quotes, SSMS has a handy feature. The option "Quote strings containing list separators when saving .csv results" can be located at Tools -> Options -> Query Results -> SQL Server -> Results to Grid. Enable this checkbox to make sure that fields with commas are correctly quoted in the output!

Custom CSV exports using T-SQL

For granular control on exports, use T-SQL to build the CSV content dynamically:

SELECT '"' + REPLACE(CAST(column_name AS VARCHAR(MAX)), '"', '""') + '"' AS QuotedColumn /* Quoteception right here! */
FROM database.schema.table
FOR XML PATH('')


This script handles any quotes embedded within your data, making sure the CSV remains valid and error-free.

Wizard mode: Guided CSV exports

SSMS also has a so-easy-a-caveperson-can-do-it-Import and Export Wizard. Open the Object Explorer, right-click on the database; a context menu will appear. Navigate to Tasks > Export Data. Choose Flat File as the destination, then proceed to use the Microsoft OLE DB Provider for SQL Server. Specify the format as delimited, and set the text qualifier to a double quote("). Don't forget to check the option to include column headers!

PowerShell scripts: Set it and forget it!

Simplify repeated CSV exports with PowerShell scripts:

Invoke-Sqlcmd -Query "SELECT * FROM your_table" -ServerInstance "your_server" |
    Export-Csv "path\to\output.csv" -NoTypeInformation -Encoding UTF8 /* Automation for the nation! */


Unpacking complexity: Best practices

Dealing with quotes

Doubling the quotes in your data before export can ensure your CSV remains valid. After all, we adhere to the age-old adage - "Quotes inside quotes can lead to an uprising."

Unifying data types

Different data types in a CSV file can result in type mismatches. By using SQL functions like CAST or CONVERT, you can unify your data types and prevent sounding like a broken record trying to debug such hiccups.

Text qualifiers for textual data

Got textual data fields and lots of them? Remember to surround them with text qualifiers. This option can be found when using the built-in Import and Export Wizard in SSMS.

Automating the process

Why do everything step by step when you have the magic of automation at your fingertips? PowerShell scripts can take over the mundane, letting him handle the monotony while you sip your coffee in peace.