Search This Blog

Monday, January 30, 2017

Handeling comma while creating CSV

I would recommend you should NOT use comma ',' in your csv as I think you are facing issues because of ',' in the data of your text column. Instead you can use '|' sign.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT Column1, Column2, Column3 FROM YourDB.[dbo].[YourTable] ORDER BY 1" queryout E:\Objects3.txt -t"|" -c -T '
if you still want to use comma as separator due to any limitation, you can use char(34) before and after the column that you think may have comma in the text. Adding char(34) i.e. double quote, will make the text with comma as a single value.


EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT Column1, char(34) + Column2 + char(34) , Column3 FROM YourDB.[dbo].[YourTable] ORDER BY 1" queryout E:\Objects3.txt -t"," -c -T '


The above code assumes that you have comma in Column 2 and file created is comma separated CSV.

I hope it will help you.

No comments:

Post a Comment