Exporting Data With Commas from SQL Server Table to CSV

I am attempting to export some data from a table in our SQL Server DB into an Excel spreadsheet or to CSV. One of the columns in the table has commas in it. When I export the data (save it to a CSV file) using SQL Server Management Studio and then open the resulting file in Excel, the column with commas ends up in multiple columns. For example, if I export a row with "If we could all export data well, we wouldn't have to ask questions like this" in one of it's columns, the resulting CSV separates that string into two columns. The first contains "If we could all export data well". The second contains "we wouldn't have to ask questions like this".

How can I avoid this when exporting?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony GiangrecoCommented:
Use Double Quotes in the code like this:

use '""' + col1 + '""' AS col1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Change the column delimiter from a comma to tab (or some other special character that does not appear in the data, such as | or ~).
commas in a field can be escaped with quotes but it depends where you plan on using the file. Some systems that process the file may not be smart enough to recognize escape characters. I ran into this when a product literally parse my csv by taking each line and splitting wherever a comma existed, regardless of quotes.
Preston CooperDatabase AdministratorCommented:
If the table has more than 10,000 rows then you should write a SQL Server Integration Service Package.  This will also make automating the extract later easier.  If you are only only doing this once and there are fewer than 10,000 rows then you can copy and paste the results to Excel from SQL Server Management Studio.
Vitor MontalvãoMSSQL Senior EngineerCommented:
cbridgman, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.