How Can I Export a Record Set to Excel from SQL Server 2008?

Question

I have a department table filled with records. I would like to run a query or use the management studio interface to export department records to an excel file. Format is not an issue, i just want to dump the records into excel. I know i can copy and paste, but this is not what i'm looking for.

Answer 1

What version of Excel are you using?

Excel 2007 has the ability to read data from pretty much any data source - you can find this on the "Data" tab under "From other sources" -> "From SQL Server".

I'm sure that this functionality is also available in earlier editions of Excel (at least 2003), but I wouldn't be able to tell you where.

Answer 2

Using SQL Server Management Studio, consider these two options:

1. Ensure you have your results format set to "Results to Grid". Run your query as per normal. Right-click the result set, and choose "Save Results As..." The default file-type should be .csv, and that'll open in Excel without problem.

2. Ensure your results format is set to "Results to File". Run your query, and SSMS will prompt you for a file name, and dump to a plaintext file. Excel will open this, but the formatting won't be optimal.

Also consider this answer from Sijin regarding setting the output separator when using Results to File. SSMS allows you to specify a separator. In this case, you'd want a comma for .csv files.

Using sqlcmd from the command line, consider this answer by scottm for another similar question.

sqlcmd -S myServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
   -o "MyData.csv" -h-1 -s"," -w 700