SQL SERVER – Export Data AS CSV from Database Using SQLCMD

We have covered multiple times in this blog that we can import CSV to Database very easily. However, recently I received a very interesting question where the user wants to export data with the help of SQLCMD.

The answer is very simple. Here are two different methods:

Method 1: CSV with Column Header

C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,”

Method 2: CSV without Column Header

C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,” -h-1

Additional explanation:

  • -S”,” – sets the delimiter to the comma
  • -w number – sets the length of the csv line before it wraps
  • -W – removes trailing whitespace
  • -h-1 – removes header in CSV (Column Header)
  • -E (use trusted connection) – instead of this use  -U username and -P password

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – Export Data AS CSV from Database Using SQLCMD

  1. Yes it works, but how can you do it with nvarchar columns that can contains commas? Is there a solution that solves this necessity?

    • @Stefano, just use a different delimiter. ^ – a carrot, ! – an exclamation mark, some other value not expected to be in the data you are querying.

      • @Eddy I didn’t mean commas,but quotation marks (inverted commas –> ‘ ). if you have these ones into the string you can’t import and export by csv files with sqlcmd,because INSERT INTO can’t work without recognizing the end of the string.

        • Hi Stefano, I resolved the comma issue by using the following dynamic SQL to quote every field

          SET NOCOUNT ON;

          DECLARE @sql NVARCHAR(MAX) = ”
          DECLARE @tableOrViewName NVARCHAR(30) = ‘schema.TableOrViewName’

          – Dynamic SQL to quote all fields, so that we can generate a valid CSV export file using SQLCMD
          SET @sql = (
          SELECT ‘, QUOTENAME([' + c.name + '], ”””) AS “‘ + c.name + ‘”‘
          FROM sys.columns c
          WHERE c.object_id = OBJECT_ID(@tableOrViewName)
          FOR XML PATH(”)
          )

          SET @sql = ‘ SELECT ‘ +
          SUBSTRING(@sql, 2, 999999999) + — remove the first comma
          ‘ FROM ‘ + @tableOrViewName

          EXEC(@sql)

  2. Hi Mrunal,
    I want to export multiple tables from a particular database into separate csv files . Please let me know how we can automate this task.

    Regards,
    Owais

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