How to export data as CSV format from SQL Server using sqlcmd

Question

I can quite easily dump data into a text file such as   sqlcmd -S myServer -d myDB -E -Q  select col1  col2  col3 from SomeTable        -o  MyData txt    However  I have looked at the help files for SQLCMD but have not seen an option specifically for CSV    Is there a way to dump data from a table into a CSV text file using SQLCMD

User · Answer

Since following 2 reasons  you should run my solution in CMD    There may be double quotes in the query Login username  amp  password is sometimes necessary to query a remote SQL Server instance  sqlcmd -U  your User   -P your password  -S  your remote Server  -d  your databasename   -i  query txt  -o  output csv  -s    -w 700

User · Answer

Alternate option with BCP   exec master  xp cmdshell  BCP  sp who  QUERYOUT C  av sp who txt -S MC0XENTC -T -c

User · Answer

This answer builds on the solution from  iain-elder  which works well except for the large database case  as pointed out in his solution   The entire table needs to fit in your system s memory  and for me this was not an option  I suspect the best solution would use the System Data SqlClient SqlDataReader and a custom CSV serializer  see here for an example  or another language with an MS SQL driver and CSV serialization  In the spirit of the original question which was probably looking for a no dependency solution  the PowerShell code below worked for me  It is very slow and inefficient especially in instantiating the  data array and calling Export-Csv in append mode for every  chunk size lines     chunk size   10000  command   New-Object System Data SqlClient SqlCommand  command CommandText    SELECT   FROM  lt TABLENAME gt    command Connection    connection  connection open    reader    command ExecuteReader     read    TRUE while  read        counter 0      DataTable   New-Object System Data DataTable      first  TRUE      try           while  read    reader Read                   count    reader FieldCount             if   first                   for  i 0   i -lt  count   i                          col   New-Object System Data DataColumn  reader GetName  i                       DataTable Columns Add  col                                     first  FALSE                               Better way to do this               data                  emptyObj   New-Object System Object             for  i 1   i -le  count   i                      data      emptyObj                             reader GetValues  data    out-null              DataRow    DataTable NewRow                DataRow ItemArray    data              DataTable Rows Add  DataRow               counter    1             if   counter -eq  chunk size                   break                                  DataTable   Export-Csv  output csv  -NoTypeInformation -Append      catch           ErrorMessage      Exception Message         Write-Output  ErrorMessage          read  FALSE          connection Close           exit          connection close

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   SQL Server 2016 includes the SqlServer module  which contains the Invoke-Sqlcmd cmdlet  which you ll have even if you just install SSMS 2016  Prior to that  SQL Server 2012 included the old SQLPS module  which would change the current directory to SQLSERVER   when the module was first used  among other bugs  so for it  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   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   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  A full path for this value is safest if you are stuck using the old SQLPS module   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

User · Answer

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

User · Answer

