BCP Command with headers

Question

BCP Command with headers

vikramchander90

Ten Centuries

Points: 1093
More actions
August 20, 2015 at 6:08 am

#299930

Hi,
I have a table which has few columns as Numeric value. I need to export the ouptut into a csv file as a report with column headers. I have used bcp command. Here column name and Column Header name is the same. The BCP query which i have used is Below
bcp "SelectCompanyCode,MonthId,ActualityCode,CompanyCounterpartCode,LocalProductCode,LocalCustomerBillTo,DestinationCountryCode, LocalCostCenterCode,LocalGLAccountCode,LocalProjectCode,TXCurrencyCode,TXAmountYTD,LocalCurrencyCode,LocalAmountYTD,SourceSystemFromTable(nolock)" queryout D:\test.csv -c -t ";" -r -S -T
Is there any way to export bcp with header or any other command to generate the csv file.
Can any 1 suggest. SQL Server 2008 R2 is used

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

vikramchander90 (8/20/2015)
Hi,
I have a table which has few columns as Numeric value. I need to export the ouptut into a csv file as a report with column headers. I have used bcp command. Here column name and Column Header name is the same. The BCP query which i have used is Below
bcp "SelectCompanyCode,MonthId,ActualityCode,CompanyCounterpartCode,LocalProductCode,LocalCustomerBillTo,DestinationCountryCode, LocalCostCenterCode,LocalGLAccountCode,LocalProjectCode,TXCurrencyCode,TXAmountYTD,LocalCurrencyCode,LocalAmountYTD,SourceSystemFromTable(nolock)" queryout D:\test.csv -c -t ";" -r -S -T
Is there any way to export bcp with header or any other command to generate the csv file.
Can any 1 suggest. SQL Server 2008 R2 is used

Heh... MS is correct. They didn't design BCP with CSV or much of anything else in mind. It was meant to support replication, IIRC.

The way to do this requires that you realize (and I'm sure that you have)that, although headers are character based and that numbers are digit based, they're all just characters. You need the SELECT you're using to have two parts UNION ALLed together. The first SELECT in the UNION should be the header names and the second SELECT should be nearly what you have but with a conversion to VARCHAR for each column.

Not fun... not elegant... but effective.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)