[SOLVED] file not having end line and " at first column and last column

file not having end line and " at first column and last column

I am trying to export of table in csv file by using bcp command in microsoft sql server.

Below is the table sample data

Table name : XYZ
col1      col2      col3
abcd,inc. USD,inc   1234
pqrs,inc  USD,inc   6789
stuv,inc  USD,inc   0009

There is comma in column values as above.

I have written .fmt file like below:

test.fmt

13.0 
3  
1 SQLCHAR 0 4000 "\",\"" 1  col1 SQL_Latin1_General_CP1_CI_AS 
2 SQLCHAR 0 4000 "\",\"" 2  col2 SQL_Latin1_General_CP1_CI_AS 
3 SQLCHAR 0 4000 "\r\n" 3   col3 SQL_Latin1_General_CP1_CI_AS

Below is command I am using:

DECLARE 
@V_BCP_QUERY VARCHAR(4000),
@V_BCP_OUTPUT_FILE VARCHAR(1500),
@V_BCP_FORMAT_FILE VARCHAR(1500),
@V_BCP_COMMAND VARCHAR(4000)
begin

SET @V_BCP_QUERY='"SELECT col1,col2,col3 FROM TABS..XYZ"'

SET @V_BCP_OUTPUT_FILE='"D:\OUTPUT.csv"'

SET @V_BCP_FORMAT_FILE='"D:\test.fmt"'

SET @V_BCP_COMMAND='bcp '+@V_BCP_QUERY+' queryout '+@V_BCP_OUTPUT_FILE+' -f '+@V_BCP_FORMAT_FILE+' -T -S "DEV-CR"'

EXECUTE Master.dbo.xp_CmdShell  @V_BCP_COMMAND

end

I am getting below data in OUTPUT.csv file:

abcd,inc.","USD,inc","1234
pqrs,inc","USD,inc","6789
stuv,inc","USD,inc","0009

there is no " at start of line and end of line. Also when I open this in excel then all rows are coming in a single line

my requirement is to export file in csv file.

Kindly help

Solution

You could hack a solution together - but using the correct tool for the job would be much easier and better in the long run.

Instead of using BCP to output individual columns, create a single column formatted with your desired result:

SELECT quotename(concat_ws(',', quotename(col1, char(34)), quotename(col2, char(34)), quotename(col3, char(34)), char(34)) 
  FROM yourTable

This will give you a single column in your output - with double-quotes around the whole string, double-quotes around each column, concatenated with the '.' separator.