Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > Exporting data to CSV file using SQLCMD/BCP

Exporting data to CSV file using SQLCMD/BCP

When it comes to exporting data there are different options available in SQL Server, such as SQL Server Integration Services, Generating Insert Scripts, BCP, Import and Export Wizard and SQLCMD.

When it comes to exporting data as CSV file, BCP and SQLCMD are the easiest methods. Both are command-line tools which can be used to export data.

General syntax for SQLCMD is as follows:

SQLCMD -S <<ServerName>> -E -Q "EXEC AdventureWorks2008R2.dbo.uspExport"
-s "," -o "C:\DataSqlCmd.csv" -h-1

Where, -S specifies Database Server Name,
-E specifies to use Windows Authentication,
-Q specifies the query to be executed against server,
-s specifies column separator character,
-o specifies the destination file and,
-h-1 specifies that column headers should not be exported.

You can provide a SELECT statement or a Stored Procedure to -Q switch. In general I use stored procedures as it allows to do any formatting required which cannot be done using SQLCMD.

I have created the stored procedure dbo.uspExport as below:

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 BusinessEntityID,

            JobTitle,

            Gender,

            BirthDate

FROM        HumanResources.Employee

The stored procedure simply returns top5 rows which are to be exported.

Executing the above SQLCMD command will generate the CSV file as follows:

          1,Chief Executive Officer                ,M,      1963-03-02
          2,Vice President of Engineering          ,F,      1965-09-01
          3,Engineering Manager                    ,M,      1968-12-13
          4,Senior Tool Designer                   ,M,      1969-01-23
          5,Design Engineer                        ,F,      1946-10-29

Same data can be exported using BCP as follows:

bcp "EXEC AdventureWorks2008R2.dbo.uspExport" queryout "C:\DataBCP.csv" -c -t , -S (local) -T

Here, -c specifies the character type format,
-t specifies the column separator,
-S specifies the database server name and,
-T specifies to use Windows Authentication.

The output generated by BCP is slightly different than of SQLCMD, output generated by BCP is as follows:

1,Chief Executive Officer,M,1963-03-02
2,Vice President of Engineering,F,1965-09-01
3,Engineering Manager,M,1968-12-13
4,Senior Tool Designer,M,1969-01-23
5,Design Engineer,F,1946-10-29

You can see that there is no padding while exporting using BCP by default, same can be achieved by using -W switch with SQLCMD.

 

If you need to implement any "complex" formatting, such as using delimiter for starting and end of row or change data format you can do this inside the stored procedure created for exporting data.

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 '#' + CAST(BusinessEntityID AS NVARCHAR(MAX)) + '#' +

            CAST(JobTitle AS NVARCHAR(MAX)) + '#' +

            CAST(Gender AS NVARCHAR(MAX)) + '#' +

            CONVERT(NVARCHAR(20), BirthDate, 107) + '#'

FROM        HumanResources.Employee

Here I have returned result as a single column with specifying ‘#’ as a delimiter and to denote start/end of a row. So all rows returned by stored procedure will be pre-formatted and -s or -t switches of SQLCMD/BCP will have no effect.

#1#Chief Executive Officer#M#Mar 02, 1963#
#2#Vice President of Engineering#F#Sep 01, 1965#
#3#Engineering Manager#M#Dec 13, 1968#
#4#Senior Tool Designer#M#Jan 23, 1969#
#5#Design Engineer#F#Oct 29, 1946#

 

To see all switches available for BCP/SQLCMD refer BOL links or execute them with -? at command prompt.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Nance
    August 31, 2012 at 9:32 pm

    I am generating the csv file, but the file contain a blank line at the end of the data. How can I eliminate it?

  2. J.
    July 12, 2013 at 3:35 am

    I am not able to generate a .csv. I keep getting “Incorrect syntax near queryout” when i try to execute the bcp.

    bcp ‘EXEC Sales_Staging.dbo.Export’ queryout ‘C:\DataBCP.csv’ -c -t , -S (local) -T

  3. Pons
    July 24, 2013 at 6:33 pm

    How to export more than one sheet.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 285 other followers

%d bloggers like this: