SQL Server and CSV

Sometimes we need to generate a simple list from a SQL Server database and store that list in comma separated values (CSV) or Excel format. It is simple enough to use Query Analyzer to create and run a query. But it is not as simple to export the results of that query to a CSV file, especially if any column in the result set has values containing a comma. SQL Server does not use quotes in the exported file to qualify the data in columns containing text. As a result, if a column contains something like "Big Company, Inc." the text after the comma will be treated as another column - in this example containing " Inc."

To work around this problem we use the following steps to export directly to an Excel file using a tab-delimited format rather than comma-delimited. Note that this will only be effective for result sets less than 65,536 rows since that's the number of rows Excel can handle.

These steps are listed originally here.

In Query Analyzer, 1. Click Tools from the Menu bar 2. Click Options 3. In Options dialog box click Results tab 4. Select “Tab delimited” option in “Results Output Format” window 5. Select “Print column headers(*)” 6. Click OK 7. Click Query from the Menu bar 8. Click Results to File 9. Type in your query in Editor Pane or use the Object Browser to generate a query 10. Press F5 key to execute your query 11. You should get the Save Results dialog box 12. In File Name window type in the name of your output file. For example: Orders.xls. Make sure you provided the file extension (xls). 13. Click Save.

In the Results Pane you will get the number of rows in the result set, path to the output file and the size of the file.