How to export data as CSV format from SQL Server using sqlcmd?
Is this not bcp
was meant for?
bcp "select col1, col2, col3 from database.schema.SomeTable" queryout "c:\MyData.txt" -c -t"," -r"\n" -S ServerName -T
Run this from your command line to check the syntax.
bcp /?
For example:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
[-d database name]
Please, note that bcp
can not output column headers.
See: bcp Utility docs page.
Example from the above page:
bcp.exe MyTable out "D:\data.csv" -T -c -C 65001 -t , ...
sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
-Q "select bar from foo" ^
-W -w 999 -s","
The last line contains CSV-specific options.
-W
remove trailing spaces from each individual field-s","
sets the column seperator to the comma (,)-w 999
sets the row width to 999 chars
scottm's answer is very close to what I use, but I find the -W
to be a really nice addition: I needn't trim whitespace when I consume the CSV elsewhere.
Also see the MSDN sqlcmd reference. It puts the /?
option's output to shame.
You can run something like this:
sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.csv" -h-1 -s"," -w 700
-h-1
removes column name headers from the result-s","
sets the column seperator to ,-w 700
sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)
With PowerShell you can solve the problem neatly by piping Invoke-Sqlcmd
into Export-Csv
.
#Requires -Module SqlServer
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
-Database AdventureWorksDW2012 `
-Server localhost |
Export-Csv -NoTypeInformation `
-Path "DimDate.csv" `
-Encoding UTF8
Invoke-Sqlcmd
is the PowerShell equivalent of sqlcmd.exe. Instead of text it outputs System.Data.DataRow
objects.
The -Query
parameter works like the -Q
parameter of sqlcmd.exe. Pass it a SQL query that describes the data you want to export.
The -Database
parameter works like the -d
parameter of sqlcmd.exe. Pass it the name of the database that contains the data to be exported.
The -Server
parameter works like the -S
parameter of sqlcmd.exe. Pass it the name of the server that contains the data to be exported.
Export-CSV
is a PowerShell cmdlet that serializes generic objects to CSV. It ships with PowerShell.
The -NoTypeInformation
parameter suppresses extra output that is not part of the CSV format. By default the cmdlet writes a header with type information. It lets you know the type of the object when you deserialize it later with Import-Csv
, but it confuses tools that expect standard CSV.
The -Path
parameter works like the -o
parameter of sqlcmd.exe.
The -Encoding
parameter works like the -f
or -u
parameters of sqlcmd.exe. By default Export-Csv outputs only ASCII characters and replaces all others with question marks. Use UTF8 instead to preserve all characters and stay compatible with most other tools.
The main advantage of this solution over sqlcmd.exe or bcp.exe is that you don't have to hack the command to output valid CSV. The Export-Csv cmdlet handles it all for you.
The main disadvantage is that Invoke-Sqlcmd
reads the whole result set before passing it along the pipeline. Make sure you have enough memory for the whole result set you want to export.
It may not work smoothly for billions of rows. If that's a problem, you could try the other tools, or roll your own efficient version of Invoke-Sqlcmd
using System.Data.SqlClient.SqlDataReader class.
Differences between SQL Server versions
As of SQL Server 2016, Invoke-Sqlcmd
ships as part of the SqlServer
module.
SQL Server 2012 instead has the old SQLPS module. When the module is imported, it changes the current location to SQLSERVER:\
. So you'll need to change the #Requires
line above to:
Push-Location $PWD
Import-Module -Name SQLPS
# dummy query to catch initial surprise directory change
Invoke-Sqlcmd -Query "SELECT 1" `
-Database AdventureWorksDW2012 `
-Server localhost |Out-Null
Pop-Location
# actual Invoke-Sqlcmd |Export-Csv pipeline
A full path for Export-Csv
's -Path
parameter is safest if you are stuck using the old SQLPS module.
To adapt the example for SQL Server 2008 and 2008 R2, remove the #Requires
line entirely and use the sqlps.exe utility instead of the standard PowerShell host.