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?
How can I avoid this when exporting?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
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.
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.
cbridgman, do you still need help with this question?