You can do it in a hackish way  Careful using the sqlcmd hack  If the data has double quotes or commas you will run into trouble   You can use a simple script to do it properly                                                                       Data Exporter                                                                                                                       Description  Allows the output of data to CSV file from a SQL           statement to either Oracle  SQL Server  or MySQL            Author  C  Peter Chen  http   dev-notes com                       Version Tracker                                                         1 0   20080414 Original version                               1 1   20080807 Added email functionality                                                                                    option explicit dim dbType  dbHost  dbName  dbUser  dbPass  outputFile  email  subj  body  smtp  smtpPort  sqlstr                      Configuration                     dbType    oracle                    Valid values   oracle    sqlserver    mysql  dbHost    dbhost                    Hostname of the database server dbName    dbname                    Name of the database SID dbUser    username                  Name of the user dbPass    password                  Password of the above-named user outputFile    c  output csv         Path and file name of the output CSV file email    email me here              Enter email here should you wish to email the CSV file  as attachment   if no email  leave it as empty string      subj    Email Subject             The subject of your email  required only if you send the CSV over email   body    Put a message here        The body of your email  required only if you send the CSV over email   smtp    mail server com           Name of your SMTP server  required only if you send the CSV over email   smtpPort   25                     SMTP port used by your server  usually 25  required only if you send the CSV over email sqlStr    select user from dual     SQL statement you wish to execute                         End Configuration                            dim fso  conn   Create filesystem object  set fso   CreateObject  Scripting FileSystemObject     Database connection info set Conn   CreateObject  ADODB connection   Conn ConnectionTimeout   30 Conn CommandTimeout   30 if dbType    oracle  then     conn open  Provider MSDAORA 1 User ID    amp  dbUser  amp    Password    amp  dbPass  amp    Data Source    amp  dbName  amp    Persist Security Info False   elseif dbType    sqlserver  then     conn open  Driver  SQL Server  Server    amp  dbHost  amp    Database    amp  dbName  amp    Uid    amp  dbUser  amp    Pwd    amp  dbPass  amp       elseif dbType    mysql  then     conn open  DRIVER  MySQL ODBC 3 51 Driver   SERVER    amp  dbHost  amp    PORT 3306 DATABASE    amp  dbName  amp     UID    amp  dbUser  amp     PASSWORD    amp  dbPass  amp     OPTION 3   end if    Subprocedure to generate data   Two parameters      1  fPath where to create the file     2  sqlstr the database query sub MakeDataFile fPath  sqlstr      dim a  showList  intcount     set a   fso createtextfile fPath       set showList   conn execute sqlstr      for intcount   0 to showList fields count -1         if intcount  lt  gt  showList fields count-1 then             a write       amp  showList fields intcount  name  amp                else             a write       amp  showList fields intcount  name  amp               end if     next     a writeline         do while not showList eof         for intcount   0 to showList fields count - 1             if intcount  lt  gt  showList fields count - 1 then                 a write       amp  showList fields intcount  value  amp                    else                 a write       amp  showList fields intcount  value  amp                   end if         next         a writeline            showList movenext     loop     showList close     set showList   nothing      set a   nothing end sub    Call the subprocedure call MakeDataFile outputFile sqlstr     Close set fso   nothing conn close set conn   nothing  if email  lt  gt     then     dim objMessage     Set objMessage   CreateObject  CDO Message       objMessage Subject    Test Email from vbs      objMessage From   email     objMessage To   email     objMessage TextBody    Please see attached file       objMessage AddAttachment outputFile      objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration sendusing     2     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserver     smtp     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserverport     smtpPort  objMessage Configuration Fields Update      objMessage Send end if   You re all done    Enjoy the file created  msgbox  Data Writer Done      Source  Writing SQL output to CSV with VBScript

User · Answer

Alternate option with BCP   exec master  xp cmdshell  BCP  sp who  QUERYOUT C  av sp who txt -S MC0XENTC -T -c

User · Answer

An answer above almost solved it for me but it does not correctly create a parsed CSV    Here s my version   sqlcmd -S myurl com -d MyAzureDB -E -s  -W -i mytsql sql   findstr  V  C  -   B  gt  parsed correctly csv   Someone saying that sqlcmd is outdated in favor of some PowerShell alternative is forgetting that sqlcmd isn t just for Windows  I m on Linux  and when on Windows I avoid PS anyway    Having said all that  I do find bcp easier

User · Answer

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  nbsp  remove trailing spaces from each individual field -s     nbsp  sets the column seperator to the comma      -w 999  nbsp  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

User · Answer

A note for anyone looking to do this but also have the column headers  this is the solution that I used an a batch file   sqlcmd -S servername -U username -P password -d database -Q  set nocount on  set ansi warnings off  sql query here   -o output tmp -s     -W type output tmp   findstr  V  -   -  gt  output csv del output tmp   This outputs the initial results  including the ---- ---- separators between the headers and data  into a temp file  then removes that line by filtering it out through findstr  Note that it s not perfect since it s filtering out - - mdash it won t work if there s only one column in the output  and it will also filter out legitimate lines that contain that string

User · Answer

