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

8 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

  3. I struggled with this all morning before coming to the unfortunate conclusion that neither sqlcmd nor Invoke-SqlCmd is up to this task without a bunch of corner cases and/or dependencies. Here’s a straight PowerShell + .Net implementation which supports parameterized queries and that you can pipe into Export-CSV:

    function QueryDatabase($serverName, $instanceName, $databaseName, $queryTemplate)
    {
    # Build the connection string.
    $connectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $connectionStringBuilder["Data Source"] = “$serverName\$instanceName”
    $connectionStringBuilder["Integrated Security"] = $true
    $connectionStringBuilder["Initial Catalog"] = $databaseName
    $connectionString = $connectionStringBuilder.ConnectionString

    # Open the connection.
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString

    # Create the command and populate the argument.
    $command = New-Object System.Data.SqlClient.SqlCommand $queryTemplate, $connection
    for ($index = 0; $index -lt $args.Length; $index++)
    {
    $null = $command.Parameters.Add((New-Object System.Data.SqlClient.SqlParameter “Arg$index”, $args[$index]))
    }

    # Hook the command to an adapter.
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $adapter.SelectCommand = $command

    # Run the query.
    $resultTable = New-Object System.Data.DataTable
    $null = $adapter.Fill($resultTable)

    # Convert the output to PSObjects
    foreach ($thisRow in $resultTable.Rows)
    {
    $newObject = @{}
    foreach ($thisColumn in $resultTable.Columns)
    {
    $newObject[$thisColumn.ColumnName] = $thisRow[$thisColumn.ColumnName]
    }
    New-Object PSObject -Property $newObject
    }

    # Close the connection.
    $connection.Close()
    }

    • Oh yeah, call it like this:

      QueryDatabase -server “foo” -instance “bar” -database “zang” -query “SELECT * FROM MyTable WHERE Xyz=@Arg0 AND Abc=@Arg1″ $xyz $abc | Export-CSV output.csv

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