CODEWITHSUNDEEP
sqlsql-servercsvdelimiterbcp
LT268
LT268

Reputation: 135

How to have Comma separated values in a single cell when exporting sql results to CSV

I am trying to export my query results to my local path using bcp cmd.

SET @CMD =       'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @Combine =   'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyExcel.csv"'
SET @DelCMD =    'del "' + @Path + '\Temp_*.csv"'

However one of resultant field Remarks contains value like

Job completed fine,Next run will be @6PM.

But my exported result set showing this value as two seperate fields as it is seperated by comma. Due to this all values are misplaced. How to correct this? If my result set does not contain any field which is not comma seperated the result looks fine.

Upvotes: 2

Views: 977

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

With BCP there is - AFAIK - no way to escape the delimiter character.

Therefore you should always

  • use QOUTENAME in order to get qouted names, rather than concatenating qoutes as characters into your string
  • use double qoutes around each text.

Your export should look like

OtherValues,"Job completed fine,Next run will be @6PM",OtherValues

BCP will handle all inside the qoutes as one string...

Upvotes: 2

Anton
Anton

Reputation: 2882

There are a few options here.

First option: You can use another field terminator, like tab, |, $, etc... but you need to make sure it is unique and is not appeared in any text. You can you multiple characters to make a delimiter unique, but Excel can import only with single character delimiter.

Second option: you may use text qualifier (") in excel import and QUOTENAME([column], CHAR(34)) in your query for all text fields

Upvotes: 1

Related Questions