Export table from database to csv file

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.

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

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;

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

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

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.

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'

In SQL Server Management Studio query window

1. Select All result set values
2. Right Click and Select "Save Results As"

3. Save as CSV file

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,
'" >',
TABLE_NAME,
'.csv') FROM INFORMATION_SCHEMA.TABLES

And get again rows like this

sqlcmd -S ... -d... -E -s, -W -Q "SELECT * FROM table1" >table1.csv
sqlcmd -S ... -d... -E -s, -W -Q "SELECT * FROM table2" >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.

You don't need to go through hoops with SSMS or programming languages.

Run a PowerShell prompt as administrator, then:

Install-Module -Name SqlServer

Once installed you can export any table to CSV like this:

Invoke-Sqlcmd -Query "SELECT * FROM [mydatabase].[dbo].[mytable]" -ServerInstance "MYSQLSERVER" | Export-Csv -Path "c:\temp\output.csv" -NoTypeInformation

You can put that in a .PS1 script and execute it however you like.