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

10 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?

    Like

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

      Like

      • @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.

        Like

        • 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)

          Like

  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

    Like

  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()
    }

    Like

    • 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

      Like

  4. when we execute this CSV file conversion sql command,. where is my CSV file saved? im successfully ran this command but unable to find appropriate csv file. Pl. help me Bro

    Like

  5. Hi,
    While exporting the data from SQL to CSV , the column which contains the value starts from ‘0’ is not reflecting in the excel sheet, what will be the solution for the same.
    Plz help.
    regards,

    Like

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