0

I am trying to export a huge load of data into an Excel file. The only option that SQL Server provides is "Save Results As."

The file type is already defined as .CSV (comma delimited).

I have a column with commas in it. So, I need to disable the "comma delimited" feature. When I save it, the column with commas gets separated into two columns and even tampers with other columns.

How do I do it?

Copying the whole data and pasting doesn't work, because it throws an OutOfMemoryException.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nayana
  • 1,513
  • 3
  • 24
  • 39
  • 1
    Maybe this question and its answer is of help: http://stackoverflow.com/questions/6115054/how-to-get-export-output-in-real-csv-format-in-sql-server-managment-studio – Onkel Toob Aug 19 '16 at 07:43

2 Answers2

0

one important thing you should know is the meaning of csv. CSV is Comma Separated Values.

Try to convert it to another file format (e.g xls/txt) or change/remove comma in your data.

zaidysf
  • 492
  • 2
  • 14
0

You can use QUOTENAME function for the columns in your data that may contain comments as such:

select QUOTENAME(column_with_commas, '"') as column_with_commas ...

and then sqlcmd to run the statement from the command file to a file. Assuming that your export sql is stored in export.sql:

sqlcmd -S server -u user -p password -i export.sql -o myoutput.csv -W

QUOTENAME documentation is here - https://msdn.microsoft.com/en-us/library/ms176114.aspx

Additional sqlcmd options are here - https://msdn.microsoft.com/en-us/library/ms162773.aspx

kgu87
  • 2,050
  • 14
  • 12