63

I want to export some SQL Server 2005 data to CSV format (comma-separated with quotes). I can think of a lot of complicated ways to do it, but I want to do it the right way. I've looked at bcp, but I can't figure out how to put the quotes around the fields (except concatenating them to the field values, which is ugly). I guess I could do it with sqlcmd and -o, but that seems ugly for the same reason.

Is there a bcp way to do it?

Is there a reasonable sqlcmd way to do it?

Is there some great, simple utility built into the Management Studio that I'm just overlooking?

John M Gant
  • 17,902
  • 17
  • 59
  • 82
  • also see: http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd – Ray Mar 11 '10 at 16:52

13 Answers13

63

In Management Studio, select the database, right-click and select Tasks->Export Data. There you will see options to export to different kinds of formats including CSV, Excel, etc.

You can also run your query from the Query window and save the results to CSV.

Jose Basilio
  • 48,203
  • 11
  • 115
  • 116
  • I knew it was something simple like that. Thanks. – John M Gant Apr 28 '09 at 19:56
  • 25
    When you save results to CSV it doesn't seem to escape commas in varchar fields properly – Joe Phillips Feb 21 '12 at 16:46
  • 2
    When exporting to CSV it also does not escape EOLNs within text fields (there is a workaround to wrap the exported data column with double quotes rightn when selecting from the database). – Jan Šotola Apr 08 '14 at 17:59
  • You'll also want to change Query Options in Management Studio to fix field escaping and add column headers. -- Go to Query -> Query Options -> Results and then select to include column headers and quote strings. – Jon Mabe Aug 11 '15 at 20:26
44

In management studio, set query options to output to file, and in options->query results set output to file to output using comma as delimiter.

Bill the Lizard
  • 369,957
  • 201
  • 546
  • 842
Sijin
  • 4,450
  • 19
  • 22
31

If you can not use Management studio i use sqlcmd.

sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","

That is the fast way to do it from command line.

sjngm
  • 11,411
  • 13
  • 72
  • 105
curtisboy
  • 311
  • 3
  • 2
  • There are a couple of problems with this approach. It doesn't include the quotes, and it prints a "number of rows affected" message at the bottom of the file. Do you know a way to get around either of those? – John M Gant Dec 15 '09 at 17:54
  • One other note: -q leaves the sqlcmd editor open. -Q closes it. – John M Gant Dec 15 '09 at 17:57
  • 9
    prepend "SET NOCOUNT ON;" to the query in order to suppress the number of rows from being displayed. – JohnW Jan 02 '12 at 19:40
  • see this one: http://stackoverflow.com/a/2426853/1246870: -W (to remove trailing spaces) was a life-saver for me – avs099 Apr 21 '15 at 09:16
25

I had to do one more thing than what Sijin said to get it to add quotes properly in SQL Server Management Studio 2005. Go to

Tools->Options->Query Results->Sql Server->Results To Grid

Put a check next to this option:

Quote strings containing list separators when saving .csv results

Note: the above method will not work for SSMS 2005 Express! As far as I know there's no way to quote the fields when exporting results to .csv using SSMS 2005 Express.

goku_da_master
  • 3,857
  • 1
  • 34
  • 38
  • 7
    Why oh why would that not be the default behavior? That's beyond dumb that you have to set that. Thanks for the tip, though. Very useful. – Josh Kodroff Jun 01 '12 at 16:49
  • 5
    For what it's worth, the "quote strings" option didn't work for me (emitted strings weren't quoted). I wound up having to include the quotation marks in my SELECT statement. Leave it to Microsoft to make a data export tool that is completely useless. – Kevin Day Oct 05 '12 at 13:17
  • 2
    I know this was a long time ago, and I am on SQL Server 2008, but one thing I wanted to add is this: make sure that after you change the quote setting, you open a new query window to run the query and export the results from. It doesn't work unless you do that. Just adding this for the benefit of anyone else who reads this answer. – sparc_spread Mar 25 '14 at 21:27
9

Yeah, there is a very simple utility in Management Studio, if you're just looking to save query results to a CSV.

Right click on the result set, the select "Save Results As". The default file type is CSV.

Pete H.
  • 1,427
  • 1
  • 12
  • 16
6

If it fits your requirements, you can use bcp on the command line if you do this frequently or want to build it into a production process.

Here's a link describing the configuration.

dkretz
  • 36,502
  • 13
  • 76
  • 133
5

For adhoc queries:

Show results in grid mode (CTRL+D), run query, click top left hand box in results grid, paste to Excel, save as CSV. You may be able to paste directly into a text file (can't try it now)

Or "Results to file" has options too for CSV

Or "Results to text" with comma separators

All settings under Tool..Options and Query.. options (I think, can't check) too

gbn
  • 394,550
  • 75
  • 549
  • 647
2

set nocount on

the quotes are there, use -w2000 to keep each row on one line.

stevej6x7
  • 21
  • 1
2

In SQL 2005, this is simple: 1. Open SQL Server management studio and copy the sql statement you need into the TSQL , such as exec sp_whatever 2. Query->Results to Grid 3. Highlight the sql statement and run it 4. Highlight the data results (left-click on upper left area of results grid) 5. Now right-click and select Save Results As 6. Select CSV in the Save as type, enter a file name, select a location and click Save.

Easy!

1

In Sql Server 2012 - Management Studio:

Solution 1:

Execute the query

Right click the Results Window

Select Save Results As from the menu

Select CSV

Solution 2:

Right click on database

Select Tasks, Export Data

Select Source DB

Select Destination: Flat File Destination

Pick a file name

Select Format - Delimited

Choose a table or write a query

Pick a Column delimiter

Note: You can pick a Text qualifier that will delimit your text fields, such as quotes.

If you have a field with commas, don't use you use comma as a delimiter, because it does not escape commas. You can pick a column delimiter such as Vertical Bar: | instead of comma, or a tab character. Otherwise, write a query that escapes your commas or delimits your varchar field.

The escape character or text qualifier you need to use depends on your requirements.

live-love
  • 34,372
  • 16
  • 163
  • 152
1

I think the simplest way to do this is from Excel.

  1. Open a new Excel file.
  2. Click on the Data tab
  3. Select Other Data Sources
  4. Select SQL Server
  5. Enter your server name, database, table name, etc.

If you have a newer version of Excel you could bring the data in from PowerPivot and then insert this data into a table.

salvationishere
  • 3,141
  • 28
  • 97
  • 142
  • Heh. There's thinking out of the box. I was working in SQL Server at the time, and couldn't figure out a good way to do it from there. I never thought to start from the other side. Good suggestion! – John M Gant Jun 19 '17 at 20:27
0

SSIS is a very good way to do this. This can be then scheduled using SQL Server Agent jobs.

user44565
  • 3
  • 5
  • 1
    SSIS is actually a poor way to do most things. Its useful if you need to write code but don't know SQL or another programming language, otherwise it is slow, bloated, doesn't use transactions in a sane way, and difficult to debug. Theres no point in an entire SSIS package when a simple BCP call will do. – David Sep 14 '15 at 14:42
0

You can use following Node.js module to do it with a breeze:

https://www.npmjs.com/package/mssql-to-csv

adnan kamili
  • 7,575
  • 5
  • 50
  • 105