Exporting from bcp command to csv is not working

Question

I'm really struggling with this particular query, any help would be greatly appreciated,

create table test(int a, int b, int c)

INSERT INTO test
select 5, 6, 7
union all
select 1, 2, 3

DECLARE @sql varchar(2000),
@filename varchar(200)
SET @sql      = 'select * from test '
set @filename = 'D:\New folder\myfile.csv '

set @sql = 'bcp "' + @sql + '" queryout "' +  @filename +'" -c -r"''\n" -t"'',''" -S localhost\mytest -T'

EXEC Master..xp_CmdShell @SQL

I tried to export into csv file but my data doesnot start with ' values.

Currently I am getting

5','6','7'
1','2','3'

Instead I am expecting the csv file start with the ' everynew line in the starting position like

 '5','6','7'
 '1','2','3'

I used different quoted identifier but still it doesnot work.

Answer 1

Use QUOTENAME function to specify the single quote, and set column delimiter to just a comma. You'll have to build out your query though.

    DECLARE @sql varchar(2000),
    @filename varchar(200)
    SET @sql = 'select QUOTENAME(a, CHAR(39)), QUOTENAME(b, CHAR(39)), QUOTENAME(c, CHAR(39)) from test'
    set @filename = 'D:\New folder\myfile.csv '

    set @sql = 'bcp "' + @sql + '" queryout "' +  @filename +'" -c -r"''\n" -t"," -S localhost\mytest -T'