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)
15 Comments. Leave new
Awesome sir
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)
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
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
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
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,
Thank you good work.
This is great. Thank you. How can I use this code and not have the —— lines show under the heading?
>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
How to I export Chinese /special characters to csv using sqlcmd
How to add text qualifier to the output text file