Click here to Skip to main content
13,766,617 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

30.9K views
17 bookmarked
Posted 25 Aug 2015
Licenced CPOL

SQL Server export to CSV

, 25 Aug 2015
Rate this:
Please Sign up or sign in to vote.
This tip contains how to Export SQL Data to CSV files using normal ways and mainly using SQLCM.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Muhammad Magdi
Software Developer (Senior)
Egypt Egypt
I’m a senior software engineer with +7 years of experience in .net technology stack. I deeply love what I do as a software engineer. I have a great passion learning new frameworks, tools, techniques in this area and sharing knowledge with community.

You may also be interested in...

Comments and Discussions

 
GeneralMy Vote of 5 Pin
aarif moh shaikh25-Aug-15 19:42
professionalaarif moh shaikh25-Aug-15 19:42 
QuestionProper references needed Pin
Wendelius12-Aug-15 10:07
mentorWendelius12-Aug-15 10:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.181114.1 | Last Updated 25 Aug 2015
Article Copyright 2015 by Muhammad Magdi
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid