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

Question

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 1

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

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.

Answer 2

I've been trying to figure this out as well. Not sure if this will work for you since your table is much larger than mine, but this is what I did just out of a whim:

1. I pulled up my table in Express by doing a SELECT * statement
2. Simply selected the resulting rows and Ctrl + C
3. Opened Excel
4. Highlighted the amount of columns the table I was pasting had
5. Pasted, and it friggin' worked!
6. Now just have to Export Excel as CSV and done.

I know it probably sounds stupid, but it actually worked for me.

Answer 3

The easiest way to do this:

Use the Excel Data Import tools

• Go to Data > From Other Sources > From Sql Server
• Fill in the Server name etc.
• Select the table or view that you want to import.

Then Save the imported data to a CSV file. If you want to export a query then save your query as a view

Answer 4

Here's the essence of a script I use to do just this:

require 'rubygems'
require 'active_record'
require 'tiny_tds'
require 'activerecord-sqlserver-adapter'
require 'acts_as_reportable'
require 'ruport'

ActiveRecord::Base.logger = Logger.new("log/debug.log")
ActiveRecord::Base.establish_connection(
  :adapter    => 'sqlserver',
  :mode       => 'dblib',
  :dataserver => 'servername',
  :username   => 'username',
  :password   => 'password',
  :timeout    => '60000'
)

class Table1 < ActiveRecord::Base
  set_table_name 'table_name'
  set_primary_key 'table_id'
  acts_as_reportable
end

Table1.report_table(:all).save_as("finished/table1.csv")

Hope it helps!

Answer 5

The simplest solution Ive found is to add double quotes in your query;

SELECT '"'+MYCHARACTERDATA+'"' FROM MYTABLE

If your spreadsheet allows you to use a custom text qualifier then you can use a more exotic character like | to avoid double quotes in the text.

Daft..SSMS should export as proper CSV with quoted text fields and properly escaped quotes in those fields.

Answer 6

I'm curious why no one has suggested using SSIS (SQL Server Integration Services) for this process? All of the wizards and tools for Import/Export from within SSMS (SQL Server Management Studio) are absolutely not intended to be comprehensive (and they certainly are not, and yes, there is a lot that Microsoft should have to answer for with the limitations). But SSIS is a very full-featured ETL tool designed to tackle problems exactly like this one. Learning curve can be a little steep, but exporting a table to a comma/quote delimited csv file is not particularly hard.

Might need this add on for Visual Studio in order to have a dev environment to create a package: http://www.microsoft.com/en-us/download/details.aspx?id=42313 (link is for VS 2013, other links are available for other versions of VS).

Answer 7

Maybe the below link can help you :

Import/Export data with SQL Server 2005 Express

Answer 8

Robert Calhoun's solution did not work for me. We had a lot of text with commas and carriage returns / newlines etc. We used the Export functionality with a few changes to Chris Christodoulou's solution above.

In SQL Management Studio, right click the database and select Tasks -> Export Data.

Then choose SQL Server as the source and Flat File as the destination. Name the file MyFile.csv.

Set the Text qualifier as "

Select 'Write a query to specify the data transfer' and paste in your query. You can leave the next settings as the defaults.

With the data exported, open with Excel and save in the Excel format.

Answer 9

The best solution I could come up with is to select all the rows and do a copy as to XML.

The paste it into a notepad file and save it as a XML file. Then open that XML file from excel and voila! Atleast that how I got my file.

Answer 10

We made a small script to convert SSMS "broken" CSV into proper CSV, find it in this answer:

https://stackoverflow.com/a/46876236/1532201