SQL Server Export to Excel using bcp/sqlcmd Utilities and CSV Files

Introduction

This article describes a solution of exporting SQL Server data to CSV files using the sqlcmd utility.

You can export SQL Server data to CSV files and open the CSV files in Microsoft Excel files.

This solution is much easier than exporting Excel files and has several advantages:

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

Table of Contents

• Introduction
• SQL Server Data Export to CSV Example Data
• SQL Server Data Export to CSV using BCP
• SQL Server Data Export to CSV using SQLCMD
• Automation of SQL Server Data Export to CSV
• Conclusion
• Download
• See Also

SQL Server Data Export to CSV Example Data

We will use the following table in the article:

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 table contains the following 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!')

So, we expect the following result in the ExcelTest.csv CSV file:

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

Important notes:

• The datetime fields should follow the yyyyMMdd HH:mm:ss format to use in Microsoft Excel.
• Text fields should be quoted as the column data contain commas.
• 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.

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!

There is no simple way to get column headers in a result file with bcp.

So, just use the sqlcmd utility instead of bcp.

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 download 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 .

The option defines the localhost server. Use .\SQLEXPRESS for a named SQL Express instance.

-d AzureDemo50

The option defines the database AzureDemo50.

-E

The option defines the trusted connection. You can use user credentials instead: -U Username -P Password

-s,

The option defines the comma as a column separator. Use -s; for semicolon.

-W

The option removes trailing spaces.

-Q "SELECT * FROM dbo04.ExcelTest"

The option defines a command line query and exit.

> ExcelTest.csv

The command redirects the data to the output file.

The command has the following result:

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 values 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. The sqlcmd utility has no switch to change NULL to an empty value!

There are two ways to solve the NULL issue:

1. Using the ISNULL or COALESCE function.
2. Using a simple command line utility, replace-null.exe.

Here is an example for the ISNULL function:

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]

Here is the final command with the replace-null.exe utility:

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

The result is perfect in both cases:

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.

Automation of SQL Server Data Export to CSV

A simple batch file looks like this:

@echo off

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

We can create a separate text file like config.txt to define the server, database and credentials:

; 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

Also, we can place pairs of a resulting CSV file and a source SQL file in a separate file like task.txt:

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

Then we can use a batch file like export-csv.cmd to run multiple 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

Conclusion

The article shows how to export data from SQL Server to CSV using the sqlcmd utility.

It contains several tricks and a batch file that you can use to export data as is.

Download