How to get export output in "real" CSV format in SQL Server Management Studio?

sql-server csv ssms
Peter Recore picture Peter Recore · May 24, 2011 · Viewed 84k times · Source

I have a query that I am running in SQL Server Management Studio (connecting to a SQL Server 2005 database). I want to export the data in CSV format. Not wannabe CSV format, where you just stick a comma between each column, but "real" CSV format, where you put quotes around your strings. This way you can export data that has commas or quotes in it.

All the examples I see limit themselves to the wannabe format. I can't figure out where the option to quote strings is.

If SSMS is truly incapable of this basic feat, are there other tools that will do it easily? I don't want to have to write a C# program every time I need a data dump.

Answer

Matthew Walton picture Matthew Walton · Oct 28, 2013

In SSMS 2012 there's an option for this, in Tools -> Options -> Query Results -> SQL Server -> Results to Grid, it's called "Quote strings containing list separators when saving .csv results". I don't know how long such an option has existed for, but I'm baffled by two things:

  1. How come it's not turned on by default
  2. How come it's an option and not an intrinsic part of the CSV exporting code

It just defies belief that the default behaviour is to have CSV export that's impossible to import properly. I've noticed Excel does the same, I'll have to go see if that's got an option too.

In the mean time, thanks to my colleague who pointed me to this bizarre bit of functionality when I was ranting about how the CSV exporter was completely useless, and this was the best link I'd found about it so I thought I'd put the knowledge here for the benefit of future searchers.

UPDATE

A screenshot below:enter image description here

Related questions

How to concatenate text from multiple rows into a single text string in SQL server?

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Read More
How to split a comma-separated value to columns

I have a table like this Value String ------------------- 1 Cleo, Smith I want to separate the comma delimited string into two columns Value Name Surname ------------------- 1 Cleo Smith I need only two fixed extra columns

Read More
Import CSV file into SQL Server

I am looking for help to import a .csv file into SQL Server using BULK INSERT and I have few basic questions. Issues: The CSV file data may have , (comma) in between (Ex: description), so how can I make import …

Read More