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?
This question is related to
sql-server
sql-server-2005
bcp
sqlcmd
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.
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.
set nocount on
the quotes are there, use -w2000 to keep each row on one line.
You can use following Node.js module to do it with a breeze:
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.
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.
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
SSIS is a very good way to do this. This can be then scheduled using SQL Server Agent jobs.
I think the simplest way to do this is from Excel.
If you have a newer version of Excel you could bring the data in from PowerPivot and then insert this data into a table.
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.
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!
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.
Source: Stackoverflow.com