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

Additional note: The CSV file format is not standardized. The basic idea of separating fields with a comma is clear, but that idea gets complicated when the field data may also contain commas or even embedded line-breaks. CSV is a common data exchange format that is widely supported by consumer, business, and scientific applications.

Reference : Pinal Dave (https://blog.sqlauthority.com)

CSV, SQL Scripts, SQL Server
Previous Post
Personal Technology – A Quick Note on Good Elevator Etiquette
Next Post
SQL SERVER – SQL Server Configuration Checking – A Must Do for Every DBA – Notes from the Field #004

Related Posts

15 Comments. Leave new

  • Avinash Reddy Munnangi
    November 25, 2013 9:03 am

    Awesome sir

    Reply
  • Stefano Micheletti
    November 25, 2013 3:16 pm

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

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

      Reply
      • Stefano Micheletti
        November 27, 2013 12:49 am

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

  • 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

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

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

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

    Reply
  • 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,

    Reply
  • Konlayut Turakit
    November 26, 2015 7:22 pm

    Thank you good work.

    Reply
  • Dominick Levi
    November 4, 2016 1:25 am

    This is great. Thank you. How can I use this code and not have the —— lines show under the heading?

    Reply
  • >the —— lines show under the heading

    sqlcmd -S SERVER -d DATABASE -E -K ReadOnly -Q “SET NOCOUNT ON; SELECT * FROM dbo.Table” -s”|,” -W -w 2000 -M |findstr /v /c:”—” >test.csv

    Reply
  • How to I export Chinese /special characters to csv using sqlcmd

    Reply
  • How to add text qualifier to the output text file

    Reply

Leave a Reply

Menu
Exit mobile version