This guide explains how to export a SQL Server query results to a CSV file using SSMS.
SQL Server Management Studio (SSMS) is the primary tool used for managing SQL Server databases and running queries. By default, SSMS exports data in a column-aligned format, but to export data as a CSV, we need to adjust the export settings to be comma-delimited.
Exporting Query Results to a File in SSMS
To begin the export process, we use the Results to File option instead of the default Results to Table. This method allows us to save the query output directly to a file.
1. Select ‘Results to File’:
In SSMS, click on the Results to File option as shown in the screenshot below.
2. Run the Query:
When you run the query, SSMS will prompt you to select a location and file name for saving the output.
3. Default Output Format:
By default, the file will be saved with a .rpt extension. If you open this file in a text editor like Notepad++, it will display the data in a column-aligned format.
4. Save as CSV:
To save the file as CSV, you can change the file extension from .rpt to .csv. However, this will still not format the output correctly for CSV use.
You will now likely want to change the delimiter after looking at this output file. This .rpt file should not be opened as CSV.
Modifying SSMS Output Format for CSV
The next step is to adjust SSMS’s default export settings to ensure that the output is in a proper comma-delimited format for CSV files.
1. Access SSMS Options:
Navigate to Tools > Options in SSMS.
2. Change Query Output Settings:
In the Options window, go to Query Results > SQL Server > Results to Text.
3. Set Delimiter to Comma:
By default, the output is set to “Column-aligned.” Change this to “Comma delimited.”
4. Reconnect to SQL Server:
After making these changes, disconnect from your current session in the SSMS Object Explorer and reconnect to the SQL Server instance.
5. Export Again:
Run your query again. This time, the output file will be formatted correctly for CSV, with values separated by commas.
Note for SQL Server Devs/Admins:
You may encounter an issue with the exported CSV data, and it contains “rows affected” at the end of it. The reason for this is that we need to include SET NOCOUNT within the SQL query when we use Results to Text. I talk more about this in another one of my posts: Count Rows in CSV Files.
Leave a Reply