Exporting data from SQL Server Express to CSV (need quoting and escaping)

sql sql-server ruby csv export
Xac Stegner picture Xac Stegner · Sep 12, 2011 · Viewed 63k times · Source

I've spent 2 days trying to export a 75,000 row table containing a large text field of user input data from a SQL server installation. This data contains every plain ascii character, tabs, and newlines. I need to export CSV where every field is quoted, and quotes within the quoted columns are properly escaped ("").

Here is what I've tried so far: - Right clicking on the database from Management Studio and exporting to Excel: fails due to the field being too long. - Data Export from Management Studio to flat file with " text separator and comma separation - completely useless, does not escape quotes within a field making the file completely ambiguous. - BCP from command line - also does not support quoting fields.

I need to import with the FasterCSV ruby library. It does not allow the quote delimiter to be a non-standard ascii character or more than one character. It also does not allow \n or \r in unquoted columns.

Any help is greatly appreciated.

Answer

Robert Calhoun picture Robert Calhoun · Jun 16, 2012

It can be done! However you have to specifically configure SSMS to use quoted output, because for some daft reason it is not the default.

In the query window you want to save go to Query -> Query Options...

Check the box "quote strings containing list separators when saving .csv results".

enabling quoted csv output

then

select 'apple,banana,cookie' as col1,1324 as col2,'one two three' as col3,'a,b,"c",d' as col4

will output

col1,col2,col3,col4
"apple,banana,cookie",1324,one two three,"a,b,""c"",d"

which is what we all want.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More