How to export SQL Server 2005 query to CSV

Question

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

User · Accepted Answer

In Management Studio  select the database  right-click and select Tasks- gt 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

User · Answer

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

User · Answer

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

User · Answer

I think the simplest way to do this is from Excel     Open a new Excel file  Click on the Data tab Select Other Data Sources Select SQL Server 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use following Node js module to do it with a breeze   https   www npmjs com package mssql-to-csv

User · Answer

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

User · Answer

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

User · Answer

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- gt Options- gt Query Results- gt Sql Server- gt 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

User · Answer

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

User · Answer

set nocount on   the quotes are there  use -w2000 to keep each row on one line

[sql-server] How to export SQL Server 2005 query to CSV

Examples related to sql-server

Examples related to sql-server-2005

Examples related to bcp

Examples related to sqlcmd