A bit of a lengthy title that one but I couldn’t think how to limit it without losing the purpose of the post.

So, this post is all about how to go about exporting CSV data from SQL Server with data encapsulated within double quotes when it is required.

With SQL Server it’s easy enough to encapsulate all your column data with double quotes using the Import and Export tool but what if you don’t want that and you only want to encapsulate data within double quotes when you want to do so.

If this applies to you then you want to read on:

It’s all in the Options

Head over to Tools -> Options and from the resultant popup click on Query Results -> SQL Server -> Results to Grid.  Once you’re there the option you want to make sure is ticked is Quote strings containing list separators when saving .csv results.

Once you’ve set this option you’ll be good to go, although a restart of SQL Server may be required.  Once you’re set, execute a query (the example below shows two rows, one without any commas, and the other with commas) and from the results window, right click, choose Save Results As, note that the file type is defaulted to CSV and save the file.

Now, open the file and you’ll see that the data has only been encapsulated with quotation marks when required.

It doesn’t work with markup

Okay so the unfortunate thing is that this doesn’t seem to work too well if you’ve got HTML or other markup stored in a varchar column.  I know, why would you, but you might.