Let's keep in touch

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 don't care about Excel at all.

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

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 [dbo02].[ExcelTest](
    [ID] [int] NOT NULL,
    [Float] [float] NULL,
    [Datetime] [datetime] NULL,
    [Nvarchar] [nvarchar](255) NULL
 CONSTRAINT [PK_ExcelTest_dbo02] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)

The test data:

INSERT [dbo02].[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 undestandable by Microsoft Excel.
  • Text fields should be quoted otherwise column data comma separates the fields.
  • First two chars of CSV file should not contain capital "L" or "D" othewise 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 a SQL Server command line utility.

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

For example, the following command:

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

returns the result without column headers:

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

But 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 a 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 AzureDemo -E -s, -W -Q "SELECT * FROM dbo02.ExcelTest" > ExcelTest.csv

where

-S .
Defines the localhost server. For a named instance you can use parameter like .\SQLEXPRESS.
-d AzureDemo
Defines the database AzureDemo.
-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 dbo02.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 dbo02.ExcelTest" to the following code and place it to the file ExcelTest.sql:

SET NOCOUNT ON
SELECT
    [ID] AS [Id]
    , [Float]
    , CONVERT(varchar(19), [Datetime], 120) AS [Datetime]
    , QUOTENAME([Nvarchar], '"') AS [Nvarchar]
FROM
    [dbo02].[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 AzureDemo -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B > ExcelTest.csv

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

The result 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
    [dbo02].[ExcelTest]

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

sqlcmd -S . -d AzureDemo -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 AzureDemo -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B | replace-null.exe > ExcelTest.csv

And 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=xng46oamrm.database.windows.net
Database=AzureDemo
Username=excel_user@xng46oamrm
Password=ExSQL_#02
Crypt=-N

Each pair of a result 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 dbo02.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 to a 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 a little afforts.

Download

Downloads
Exporting SQL Server Data to CSV Using SQLCMD
Shows using the sqlcmd utility for exporting data to CSV
Version: 1.6 | 12/03/2014 | 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.6 | 12/03/2014 | 0.1MB | Article

Bonus Downloads | Use Express Editions for Free
Command Line Utility GSQLCMD
Executes SQL queries and scripts, imports and exports data
Version: 2.1 | 12/17/2014 | 7.7MB | Homepage | Article 1 | Article 2
Command Line Utility GWEBCMD
Converts XML, HTML, CSV, and JSON from the web and text files into CSV
Version: 2.1 | 12/17/2014 | 1.0MB | Homepage
SaveToDB Suite
Includes products and examples for working with databases and web in Excel
Version: 4.11 | 12/17/2014 | 27.7MB | Homepage | Article 1 | Article 2

To top

Download

Comments  

# 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 free SaveToDB Express

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