SQL- BCP export from with headers and quotes

0
votes

I am trying to export data from a SQL view to a CSV. The format of the CSV is as follows:

"SampleNo","Analysis Code","Analyte Name","Old Result","New Result","ChangeDate", "AD03861","$AIR","1,2,4-trimethylbenzene","--------","0.0001","12/28/2015 4:52:50 PM",

I am able to get the quotes around the fields but without the headers. And I am able to get the headers but without the quotes. I tried combining the two and got the following error.

Starting copy... SQLState = S0022, NativeError = 207 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column nam e 'SAMPNO'. SQLState = S0022, NativeError = 207 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column nam e 'ACODE'. SQLState = S0022, NativeError = 207 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column nam e 'ANLNAME'. SQLState = S0022, NativeError = 207 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column nam e 'OLDRESULT'. SQLState = S0022, NativeError = 207 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column nam e 'NEWRESULT'. SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

BCP copy out failed

bcp "select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANLNAME+CHAR(34),CHAR(34)+OLDRESULT+CHAR(34),CHAR(34)+NEWRESULT+CHAR(34) union all select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANLNAME+CHAR(34),CHAR(34)+OLDRESULT+CHAR(34),CHAR(34)+NEWRESULT+CHAR(34) FROM TEST.dbo.MODIFY_EXPORT" QUERYOUT c:\temp\test.csv -S SERV2012R2 -U sa -P password -c -t","
2
sql-servercsvbcp
This works for the headers: bcp "select 'SAMPNO', 'ACODE', 'ANLNAME', 'OLDRESULT', 'NEWRESULT' union all select SAMPNO, ACODE, ANLNAME, OLDRESULT, NEWRESULT FROM TEST.dbo.MODIFY_EXPORT" QUERYOUT c:\temp\test.csv -S SERV2012R2 -U sa -P password -c -t"," - heejin ghim
This works for the quotes: bcp "select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANLNAME+CHAR(34),CHAR(34)+OLDRESULT+CHAR(34),CHAR(34)+NEWRESULT+CHAR(34) FROM TEST.dbo.MODIFY_EXPORT" QUERYOUT c:\temp\test.csv -S SERV2012R2 -U sa -P password -c -t"," - heejin ghim

2 Answers

0
votes

Try wrapping your headers in single quotes:

bcp "select CHAR(34)+''SampleNo''+CHAR(34),CHAR(34)+''Analysis Code''+CHAR(34),CHAR(34)+''Analyte Name''+CHAR(34),CHAR(34)+''Old Result''+CHAR(34),CHAR(34)+''New Result''+CHAR(34),CHAR(34)+''ChangeDate''+CHAR(34) union all select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANLNAME+CHAR(34),CHAR(34)+OLDRESULT+CHAR(34),CHAR(34)+NEWRESULT+CHAR(34),CHAR(34)+CHANGEDATE+CHAR(34) FROM TEST.dbo.MODIFY_EXPORT" QUERYOUT c:\temp\test.csv -S SERV2012R2 -U sa -P password -c -t","

Note: I changed your example code to use the header info in your sample output.

0
votes

I think the proper way to handle quoted data elements with BCP is to use a format file. I've given this answer in the past:

SQL Server BCP Bulk insert Pipe delimited with text qualifier format file

The reason I think this is better than putting the quotes in your actual query is that the quotes are not the data you wish to transmit. The quotes are just formatting. So, it will make more sense to others later who will read your code to leave formatting info in the BCP command itself and not nested into the query.

Getting the header included in a data file is a separate issue and you seem to be handling that correctly with a "union" query to blend the header row and data rows together in one result with "queryout".

Be aware, you will likely still get data conversion errors when you add a header into a file in this manner. So, you have to convert all your outgoing data into "char/varchar" datatypes. Since you are using a "union" to blend your header record (all varchar data by default since you do not cast them explicitly) with your detail data (possibly some int, numeric, data data types mixed in as they are stored in your TEST.dbo.MODIFY_EXPORT table) both the header and detail queries must be blended into one result and datatypes must match for each column between your two queries.

A note, you could also query/bcp out the header and detail records as two separate files (2 separate bcp commands) and just merge them together with a OS/script command.