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

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

CodePudding user response：

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.

CodePudding user response：

Sure it's ugly, but it's simple, quick and gets it done.

SELECT '"'   col1   '",'  
        '"'   col2   '",'  
        '"'   col3   '"'
FROM Table