You can do it in a hackish way  Careful using the sqlcmd hack  If the data has double quotes or commas you will run into trouble   You can use a simple script to do it properly                                                                       Data Exporter                                                                                                                       Description  Allows the output of data to CSV file from a SQL           statement to either Oracle  SQL Server  or MySQL            Author  C  Peter Chen  http   dev-notes com                       Version Tracker                                                         1 0   20080414 Original version                               1 1   20080807 Added email functionality                                                                                    option explicit dim dbType  dbHost  dbName  dbUser  dbPass  outputFile  email  subj  body  smtp  smtpPort  sqlstr                      Configuration                     dbType    oracle                    Valid values   oracle    sqlserver    mysql  dbHost    dbhost                    Hostname of the database server dbName    dbname                    Name of the database SID dbUser    username                  Name of the user dbPass    password                  Password of the above-named user outputFile    c  output csv         Path and file name of the output CSV file email    email me here              Enter email here should you wish to email the CSV file  as attachment   if no email  leave it as empty string      subj    Email Subject             The subject of your email  required only if you send the CSV over email   body    Put a message here        The body of your email  required only if you send the CSV over email   smtp    mail server com           Name of your SMTP server  required only if you send the CSV over email   smtpPort   25                     SMTP port used by your server  usually 25  required only if you send the CSV over email sqlStr    select user from dual     SQL statement you wish to execute                         End Configuration                            dim fso  conn   Create filesystem object  set fso   CreateObject  Scripting FileSystemObject     Database connection info set Conn   CreateObject  ADODB connection   Conn ConnectionTimeout   30 Conn CommandTimeout   30 if dbType    oracle  then     conn open  Provider MSDAORA 1 User ID    amp  dbUser  amp    Password    amp  dbPass  amp    Data Source    amp  dbName  amp    Persist Security Info False   elseif dbType    sqlserver  then     conn open  Driver  SQL Server  Server    amp  dbHost  amp    Database    amp  dbName  amp    Uid    amp  dbUser  amp    Pwd    amp  dbPass  amp       elseif dbType    mysql  then     conn open  DRIVER  MySQL ODBC 3 51 Driver   SERVER    amp  dbHost  amp    PORT 3306 DATABASE    amp  dbName  amp     UID    amp  dbUser  amp     PASSWORD    amp  dbPass  amp     OPTION 3   end if    Subprocedure to generate data   Two parameters      1  fPath where to create the file     2  sqlstr the database query sub MakeDataFile fPath  sqlstr      dim a  showList  intcount     set a   fso createtextfile fPath       set showList   conn execute sqlstr      for intcount   0 to showList fields count -1         if intcount  lt  gt  showList fields count-1 then             a write       amp  showList fields intcount  name  amp                else             a write       amp  showList fields intcount  name  amp               end if     next     a writeline         do while not showList eof         for intcount   0 to showList fields count - 1             if intcount  lt  gt  showList fields count - 1 then                 a write       amp  showList fields intcount  value  amp                    else                 a write       amp  showList fields intcount  value  amp                   end if         next         a writeline            showList movenext     loop     showList close     set showList   nothing      set a   nothing end sub    Call the subprocedure call MakeDataFile outputFile sqlstr     Close set fso   nothing conn close set conn   nothing  if email  lt  gt     then     dim objMessage     Set objMessage   CreateObject  CDO Message       objMessage Subject    Test Email from vbs      objMessage From   email     objMessage To   email     objMessage TextBody    Please see attached file       objMessage AddAttachment outputFile      objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration sendusing     2     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserver     smtp     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserverport     smtpPort  objMessage Configuration Fields Update      objMessage Send end if   You re all done    Enjoy the file created  msgbox  Data Writer Done      Source  Writing SQL output to CSV with VBScript

User · Answer

This answer builds on the solution from  iain-elder  which works well except for the large database case  as pointed out in his solution   The entire table needs to fit in your system s memory  and for me this was not an option  I suspect the best solution would use the System Data SqlClient SqlDataReader and a custom CSV serializer  see here for an example  or another language with an MS SQL driver and CSV serialization  In the spirit of the original question which was probably looking for a no dependency solution  the PowerShell code below worked for me  It is very slow and inefficient especially in instantiating the  data array and calling Export-Csv in append mode for every  chunk size lines     chunk size   10000  command   New-Object System Data SqlClient SqlCommand  command CommandText    SELECT   FROM  lt TABLENAME gt    command Connection    connection  connection open    reader    command ExecuteReader     read    TRUE while  read        counter 0      DataTable   New-Object System Data DataTable      first  TRUE      try           while  read    reader Read                   count    reader FieldCount             if   first                   for  i 0   i -lt  count   i                          col   New-Object System Data DataColumn  reader GetName  i                       DataTable Columns Add  col                                     first  FALSE                               Better way to do this               data                  emptyObj   New-Object System Object             for  i 1   i -le  count   i                      data      emptyObj                             reader GetValues  data    out-null              DataRow    DataTable NewRow                DataRow ItemArray    data              DataTable Rows Add  DataRow               counter    1             if   counter -eq  chunk size                   break                                  DataTable   Export-Csv  output csv  -NoTypeInformation -Append      catch           ErrorMessage      Exception Message         Write-Output  ErrorMessage          read  FALSE          connection Close           exit          connection close

