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

Answer 1

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" > 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.

Answer 2

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

Answer 3

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);
   });

Answer 4

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;