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

Question

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.

Solution 1

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:

Solution 2

My normal work-around is to build it into the query:

SELECT '"' + REPLACE(CAST(column AS NVARCHAR(4000)), '"', '""') + '"' AS Header, ... FROM ...

You can build that into a user-defined function, to make it a little easier, but you have to build a separate function for each data type.

Solution 3

It's sad the option is available in a confusing state, yet not perfectly operational. The following is working at least.

1. Choose "Tasks>Export Data" from the DB context menu (does not work at Table level either)
2. For Source, choose "Microsoft OLE DB Provider for SQL Server"
3. For destination choose "Flat File...", and specify "Format" as delimited and text qualifier as double-quote
4. Select Table or query (I worked with query)
5. Finish the wizard

you should be good to go!

Solution 4

Different combinations of these settings can bring results in the output that are incorrect or partial data. This is because Microsoft didn't think it was important enough to fix these issues. I'm only explaining what happens with CSV files when sending the results to a file.

To get good results, do the following:

Open new query window (new tab/session) ... if you do not, configuration below is lost and set back to the defaults

Write the query to handle the quote inside the quote, and also wrap all string data types in quotes. Also be aware that different DBMS and programming language grammars accept a different syntax for an escaped double quote (if using this output as input to another system). Some use \". Some use "". XML uses &quot;. Probably a reason Microsoft chose to ignore this functionality, so they didn't have to deal with the arguments.

.. If Escape Sequence of new system is "".

SELECT '"' + REPLACE(CAST(column1 AS VARCHAR(MAX)), '"', '""') + '"' FROM table1

.. If Escape Sequence of new system is \".

SELECT '"' + REPLACE(CAST(column1 AS VARCHAR(MAX)), '"', '\"') + '"' FROM table1

Configuration:

Query Options > Results > "Include column headers when copying or saving the results" checked

Query Options > Results > "Quote strings containing list separators when saving .csv results" - BROKEN; DO NOT USE!

Query Options > Results > others unchecked

Query Options > Results > Text > comma delimited (setting on top right corner)

Query Options > Results > Text > "Include column headers in the result set" checked

Query Options > Results > Text > others unchecked

Query Options > Results > Text > "Maximum number of characters displayed in each column" - set to max length so strings don't get truncated.

Query > Results To File (this is a toggle between all 3 options)

Execute query (F5)

Prompt for file name of report

Open file to look at results

NOTE: If you need to do this on a regular basis, you're better off just developing a program that will do this for you in .NET or Java, or whatever language you are comfortable with. Otherwise you have a high probability of making a mistake. Then be extremely aware of the syntax of the system you're importing into, before you define your export out of SQL Server.

Solution 5

How do you feel about Export to CSV from SSMS via PowerShell? This post describes how to define an external tool in SSMS that sends the currently selected query to a PowerShell script which exports to a CSV.

View more solutions

Comments

[Peter Recore]

So far this looks like the best bang for my buck. I haven't even added it to the External tools menu in SSMS - I am just running it from the command line.

[Rob]

Might not be necessary but I find it easier to just '"' + REPLACE(CAST(column AS VARCHAR), '"', '""') + '"'. That way I'm not worrying about undercutting a field.

[Lloyd]

Exists in SSMS 2008 as well.

[wghornsby]

It's also in 2005. Thanks for the great tip

[Seth Battin]

In 2012, this setting does not apply to the export wizard which can also yield a CSV. Luckily the save-results-as method takes about 20 fewer clicks anyway.

[Kate]

@SethBattin Hi Seth, I'm using 2012 and when I use 'Save Results As' and select CSV, I get it without enclosing quotes. Can you say how you managed to get it to include quotes?

[Seth Battin]

@Simon that's the subject of this answer. The process described above does work for right-clicking. It still doesn't escape the contents of strings though, it merely wraps them in quotes. So newlines and such are still a problem.

[KyleMit]

Note: SSMS will qualify a field containing a delimiter or qualifier, but it won't qualify a field that contains line breaks. So in this regard SSMS produces technically invalid CSV files

[Sergey Romanov]

Excellent post! I knew there have to be something. But you are right, one definitely does тещ expect this to be an option.

[Jason Larke]

