SQL Server export to CSV

Introduction

Exporting SQL Data to CSV files is the simplest solution in some cases.

There are some advantages:

• User may not have a direct connection to SQL Server.
• Can use it to transfer large database between programs.
• CSV is human readable and easy to edit manually.
• CSV is simple to implement and parse.

I was in a situation where I want to export more than 150 SQL tables to CSV files but I want to automate the process to save time and effort like a wizard or execute query that converts all tables to CSV and I found SQLCMD is very helpful.

How to Export SQL Data to CSV Files

Using SQL Server Import and Export Wizard

Right click on your DB choose ->tasks->export Data, then SQL wizard will open. You have to choose your:

• Data Source, then press next
• Destination source. You should choose Flat File Destination and specify where to store the CSV generated file, then press next
• Choose which table you need to convert to CSV, press next
• You can save these steps as SSIS file to run it again or just Finish and File will be generated

Import and Export Wizard disadvantages

This wizard allows you to export only one table per time so if you have a large database, you will need to repeat the above steps for each table in your DB and it will take a long time.

Changing SQL Server Query Result

• Go to Tools > Options > Query Results > SQL Server > Results To Text
• On the far right, there is a drop down box called Output Format
• Choose Comma Delimited and click OK.
• This will show your query results as comma-delimited text, to save the results of a query to a file: Ctrl + Shift + F. A full screen version of that image is shown below:
  
  

Disadvantages

If the data is very large, it will not be saved. You have to repeat saving for each file and it will take more time.

Using Sqlcmd

SQLCMD utility allows you to type Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job.

To export data as CSV you can run something like that:

sqlcmd -S ServerName -d DBName -E -s, -W -w 65535 -Q "Query" -o FILEPATH\myfile.csv

-s, -d, -E, -W, -w, -Q and more are SQLCMD options you can check them from here.

This line can be executed using OS CMD or using normal query editor, but you have to make two things:

• Change the Query mode to SQLCMD mode. To do that, go to Query --> SQLCMD mode.
• Add ”:!!” before the SQLCMD Line. The exclamation points lets you run a Windows operating system command, such as those you’d run directly at a command prompt. So the command should be like below:

:!!sqlcmd -S ServerName -d DBName -E -s, -W -w 65535 -Q "Query" -o FILEPATH\myfile.csv

This line will output CSV file, but it will add two things:

• # of columns affected at the end of the file so to remove it SET NOCOUNT ON; before your Query like below
• NOCOUNT ON; SELECT * FROM mytable”
• Hyphens after the header row. To remove it, I did a workaround so will update query like below:

:!!sqlcmd -S Servername -d DataBaseName -E -s, -W  -w 65535 -Q "SET NOCOUNT on; 
SELECT * FROM dbo.mytable" -o "Path\mytable.tmp"
:!! find /v "---" < "Path\mytable.tmp" > 
"Path\mytable.csv" & del "Path\mytable.tmp"

So now, you can repeat these two lines change Query command and the output File name for all tables in your Database, set them in the same File change query mode to SQLCMD Mode and hit the Execute button and it will generate CSV files for each table. The script file will be something like the snippet below:

:!!sqlcmd -S Servername -d DataBaseName -E -s, -W  -w 65535 -Q "SET NOCOUNT on; 
SELECT * FROM dbo.mytable" -o "Path\mytable.tmp"
:!! find /v "---" < "Path\mytable.tmp" > 
"Path\mytable.csv" & del "Path\mytable.tmp"

:!!sqlcmd -S Servername -d DataBaseName -E -s, -W  -w 65535 -Q "SET NOCOUNT on; 
SELECT * FROM dbo.mytable2" -o "Path\mytable2.tmp"
:!! find /v "---" < "Path\mytable2.tmp" > 
"Path\mytable2.csv" & del "Path\mytable2.tmp"

Notes When Exporting Data to CSV

• If your string data contains “,” you must add double quotes around this string before exporting to CSV.
• If you use SQLCMD and find command to remove Hyphens, you should care about that your data does not contain ” --- “as it will be deleted.