User · Answer

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

User · Answer

A note for anyone looking to do this but also have the column headers  this is the solution that I used an a batch file   sqlcmd -S servername -U username -P password -d database -Q  set nocount on  set ansi warnings off  sql query here   -o output tmp -s     -W type output tmp   findstr  V  -   -  gt  output csv del output tmp   This outputs the initial results  including the ---- ---- separators between the headers and data  into a temp file  then removes that line by filtering it out through findstr  Note that it s not perfect since it s filtering out - - mdash it won t work if there s only one column in the output  and it will also filter out legitimate lines that contain that string

User · Answer

Since following 2 reasons  you should run my solution in CMD    There may be double quotes in the query Login username  amp  password is sometimes necessary to query a remote SQL Server instance  sqlcmd -U  your User   -P your password  -S  your remote Server  -d  your databasename   -i  query txt  -o  output csv  -s    -w 700

User · Answer

An answer above almost solved it for me but it does not correctly create a parsed CSV    Here s my version   sqlcmd -S myurl com -d MyAzureDB -E -s  -W -i mytsql sql   findstr  V  C  -   B  gt  parsed correctly csv   Someone saying that sqlcmd is outdated in favor of some PowerShell alternative is forgetting that sqlcmd isn t just for Windows  I m on Linux  and when on Windows I avoid PS anyway    Having said all that  I do find bcp easier

User · Answer

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   SQL Server 2016 includes the SqlServer module  which contains the Invoke-Sqlcmd cmdlet  which you ll have even if you just install SSMS 2016  Prior to that  SQL Server 2012 included the old SQLPS module  which would change the current directory to SQLSERVER   when the module was first used  among other bugs  so for it  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   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   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  A full path for this value is safest if you are stuck using the old SQLPS module   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

User · Answer

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  nbsp  remove trailing spaces from each individual field -s     nbsp  sets the column seperator to the comma      -w 999  nbsp  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

User · Answer

Usually sqlcmd comes with bcp utility  as part of mssql-tools  which exports into CSV by default   Usage   bcp  dbtable   query   in   out   queryout   format  datafile   For example   bcp exe MyTable out data csv     To dump all tables into corresponding CSV files  here is the Bash script      usr bin env bash   Script to dump all tables from SQL Server into CSV files via bcp     file  bcp-dump sh server  sql example com    Change this  user  USER    Change this  pass  PASS    Change this  dbname  DBNAME    Change this  creds  -S   server  -U   user  -P   pass  -d   dbname   sqlcmd  creds -Q  SELECT   FROM sysobjects sobjects   gt  objects lst sqlcmd  creds -Q  SELECT   FROM information schema routines   gt  routines lst sqlcmd  creds -Q  sp tables    tail -n  3   head -n -2  gt  sp tables lst sqlcmd  creds -Q  SELECT name FROM sysobjects sobjects WHERE xtype    U     tail -n  3   head -n -2  gt  tables lst  for table in    lt tables lst   do   sqlcmd  creds -Q  exec sp columns  table   gt   table desc  amp  amp      bcp  table out  table csv -S  server -U  user -P  pass -d  dbname -c done

User · Answer

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

User · Answer

