Is there an easy way to output text files (or CSV) from SQL Server?

Question

I spent a lot of effort searching and searching, and the best option is either:

• sending output to text and copying / pasting into a text file or Excel OR

• output to a somewhat unorthodox .rpt file, which I’m not sure what you would do - opening it in Excel does not preserve the formatting present in the original release.

For what seems like a fairly common task, I am surprised that there is no simpler way to do this.

Can anyone suggest an easier way to do this than the two methods I described?

Oh, and what it's worth, I'm working on SQL Server 2008.

Answer 1

Even through the SSMS GUI, it is still relatively a PITA process:

Plan A:

• Tools, parameters, query results, results for text <= Change output format from "fixed columns" to "delimit by tabs"

• At this point, you can "Save Results to File" and specify the .csv file

Plan B: Run your favorite scripting language (e.g. vb.net) and just write a program that executes an SQL query and writes a CSV file. 10 lines, vertices :)

Plan C: Another approach is to use some external program to execute the query and translate the results for you. SQL Server ships with "BCP." You can easily write a .bat file to call it:

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

'Hope that helps

Answer 2

What about the BCP utility? http://msdn.microsoft.com/en-us/library/ms189569.aspx

Answer 3

Are you using SQL Server Management Studio? If so, when you open a new query window, you can choose to send the output to a file. Query menu → Results for → Results to file.

Answer 4

Chris, it's really super easy.

If your query results are displayed in a grid (as by default), just right-click on the grid and select Save Results As...

Answer 5

Inside MSSQL Management Studio, you can right-click on the database and select "Tasks" → "Export Data". It launches a wizard that allows you to select a data source. On the Destination page, you can select either Microsoft Excel or Flat File Destination. The next page in the wizard allows you to specify data from one or more tables or a user request to receive your data. If you selected “Flat File Destination” earlier, on the next page you can set your own separators.

Answer 6

Another option is to pull it in Excel from SQL

Answer 7

Run your query and on the Results tab, press Ctrl-a to select everything and paste it into Excel.