Introduction

SQL Server data export to CSV files instead of Microsoft Excel files is the simplest solution in some cases.

There are some advantages:

  • A user may not have a direct connection to SQL Server.
  • A user can use any version of Microsoft Excel or OpenOffice.
  • As a database developer or DBA, you do not care about Excel at all.

This article contains a solution for SQL Server data export to CSV files using sqlcmd utility.

Bonus

I am also an author of the gsqlcmd command line utility.
It allows exporting data to plain text, CSV, and HTML much easily.
You may download and use it for free.

Table of Contents

SQL Server Data Export to CSV Example Data

A test table with Excel data types is used in the example:

CREATE TABLE [dbo04].[ExcelTest](
    [ID] [int] NOT NULL,
    [Float] [float] NULL,
    [Datetime] [datetime] NULL,
    [Nvarchar] [nvarchar](255) NULL
 CONSTRAINT [PK_ExcelTest_dbo04] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

The test data:

INSERT [dbo04].[ExcelTest]
    ([ID], [Float], [Datetime], [Nvarchar])
VALUES
    (1, 123.4567, '20110617 01:00:00', N'Hello, SQL Server!')
    (1, NULL, NULL, N'Hello, Excel!')

The following result CSV file ExcelTest.csv is expected:

Id,Float,Datetime,Nvarchar
1,123.4567,2011-06-17 01:00:00,"Hello, SQL Server!"
2,,,"Hello, Excel!"

Pay attention to the following tips:

  • The datetime fields should follow formats shown above to be understandable by Microsoft Excel.
  • Text fields should be quoted. Otherwise, column data comma separates the fields.
  • First two characters of CSV files should not contain capital "L" or "D". Otherwise, Microsoft Excel shows "SYLK: File format is not valid" error message when users open a file.

To top

SQL Server Data Export to CSV using BCP

bcp is an SQL Server command line utility.

There are many questions on the Internet about using bcp utility to export SQL Server data to CSV file.

For example, the following command:

bcp "SELECT * FROM dbo04.ExcelTest" queryout ExcelTest.csv -t, -c -S . -d AzureDemo50 -T

returns the result without column headers:

1,123.4567,2011-06-17 01:00:00.000,Hello, SQL Server!
2,,,Hello, Excel!

However, there is no simple way to get column headers using bcp in a result file.

Use sqlcmd utility instead of bcp.

To top

SQL Server Data Export to CSV using SQLCMD

sqlcmd is an SQL Server command line utility.

sqlcmd can be downloaded separately with the SQL Server Feature Pack. See links below.

Basic Export Command for SQLCMD

The basis export command for sqlcmd:

sqlcmd -S . -d AzureDemo50 -E -s, -W -Q "SELECT * FROM dbo04.ExcelTest" > ExcelTest.csv

where

-S .
Defines the localhost server. For a named instance you can use a parameter like .\SQLEXPRESS.
-d AzureDemo50
Defines the database AzureDemo50.
-E
Defines the trusted connection. Instead, you can use user credentials: -U Username -P Password
-s,
Defines the comma as a column separator. Use -s; for semicolon.
-W
Removes trailing spaces.
-Q "SELECT * FROM dbo04.ExcelTest"
Defines a command line query and exit.
> ExcelTest.csv
Outputs the result to file ExcelTest.csv.

The result of the command:

ID,Float,Datetime,Nvarchar
--,-----,--------,--------
1,123.4567,2011-06-17 01:00:00.000,Hello, SQL Server!
2,NULL,NULL,Hello, Excel!

(2 rows affected)

Final Export Command for SQLCMD

First of all, we modify the query "SELECT * FROM dbo04.ExcelTest" to the following code and place it into the file ExcelTest.sql:

SET NOCOUNT ON
SELECT
    [ID] AS [Id]
    , [Float]
    , CONVERT(varchar(19), [Datetime], 120) AS [Datetime]
    , QUOTENAME([Nvarchar], '"') AS [Nvarchar]
FROM
    [dbo04].[ExcelTest]

Code comments:

  • SET NOCOUNT ON suppresses the message "(2 rows affected)".
  • [ID] AS [Id] prevents the Excel SYLK error message as talked above.
  • CONVERT formats datetime value to Excel formats.
  • QUOTENAME quotes text column data.

The final command is:

sqlcmd -S . -d AzureDemo50 -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B > ExcelTest.csv

where findstr /V /C:"-" /B removes strings like "--,-----,--------,--------".

The resulting file ExcelTest.csv contains the desired result that can be opened with Microsoft Excel:

Id,Float,Datetime,Nvarchar
1,123.4567,2011-06-17 01:00:00,"Hello, SQL Server!"
2,NULL,NULL,"Hello, Excel!"

The last problem is NULL values. sqlcmd utility has no switch to change NULL to empty value!

There are two ways to solve NULL issue:

  1. Use of the ISNULL function in the SQL query to change NULL to ''.
  2. Use of a simple command line utility replace-null.exe.

The example for ISNULL function use:

SET NOCOUNT ON
SELECT
    [ID] AS [Id]
    , ISNULL([Float], '') AS [Float]
    , ISNULL(CONVERT(varchar(19), [Datetime], 120), '')  AS [Datetime]
    , ISNULL(QUOTENAME([Nvarchar], '"'), '') AS [Nvarchar]
FROM
    [dbo04].[ExcelTest]

The final command with replace-null.exe utility is:

sqlcmd -S . -d AzureDemo50 -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B | replace-null.exe > ExcelTest.csv

The result is perfect:

Id,Float,Datetime,Nvarchar
1,123.4567,2011-06-17 01:00:00,"Hello, SQL Server!"
2,,,"Hello, Excel!"

The core code of the replace-null.exe utility using Visual Basic:

Dim line As String = Console.ReadLine()
While line IsNot Nothing
    Console.WriteLine(line.Replace("NULL", ""))
    line = Console.ReadLine()
End While

The source code and the binary file of the replace-null.exe utility are included in the example download package.

To top

SQL Server Data Export to CSV Automation

The simplest way to batch the export is to make a cmd file like this:

@echo off

sqlcmd -S . -d AzureDemo50 -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B | replace-null.exe > ExcelTest.csv

Then add new tasks or modify ones using text editor like Notepad or Far Manager.

We can move the server and credential definition to a separate file like this config.txt:

; Leave Username and Password empty for trusted connection
; Leave Crypt empty for SQL Server without encryption
Server=ko7h266q17.database.windows.net
Database=AzureDemo50
Username=excel_user@ko7h266q17
Password=ExSQL_#02
Crypt=-N

Each pair of a resulting CSV file and a source SQL file or a source SQL query we can define in a separate file like this task.txt:

ExcelTest1.csv=ExcelTest.sql
ExcelTest2.csv=EXEC dbo04.uspExportExcelTestCSV

Then we can use a simple batch file like export-csv.cmd to run multiple CSV export tasks:

@echo off

for /F "eol=; tokens=1* delims==" %%i in (config.txt) do set %%i=%%j

set Credentials=-U %Username% -P %Password%

if .%Username%.==.. set Credentials=-E

for /F "eol=; tokens=1* delims==" %%i in (task.txt) do call :RUN_ONE %%i "%%j"

goto END

:RUN_ONE

set type=-i
if not exist %2 set type=-Q

sqlcmd -S %Server% -d %Database% %Credentials% %Crypt% -s, -W %type% %2 | findstr /V /C:"-" /B | replace-null.exe > %1

goto END

:END

To add a new CSV file to SQL Server export just write a query, put it in an SQL query file, and add the destination-source pair to the task.txt text file.

To top

Conclusion

The article contains a solution for SQL Server data export to CSV using sqlcmd utility.

You can use it to export SQL Server data to your Excel or OpenOffice users with fewer efforts.

Download

Downloads
Exporting SQL Server Data to CSV Using SQLCMD
Shows using the sqlcmd utility for exporting data to CSV
Version: 1.7 | 10/23/2015 | 0.1MB | Article
Exporting SQL Server Data to XML Using BCP
Shows using the bcp utility for exporting SQL Server data to XML
Version: 1.7 | 10/23/2015 | 0.1MB | Article
gsqlcmd (Portable Version) (Free)
Executes SQL queries and scripts, imports and exports data
Version: 3.2 | 03/15/2016 | 8.1MB | Homepage | Article 1 | Article 2
gwebcmd (Portable Version) (Free)
Converts XML, HTML, CSV, and JSON from the web and text files into CSV
Version: 3.1 | 03/15/2016 | 1.1MB | Homepage
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.9 | 06/21/2016 | 20.9MB | Homepage | Article 1 | Article 2

To top

Download

Get SaveToDB Tips & Tricks

Thank you for download!
You may subscribe now for tips & tricks.

You will receive several useful emails to learn SaveToDB step by step.

Name:

Email:

Get gsqlcmd Tips & Tricks

Thank you for download!
You may subscribe now for tips & tricks.

You will receive several useful emails to learn gsqlcmd step by step.

Name:

Email:

Get gwebcmd Tips & Tricks

Thank you for download!
You may subscribe now for tips & tricks.

You will receive several useful emails to learn gwebcmd step by step.

Name:

Email:

Comments  

# John 2016-06-15 22:54
Awesome! This is what I have been looking for. Very simple and step by step. Thank you so much!
Reply | Reply with quote | Quote
# Bert 2015-09-22 18:14
Thanks for this information. I've been using it with great success, but I've noticed that when the CSV files are opened, by default, they will not display Unicode/UTF-8 characters. After some searching, I found that it's possible to write a byte order mark (BOM) prior to running sqlcmd as described above, redirecting the output. See http://superuser.com/a/904882/74320 for the explanation of how to write the BOM using Notepad++ in a batch file.
Reply | Reply with quote | Quote
# Guy Roberto 2015-08-17 11:26
Excellent article. I'm with a problem to use it from PHP, probably because of authority. SQLCMD runs from command line but not from PHP script.
Reply | Reply with quote | Quote
# Ayub Khan M 2015-04-16 06:44
how to protect the CSV with the Password in SQL Server.

Please help on this.
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-05-12 16:13
How do you solve this task?

It seems that you can encrypt the entire folder.
Reply | Reply with quote | Quote
# Ayub Khan M 2015-05-13 04:35
Quoting Sergey Vaselenko:
How do you solve this task?

It seems that you can encrypt the entire folder.


I have creating multiple CSV files using BCP Command and Using a .NET assembly to Combine them and save it as a Excel with password protection. I am Using SQL Server 2012
Reply | Reply with quote | Quote
# Chris 2016-04-05 02:28
Quoting Ayub Khan M:
Quoting Sergey Vaselenko:
How do you solve this task?

It seems that you can encrypt the entire folder.


I have creating multiple CSV files using BCP Command and Using a .NET assembly to Combine them and save it as a Excel with password protection. I am Using SQL Server 2012



Is there a sample code for this task?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-04-05 08:45
This is Ayub's solution. I haven't the code.

Maybe, you may encrypt the entire folder.
Take a look at this article
http://www.groovypost.com/howto/windows-8-7-efs-encrypt-files-folders/
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-05-13 10:20
Thank you very much for your tip!
Reply | Reply with quote | Quote
# Gopal 2015-02-13 00:09
I tried it , but it did not work in SqlServer 2005, can you help me please
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-02-14 13:20
Hi Gopal,
What is not working?
You may also download and try the gsqlcmd utility. It works with SQL Server 2005 definitely.
Reply | Reply with quote | Quote
# Syed 2014-12-24 09:46
Nice article... it tells about your expertise and presentation skills.

Thanks you
Reply | Reply with quote | Quote
# PegH 2014-09-26 20:27
This is a very efficient way to export data from SQL Server!

Thanks!!!
Reply | Reply with quote | Quote
# Adolfo Socorro 2014-07-12 17:30
Great stuff! Thanks. I used this to convert each text column, to remove new lines and CR:

QUOTENAME(REPLACE(REPLACE(@Tex t, CHAR(13), ''), CHAR(10), ''), '"')

Also, I got rid of times in datetime columns by using this:

CONVERT(varchar(19), MyDatetimeCol, 101)
Reply | Reply with quote | Quote
# Linkesh Kanna Velu 2012-10-15 13:38
Nice way of explanation. Anyone can able to easily learn and do by reading through this. Thanks.
Reply | Reply with quote | Quote
# Wim 2012-08-15 13:19
Thank you !
Very helpful posting
Reply | Reply with quote | Quote
# Roshan Gujrathi 2012-07-16 11:35
Simply great, thanks for giving working code with explanation.
Reply | Reply with quote | Quote

Use Excel as a DB front-end
with the SaveToDB add-in

Use Excel as a DB front-end with the SaveToDB add-in