How to get export output in "real" CSV format in SQL Server Management Studio?_问答_开发者

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 forma开发者_如何学JAVAt. 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.

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:

How come it's not turned on by default
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:

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

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.

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

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

you should be good to go!

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.

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.

As of 2016, this is the default behaviour when the following option is selected in Query Options:

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

Columns are delimited by commas, and fields containing commas are double-quote encapsulated.

After reading the answers (iacob's in particular) and trying out the suggested options in a newer version of SSMS, I decided to do a deep dive and compile a comprehensive overview of the options and how they affect the export format.

Export query result to file options

(This is with SSMS v18.4)

No options selected:
=> Data is exported as "CSV" but with semicolons instead of commas as delimiters.
=> Values containing semicolons and/or double quotes are properly enclosed in double quotes.
=> Double quotes are properly escaped with 2x double quotes.
"Include column headers when copying or saving the results"
=> Does exactly what it says, just adds the column headers as the first line.
=> Contradicting iacob's anwer, this option does NOT change the delimiter to a comma and is NOT required for enclosing value with semicolons in double quotes and properly escaping double quotes.
"Retain CR/LF on copy or save"
=> Values containing newlines are properly enclosed in double quotes and newlines are retained.

Having both options selected seems to produce the most usable CSV format, even though it's using a semicolon instead of comma as delimiter.

Another issue that remains is that NULL values are exported as "NULL" instead of an empty field.

Conclusion:

No matter which method is chosen (export data task / export result to CSV) and which options are selected, there still seems to be no single way to make SSMS properly export to CSV with proper support for commas, semicolons, double quotes as well as null values.

Usually I use this kind of function:

CREATE FUNCTION [dbo].[toExport]
(
    @txt varchar(max)

)
RETURNS varchar(max)
AS
BEGIN
    
    return REPLACE(REPLACE(REPLACE(@txt, ';', ','), CHAR(10), ' '), CHAR(13), ' ');

END

And in select I put it here:

SELECT dbo.toExport( column_name ) AS column_name FROM ....

And in SMSS 2012 simply Right click on a grid and save results as, or copy all grid (ctrl-A) and ctrl-V to Excel.

It's easiest way to manage data in for example MS Excel without problems with columns.

Of course you must click "Quote strings containing list separators when saving .csv results" in Tools -> Options -> Query Results -> Sql Server -> Results to Grid and increase Maximum Characters Retrieved if you need it.

I think people have provided correct solutions here in particular the response from Matthew Walton about changing the options but for me that was not available (possibly because my database is older version)

Alternatively if you are able to export it as a pipe or comma delimited and all you want is add quotations around your columns then just run this command in Powershell.

Import-Csv 'C:\input.txt' -Delimiter '|' |
    Export-Csv 'C:\output.csv' -Delimiter '|' -NoType

input.txt file

1|A|Test|ABC,PQR

After the command is executed

"1"|"A"|"Test"|"ABC,PQR"

I think the easiest is to open excel and import the data from SQL connection rather than using SSMS export.... I'm using SSMS 2016 and it doesn't have the option "Quote strings containing list separators when saving .csv results" presumably because it doesn't work

Ron

SSMS Tools >> Options >> Query Results >> SQL Server >> Results to Grid, Setting: "Quote strings containing list separators when saving .csv results".

enter image description here

Also, important if comma not default separator (Nordic countries et al.) Control Panel >> Clock, Language, and Region >> Region >> Additional Settings enter image description here

This also quotes cells that include linebreaks

As a sidenote, when importing thus created file to Excel there are some things to note. Csv-file must be opened with a double click (source: https://superuser.com/questions/180964/how-to-open-semicolon-delimited-csv-files-in-us-version-of-excel) Also, if Excel regional settings use ; as separator the following must be added in the first line of the CSV file:

sep=,

I know of no way to do this with SSMS alone. I know TOAD has a CSV option. Not sure if it is an escaped format. If SSIS is an option, you can convert to a format that escapes strings (true CSV), but that is not in SSMS.

If you have to write a C# program, I would consider querying the table and then running the query, as the metadata will clue which need the escape.

You could export to a tab-delimited format instead.

As all the settings mentioned above didn't fix the CSV my SSMS (SQL Server 2014) generated (nor exporting a tab-separated file), a colleague and I made a converter script (Ruby) to convert the SSMS CSV into readable CSV.

It keeps encoding, separators and linebreaks of the original file and even does an exact-byte-match validation at the end (it creates a file in the SSMS format from the parsed (!) input file and compares both files).

I was able to export one of my result sets with Matthew Walton's answer, however another of my results sets was not working. I'm working with xml data in my sql data, so I cannot use either comma delimited nor tab delimited outputs.

I solved this by using a basic python script that leverages pandas. Pandas has dataframe objects that will store all of your data cleanly, then you can export that dataframe into an excel sheet. I used this link to help me - https://appdividend.com/2020/04/27/python-pandas-how-to-convert-sql-to-dataframe/

import pandas as pd
import pyodbc
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
sql_query = pd.read_sql_query(
    '''SELECT TOP (1000) [column_1]
                  ,[column_2]
                  ,[column_3]
                   FROM [My_Table]
                  ''', conn)

df = pd.DataFrame(sql_query, columns=['column_1', 'column_2', 'column_3'])
df.to_excel("exported_data.xlsx", index=False)

I would like to suggest an alternative approach. I have this question bookmarked in SO. Voted in a lot of the answers and comments. But it is always a mess when I need to do the banal task of exporting a CSV file from a query in SQL Management Studio.

The easiest solution is to just to use another tool, like the free Dbeaver.

Download the Dbeaver multiplatform database tool (there is a portable version, you can use if without admin rights in your machine).
Run it and create a new SQL Server connection.
Open a new "Sql editor" tab
Click the arrow to execute your query
Right click the result grid and select "export data" and select CSV
Voilá! Now you have a decently formated CSV file.

No mystery. No need to restart. It just works.

Extra tip: you can even export your data without doing a select first. Right click on the table/database and you will find a export data option.

Lots of great explanations on here. But for me, simply using the SQL Export Data Task wizard, with the destination as a Flat File, I just had to set the Text qualifier to ". That did the trick for me. Every other setting was the default setting.

This helped point me in the right direction:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/43f39e86-67c1-4ba1-a3ee-cd2e3688936f/how-to-deal-with-commas-in-data-when-exporting-to-a-csv-file?forum=sqlintegrationservices