Usually sqlcmd comes with bcp utility  as part of mssql-tools  which exports into CSV by default   Usage   bcp  dbtable   query   in   out   queryout   format  datafile   For example   bcp exe MyTable out data csv     To dump all tables into corresponding CSV files  here is the Bash script      usr bin env bash   Script to dump all tables from SQL Server into CSV files via bcp     file  bcp-dump sh server  sql example com    Change this  user  USER    Change this  pass  PASS    Change this  dbname  DBNAME    Change this  creds  -S   server  -U   user  -P   pass  -d   dbname   sqlcmd  creds -Q  SELECT   FROM sysobjects sobjects   gt  objects lst sqlcmd  creds -Q  SELECT   FROM information schema routines   gt  routines lst sqlcmd  creds -Q  sp tables    tail -n  3   head -n -2  gt  sp tables lst sqlcmd  creds -Q  SELECT name FROM sysobjects sobjects WHERE xtype    U     tail -n  3   head -n -2  gt  tables lst  for table in    lt tables lst   do   sqlcmd  creds -Q  exec sp columns  table   gt   table desc  amp  amp      bcp  table out  table csv -S  server -U  user -P  pass -d  dbname -c done

User · Answer

You can do it in a hackish way  Careful using the sqlcmd hack  If the data has double quotes or commas you will run into trouble   You can use a simple script to do it properly                                                                       Data Exporter                                                                                                                       Description  Allows the output of data to CSV file from a SQL           statement to either Oracle  SQL Server  or MySQL            Author  C  Peter Chen  http   dev-notes com                       Version Tracker                                                         1 0   20080414 Original version                               1 1   20080807 Added email functionality                                                                                    option explicit dim dbType  dbHost  dbName  dbUser  dbPass  outputFile  email  subj  body  smtp  smtpPort  sqlstr                      Configuration                     dbType    oracle                    Valid values   oracle    sqlserver    mysql  dbHost    dbhost                    Hostname of the database server dbName    dbname                    Name of the database SID dbUser    username                  Name of the user dbPass    password                  Password of the above-named user outputFile    c  output csv         Path and file name of the output CSV file email    email me here              Enter email here should you wish to email the CSV file  as attachment   if no email  leave it as empty string      subj    Email Subject             The subject of your email  required only if you send the CSV over email   body    Put a message here        The body of your email  required only if you send the CSV over email   smtp    mail server com           Name of your SMTP server  required only if you send the CSV over email   smtpPort   25                     SMTP port used by your server  usually 25  required only if you send the CSV over email sqlStr    select user from dual     SQL statement you wish to execute                         End Configuration                            dim fso  conn   Create filesystem object  set fso   CreateObject  Scripting FileSystemObject     Database connection info set Conn   CreateObject  ADODB connection   Conn ConnectionTimeout   30 Conn CommandTimeout   30 if dbType    oracle  then     conn open  Provider MSDAORA 1 User ID    amp  dbUser  amp    Password    amp  dbPass  amp    Data Source    amp  dbName  amp    Persist Security Info False   elseif dbType    sqlserver  then     conn open  Driver  SQL Server  Server    amp  dbHost  amp    Database    amp  dbName  amp    Uid    amp  dbUser  amp    Pwd    amp  dbPass  amp       elseif dbType    mysql  then     conn open  DRIVER  MySQL ODBC 3 51 Driver   SERVER    amp  dbHost  amp    PORT 3306 DATABASE    amp  dbName  amp     UID    amp  dbUser  amp     PASSWORD    amp  dbPass  amp     OPTION 3   end if    Subprocedure to generate data   Two parameters      1  fPath where to create the file     2  sqlstr the database query sub MakeDataFile fPath  sqlstr      dim a  showList  intcount     set a   fso createtextfile fPath       set showList   conn execute sqlstr      for intcount   0 to showList fields count -1         if intcount  lt  gt  showList fields count-1 then             a write       amp  showList fields intcount  name  amp                else             a write       amp  showList fields intcount  name  amp               end if     next     a writeline         do while not showList eof         for intcount   0 to showList fields count - 1             if intcount  lt  gt  showList fields count - 1 then                 a write       amp  showList fields intcount  value  amp                    else                 a write       amp  showList fields intcount  value  amp                   end if         next         a writeline            showList movenext     loop     showList close     set showList   nothing      set a   nothing end sub    Call the subprocedure call MakeDataFile outputFile sqlstr     Close set fso   nothing conn close set conn   nothing  if email  lt  gt     then     dim objMessage     Set objMessage   CreateObject  CDO Message       objMessage Subject    Test Email from vbs      objMessage From   email     objMessage To   email     objMessage TextBody    Please see attached file       objMessage AddAttachment outputFile      objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration sendusing     2     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserver     smtp     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserverport     smtpPort  objMessage Configuration Fields Update      objMessage Send end if   You re all done    Enjoy the file created  msgbox  Data Writer Done      Source  Writing SQL output to CSV with VBScript

