Export table from database to csv file

Question

I want to  Export table from sql server database to a comma delimited csv file without using sql Server import export wizard  I want to do it using a query because I want to use the query in automation  Is it possible  I searched for that and didn t find a good answer

User · Answer

rsubmit  options missing 0  ods listing close  ods csv file    FILE PATH and Name of report csv    proc sql  SELECT   FROM  YOUR FINAL TABLE NAME   quit  ods csv close   endrsubmit

User · Answer

Dead horse perhaps  but a while back I was trying to do the same and came across a script to create a STP that tried to do what I was looking for  but it had a few quirks that needed some attention  In an attempt to track down where I found the script to post an update  I came across this thread and it seemed like a good spot to share it   This STP  Which for the most part I take no credit for  and I can t find the site I found it on   takes a schema name  table name  and Y or N  to include or exclude headers  as input parameters and queries the supplied table  outputting each row in comma-separated  quoted  csv format    I ve made numerous fixes changes to the original script  but the bones of it are from the OP  whoever that was   Here is the script   IF OBJECT ID  get csvFormat    P   IS NOT NULL     DROP PROCEDURE get csvFormat GO  CREATE PROCEDURE get csvFormat  schemaname VARCHAR 20    tablename VARCHAR 30   header char 1   AS BEGIN     IF ISNULL  tablename               BEGIN         PRINT  NO TABLE NAME SUPPLIED  UNABLE TO CONTINUE           RETURN     END     ELSE     BEGIN         DECLARE  cols VARCHAR MAX    sqlstrs VARCHAR MAX    heading VARCHAR MAX    schemaid int          --if no schemaname provided  default to dbo         IF ISNULL  schemaname                       SELECT  schemaname    dbo           --if no header provided  default to Y         IF ISNULL  header                       SELECT  header    Y           SELECT  schemaid    SELECT schema id FROM sys schemas WHERE  name     schemaname          SELECT           cols                 SELECT     CAST     b name      AS VARCHAR 50                   FROM sys objects a              INNER JOIN sys columns b ON a object id b object id              WHERE a name    tablename AND a schema id    schemaid             FOR XML PATH                         heading                 SELECT        b name       FROM sys objects a              INNER JOIN sys columns b ON a object id b object id              WHERE a name   tablename AND a schema id    schemaid             FOR XML PATH                        SET  tablename    schemaname          tablename         SET  heading     SELECT       right  heading len  heading -1        AS CSV  0 AS Sort     CHAR 13          SET  cols               replace right  cols len  cols -1                                 CHAR 13           IF  header    Y              SET  sqlstrs     SELECT CSV FROM      CHAR 13     heading     UNION SELECT CONCAT      cols      CSV  1 AS Sort FROM      tablename   CHAR 13       X ORDER BY Sort  CSV ASC          ELSE             SET  sqlstrs     SELECT CONCAT      cols      CSV FROM      tablename           IF  schemaid IS NOT NULL              EXEC  sqlstrs          ELSE              PRINT  SCHEMA DOES NOT EXIST       END END  GO  --------------------------------------  --EXEC get csvFormat  schemaname  dbo    tablename  TradeUnion    header  Y

User · Answer

Some ideas   From SQL Server Management Studio   1  Run a SELECT statement to filter your data  2  Click on the top-left corner to select all rows  3  Right-click to copy all the selected  4  Paste the copied content on Microsoft Excel  5  Save as CSV   Using SQLCMD  Command Prompt   Example   From the command prompt  you can run the query and export it to a file   sqlcmd -S   -d DatabaseName -E -s  -W -Q  SELECT   FROM TableName   gt  C  Test csv   Do not quote separator use just -s  and not quotes -s    unless you want to set quote as separator   More information here  ExcelSQLServer  Notes    This approach will have the  Rows affected  information in the bottom of the file  but you can get rid of this by using the  SET NOCOUNT ON  in the query itself  You may run a stored procedure instead of the actual query  e g   EXEC Database dbo StoredProcedure   You can use any programming language or even a batch file to automate this   Using BCP  Command Prompt   Example   bcp  SELECT   FROM Database dbo Table  queryout C  Test csv -c -t    -T -S   SQLEXPRESS   It is important to quote the comma separator as -t    vs just -t   More information here  bcp Utility  Notes    As per when using SQLCMD  you can run stored procedures instead of the actual queries You can use any programming language or a batch file to automate this   Hope this helps

User · Answer

And when you want all tables for some reason    You can generate these commands in SSMS   SELECT  CONCAT  sqlcmd -S     Your local  SERVERhere     -d    YourDB     -E -s  -W -Q  SELECT   FROM    TABLE NAME      gt    TABLE NAME    csv   FROM INFORMATION SCHEMA TABLES   And get again rows like this  sqlcmd -S     -d    -E -s  -W -Q  SELECT   FROM table1   gt table1 csv sqlcmd -S     -d    -E -s  -W -Q  SELECT   FROM table2   gt table2 csv       There is also option to use better TAB as delimiter  but it would need a strange Unicode character - using Alt 9 in CMD  it came like this    Unicode CB25   but works only by copy paste to command line not in batch

User · Answer

Here is an option I found to export to Excel  can be modified for CSV I believe   insert into OPENROWSET  Microsoft Jet OLEDB 4 0     Excel 8 0 Database D  testing xls      SELECT   FROM  SheetName     select   from SQLServerTable

User · Answer

I wrote a small tool that does just that  Code is available on github   To dump the results of one  or more  SQL queries to one  or more  CSV files   java -jar sql dumper jar  path sql files   path out  user pass jdbcString   Cheers

User · Answer

In SQL Server Management Studio query window   Select All result set values Right Click and Select  Save Results As        Save as CSV file

User · Answer

You can also use following Node js module to do it with ease   https   www npmjs com package mssql-to-csv  var mssqlExport   require  mssql-to-csv           All config options supported by https   www npmjs com package mssql      var dbconfig             user   username           password   pass           server   servername           database   dbname           requestTimeout  320000          pool                max  20              min  12              idleTimeoutMillis  30000                       var options             ignoreList    sysdiagrams       tables to ignore          tables                          empty to export all the tables          outputDirectory   somedir           log  true             mssqlExport dbconfig  options  then function            console log  All done successfully             process exit 0          catch function err           console log err toString             process exit -1

User · Answer

From SQL Server Management Studio  Right click the table you want to export and select  Select All Rows   Right click the results window and select  Save Results As

[sql-server] Export table from database to csv file

Examples related to sql-server

Examples related to database

Examples related to csv