37

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.

Xac Stegner
  • 317
  • 1
  • 3
  • 6
  • 1
    can you just connect to the sql server from the ruby program and skip the export/import step? YOu can use the ruby-DBI package to connect select * and just quote it however you want. – Doon Sep 12 '11 at 03:55
  • @Doon that might be worth looking into. If I can connect and get results with Ruby I may be able to use FasterCSV to export valid CSV. I can't connect from an outside client as I'm working on a machine with strict permissions which does not allow remote connections to the SQL server. – Xac Stegner Sep 12 '11 at 13:31
  • You may be experiencing this issue if you are using SQL Server 2012 https://connect.microsoft.com/SQLServer/feedback/details/735714 – andrej351 Aug 19 '13 at 02:45
  • This solution gets into detail: http://stackoverflow.com/questions/6115054/how-to-get-export-output-in-real-csv-format-in-sql-server-managment-studio – JustBeingHelpful Sep 18 '15 at 22:04
  • Possible duplicate of [How to get export output in "real" CSV format in SQL Server Managment Studio?](http://stackoverflow.com/questions/6115054/how-to-get-export-output-in-real-csv-format-in-sql-server-managment-studio) – LCJ Apr 27 '17 at 18:29

10 Answers10

45

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.

Update 2022-Aug-08

I just installed SSMS version 18.12.1. Inexplicably, Microsoft has removed the "quote strings" option from both the Query -> Query Options -> Results -> Grid method described above and the Tools -> Options -> Query Results -> SQL Server -> Results to Grid method suggested by Tim Partridge. I have no solution for this version of SSMS.

Robert Calhoun
  • 4,823
  • 1
  • 38
  • 34
  • if your data has fields with quotes but no commas, http://stackoverflow.com/questions/14573623/whats-a-semantically-correct-way-to-parse-csv-from-sql-server-2008/14575076 has a Ruby method that will work (FasterCSV won't) – Seamus Abshere Apr 04 '13 at 17:04
  • 5
    Note that this solution is for a particular query. You can set the default for all queries in Tools > Options > Query Results > SQL Server > Results to Grid > Quote strings containing list separators when saving .csv results – Tim Partridge Oct 30 '13 at 17:27
  • 10
    This doesn't work for text fields containing newlines. – jrothenbuhler Apr 21 '14 at 23:33
  • 2
    This looks good at first, but it doesn't always escape double quotes (`"`). The case in this answer is fine, but I tried `SELECT '"'` AND `SELECT 'a"'`, and in both of these cases, the values were output verbatim using SQL Server Management Studio 2014. It's definitely a useful answer, but I've downvoted it because it only works in some cases and could therefore cause problems for people who rely on it. – Sam Nov 21 '14 at 01:53
  • @jrothenbuhler - The new line thing was a pain, so I wrote a program to do the exporting. https://github.com/deeja/SQLtoCSV/releases – Dan May 28 '15 at 13:48
  • Note that with SQL Server Management Studio 2012, setting this option, both for the specific query and for all queries via the Tools menu, has no effect (for me). The output doesn't contain any quoted entries, including field values containing commas and hence I'm unable to produce a valid CSV I can read elsewhere. – DavidJ Aug 25 '15 at 14:52
  • 1
    Note that with SSMS 17, this option has been _removed_. – Jimmy M. Mar 27 '19 at 16:10
26

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.

ckpepper02
  • 3,297
  • 5
  • 29
  • 43
  • 5
    This answer really should be upvoted. I'm more shocked than the OP that this actually works. I did it for a table of 170 K rows over remote desktop since the production server didn't have Excel or anything else installed. So I went inside the guest RDP system, ran the SQL, copied the rows, and then went back to the host system running the RDP session and pasted into OpenOffice. It took about ~10 minutes to stream the clipboard data over RDP and I had to drag OpenOffice around a bit to get it to redraw afterwards, but it did work and produced escaped data. Sad but true. – Kristopher Ives Mar 14 '12 at 23:26
  • 16
    I would like to add that I hope Microsoft engineers get kicked in the face on a daily basis. – Kristopher Ives Mar 14 '12 at 23:28
  • 3
    It's a shame that this was not marked as the answer because this absolutely works. In my case exporting from SSMS wasn't properly escaping strings with newlines. Copying/Pasting in Excel and then exporting was the answer because it handles CSV much better. – Frank Hale Feb 21 '13 at 21:35
  • 2
    this is the perfect solution, this way all problems of garbage characters is gone, don't know why by exporting with sql server causes malinterpretation of special chars. Thank you ckpepper02 – linuxatico Oct 03 '13 at 15:44
  • I'm sorry to say but this isn't always the perfect solution. Yes, I agree it works in some cases, but like the original question asked (OP??), it doesn't work where you have a large text field in SQL that has quotes, commas, tabs and new lines. I tried this initially too, but it just didn't work, so I wouldn't vote for it to be the accepted answer to the original question. – Ads Oct 27 '15 at 00:04
  • 1
    The simplest somethings the the hardest solution. Well done! – medina Dec 08 '15 at 06:06
  • It's not a good solution. What if you've got a huge result set and it simply throws a memory exception when you try to ctrl+c? It works for small results, but in no-way it should be an accepted answer. – lebryant Jun 16 '16 at 07:50
  • To echo Ads - this works for fields containing commas and quotes, but falls over on fields containing tabs. – aucuparia Sep 08 '16 at 11:10
  • Does not work if some of your fields contain newlines in the data – ladieu Oct 17 '16 at 20:36
  • I am having tab character in my data and I also thought this is a perfect solution but my column values having tab character split into two column. – ironman May 26 '17 at 14:20
11

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

Chris Christodoulou
  • 139
  • 1
  • 3
  • This IS a solution to the original problem. It worked for me locally, but I have a database that I can't connect to with Excel in this way due to security restrictions. :-( – Ads Oct 27 '15 at 00:05
  • 3
    I had a bunch of data with quotes, newlines, weird characters etc. This is the answer that worked. Thank you. (But it should be noted that I had to select my columns into a newTable so that excel could see the table instead of a result set) – Kevin Quiring Dec 02 '16 at 00:47
  • Yes, create a View and export it, rather than running some SQL and making an ad hoc report. That's best practice I think. – Dave Apr 06 '17 at 20:26
2

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.

Rolf Herbert
  • 249
  • 2
  • 13
2

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!

Unixmonkey
  • 18,485
  • 7
  • 55
  • 78
1

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

nick
  • 181
  • 1
  • 3
  • this is the best solution, but it just feels like so much effort when you want a quick and dirty one-off copy to Excel. It shouldn't be this hard! – aucuparia Sep 08 '16 at 11:14
0

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.

VictorySaber
  • 3,084
  • 1
  • 27
  • 45
  • D'oh..!!!! So close.. the preview worked flawlessly but during the actual export I got this error: Error 0xc020802e: Data Flow Task 1: The data type for "Destination - testttttt_csv.Inputs[Flat File Destination Input].Columns[Main_Content]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component. (SQL Server Import and Export Wizard) – Ads Oct 27 '15 at 00:13
0

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.

doesntknownanything
  • 35
  • 4
0

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

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

Niklas B.
  • 229
  • 1
  • 5
0

Maybe the below link can help you :

Import/Export data with SQL Server 2005 Express

masoud ramezani
  • 22,228
  • 29
  • 98
  • 151