User · Answer

You can do it in a hackish way  Careful using the sqlcmd hack  If the data has double quotes or commas you will run into trouble   You can use a simple script to do it properly                                                                       Data Exporter                                                                                                                       Description  Allows the output of data to CSV file from a SQL           statement to either Oracle  SQL Server  or MySQL            Author  C  Peter Chen  http   dev-notes com                       Version Tracker                                                         1 0   20080414 Original version                               1 1   20080807 Added email functionality                                                                                    option explicit dim dbType  dbHost  dbName  dbUser  dbPass  outputFile  email  subj  body  smtp  smtpPort  sqlstr                      Configuration                     dbType    oracle                    Valid values   oracle    sqlserver    mysql  dbHost    dbhost                    Hostname of the database server dbName    dbname                    Name of the database SID dbUser    username                  Name of the user dbPass    password                  Password of the above-named user outputFile    c  output csv         Path and file name of the output CSV file email    email me here              Enter email here should you wish to email the CSV file  as attachment   if no email  leave it as empty string      subj    Email Subject             The subject of your email  required only if you send the CSV over email   body    Put a message here        The body of your email  required only if you send the CSV over email   smtp    mail server com           Name of your SMTP server  required only if you send the CSV over email   smtpPort   25                     SMTP port used by your server  usually 25  required only if you send the CSV over email sqlStr    select user from dual     SQL statement you wish to execute                         End Configuration                            dim fso  conn   Create filesystem object  set fso   CreateObject  Scripting FileSystemObject     Database connection info set Conn   CreateObject  ADODB connection   Conn ConnectionTimeout   30 Conn CommandTimeout   30 if dbType    oracle  then     conn open  Provider MSDAORA 1 User ID    amp  dbUser  amp    Password    amp  dbPass  amp    Data Source    amp  dbName  amp    Persist Security Info False   elseif dbType    sqlserver  then     conn open  Driver  SQL Server  Server    amp  dbHost  amp    Database    amp  dbName  amp    Uid    amp  dbUser  amp    Pwd    amp  dbPass  amp       elseif dbType    mysql  then     conn open  DRIVER  MySQL ODBC 3 51 Driver   SERVER    amp  dbHost  amp    PORT 3306 DATABASE    amp  dbName  amp     UID    amp  dbUser  amp     PASSWORD    amp  dbPass  amp     OPTION 3   end if    Subprocedure to generate data   Two parameters      1  fPath where to create the file     2  sqlstr the database query sub MakeDataFile fPath  sqlstr      dim a  showList  intcount     set a   fso createtextfile fPath       set showList   conn execute sqlstr      for intcount   0 to showList fields count -1         if intcount  lt  gt  showList fields count-1 then             a write       amp  showList fields intcount  name  amp                else             a write       amp  showList fields intcount  name  amp               end if     next     a writeline         do while not showList eof         for intcount   0 to showList fields count - 1             if intcount  lt  gt  showList fields count - 1 then                 a write       amp  showList fields intcount  value  amp                    else                 a write       amp  showList fields intcount  value  amp                   end if         next         a writeline            showList movenext     loop     showList close     set showList   nothing      set a   nothing end sub    Call the subprocedure call MakeDataFile outputFile sqlstr     Close set fso   nothing conn close set conn   nothing  if email  lt  gt     then     dim objMessage     Set objMessage   CreateObject  CDO Message       objMessage Subject    Test Email from vbs      objMessage From   email     objMessage To   email     objMessage TextBody    Please see attached file       objMessage AddAttachment outputFile      objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration sendusing     2     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserver     smtp     objMessage Configuration Fields Item   http   schemas microsoft com cdo configuration smtpserverport     smtpPort  objMessage Configuration Fields Update      objMessage Send end if   You re all done    Enjoy the file created  msgbox  Data Writer Done      Source  Writing SQL output to CSV with VBScript

[sql-server] How to export data as CSV format from SQL Server using sqlcmd?

Examples related to sql-server

Examples related to file

Examples related to csv

Examples related to sqlcmd