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.4 | 11/02/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.3 | 11/02/2016 | 1.1MB | Homepage
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.10 | 11/02/2016 | 20.9MB | Homepage | Article 1 | Article 2

To top