4

I have a missing option quote strings containing list separators when saving .csv results in SSMS 2016. Seems like this option was eliminated in this version of SSMS.

I need to export query results in CSV file (i.e., Comma Separated Variable file) is an UTF-8/ASCII text file in which records have a variable length and end with a carriage return and line feed. Fields are explicitly delimited with a comma. It is recommended that all fields are enclosed into delimiting quotes. The delimiting quotes are mandatory for those fields that are expected to contain a list of codes separated by comma.

I am able to export the results into CSV file BUT it is missing the delimiting quotes. e.g. I have following output 

Aguilar,F,03191970,H,700,699,240,S

which is supposed to be 

Aguilar,F,03191970,H,"700,699,240",S

Not sure if there is a better way to accomplish the same. Please advise.

Kaur
  • 491
  • 2
  • 7
  • 19
  • Since the requirement of quotes is only for the fields that are expected to contain a list of codes separated by comma, I have concatenated in my select statement to get around. I am still looking for a better solution; if anyone has. `,CASE WHEN rc5 > ' ' THEN '"' + rc1 + ', ' + rc2 + ', ' + rc3 + ', ' + rc4 + ', ' + rc5 + '"' ELSE ' ' END ` – Kaur Jan 25 '17 at 22:58

2 Answers2

0

I ran into this same issue today after migrating to a new laptop and installing the latest version of SSMS. I ended up installing an older version of SSMS 2012 (11.0.6020.0) from the following URL:

Brian Adkins
  • 657
  • 2
  • 6
  • 13
-1

In the results grid, right-click, and choose the "Save Results As" option. A "Save Grid Results" window will pop-up. If you then save as csv, you will get a properly quoted csv file.

For some reason, this is different from what you get if you choose Query -> Results to -> Results To File option from the menubar, and then hit Execute. When you go through the menubar, embedded delimiters in your output file will not be correctly quoted, even when you save as csv.

jefnik
  • 1