For anyone else that had the same issue I had: You need to open a new query editor window for the changes to take effect. Doing Save Results As.. on the same result set before/after changing the behaviour makes no difference to the exported CSV.

[simbolo]

I had far better results adding a 'SQL Connection' inside the Excel workbook itself. Paste in your query there and the results are pulled in perfectly. I spent hours trying to get a CSV from SQL Management Studio 2014, but the save results function seems completly broken even with this setting turned on.

[smoore4]

Do what Jason Larke mentions and, surprisingly, don't save it as an existing file and overwrite. Create a new one.

[Exit]

This is not working at all. I'm ready to explode. Why does MS make everything so insanely frustrating? I've got the check box selected and confirmed in both the Query options and the general options, yet, all strings are still without quotes.

[Joel Coehoorn]

Today, I use varchar(max). When I wrote this originally, I had just come from a shop that was still on only (ugh) Sql Server 2000 and only beginning to look at 2005.

[Admin]

@Exit (and others who can't get this to work). 1) You need to open a new query window after you set this option. 2) The "list separator" character is as defined in your system control panel regional settings. 3) Only values containing a list separator will be contained in quotes.

[Lumberjack]

I couldn't find this option in SSMS 2016.

[mattmc3]

You would think this would work, but no - columns containing double quotes in the data are not properly escaped. Faux-csv is all SQL Server deals in on the export wizard.

[Dr. Ogden Wernstrom]

In SSMS v17 I found that the specified option was missing, however it appears to have been merged with the other option 'Include column headers when copying or saving the results', because checking that had the desired effect for me. Opening a new query window is still required for the setting to take effect.

[r.sumesh]

I or my consumer did not have reasons to complaint where the data had comma and single quotes in the text fields. As I've already mentioned, I was working with the query option and if you are aware about dirty fields, you can always wrap them with quotename. Thankfully, did not run into double quotes though. And I was suggesting a native option, instead of depending on external solutions.

[Owen]

Microsoft: "Let's make CSVs the default export format for SSMS". Also Microsoft: "Let's ignore basic implementation details."

[Rei Miyasaka]

@NineTails I love how that's not even explained or documented anywhere.

[Dr. Ogden Wernstrom]

@ReiMiyasaka Exactly why I shared.

[Codism]

SSMS 2014 here. Be careful with this option: the quotation marks are not handled when export to CSV. For example, if a column value contains a leading and ending quotes, these marks are not escaped and a CSV reader will strip out these marks later.

[N. M.]

You might also need to preserve the column type e.g. NVARCHAR if the original type was NVARCHAR.

[Joel Coehoorn]

Good point. varchar => nvarchar is widening, won't break things, but nvarchar => varchar might lose data. The best thing to do is use whatever matches the original column, but since people tend to just copy/paste example code off of stack overflow, the code in my answer is probably better for using nvarchar (and this change is now made).

[Ladislav Gallay]

I need to test this, but this seems exactly what we need and it's already in Ruby. Great!

[Brett Donald]

This option is still the most practical, even though it doesn't automatically escape double quotes. It's easy enough to escape double quotes with replace().

[Naypa]

Since Microsoft can't do something as basic as export to a csv file, this is the best solution. My files with line breaks weren't correctly exported using the most voted solution.

[TaeKwonJoe]

The option Quote strings containing list separators when saving .csv results is not available in SSMS v18.2, v18.9.1, nor v18.10. @Joona would you mind updating your answer to include your SSMS version number where this setting is available?

[m1m1k]

Option is no longer present in SSMS v18. I wish it was!

[iacob]

I'm using version 18.9.2 and it behaves as I described; behaviour seems to be version / locale specific.

[owl7]

DBeaver still has slight issues if you want CR/LF in the text field replaced by spaces. Although the .csv file may render OK when opened in Excel, rows appear split on CR/LF when opened in notepad++. Obviously it's retaining these special characters. If you want them replaced by spaces, use SSMS v18 with Tools > Options > Query Results > SQL Server > Results to Grid > "Include column headers when copying or saving the results" ticked.

[Naypa]

@owl7 this is the correct behavior of CSV files. Lines with linebreaks are enclosed in quotes and proper CSV readers can correctly parse them. The original data isn't altered and the linebreaks will be displayed in any text editor.