| Author |
 Topic  |
|
|
kwilliams
Posting Yak Master
194 Posts |
 Posted - 11/26/2012 : 09:26:53
|
I created a simple Stored Procedure (SP) that queries a table and renames the column names, and I'm able to push those results out to a CSV file *with* those header names when done manually.
But when I try to export the results to a CSV file by running the same SP using xp_cmdshell with bcp, the header row (Col1, Col2, and Col3) does not appear in the resulting CSV file.
ORIGINAL TABLE:
ColumnName1-----ColumnName2-----ColumnName3
Joe Schmo Customer
Jane Doe Customer
Tim Tiny Musician
QUERY WITHIN SP:
SELECT ColumnName1 AS Col1, ColumnName2 AS Col2, ColumnName3 AS Col3
FROM TABLENAME
QUERY RESULT:
Col1-----Col2-----Col3 <<<<----- WHAT I WANT!
Joe Schmo Customer
Jane Doe Customer
Tiny Tim Musician
Here's the code I'm using within the job:
EXEC xp_cmdshell 'bcp "EXEC DATABASENAME.dbo.STOREDPROCEDURENAME" QUERYOUT "\\MYSERVERNAME\files\export.csv" -c -t, -T -S'
CSV FILE RESULT USING BCP:
Joe Schmo Customer
Jane Doe Customer
Tim Tiny Musician
As you can see the headers are missing. What am I doing wrong?
KWilliams
-------------------
It's the end of the world as we know it...and I feel fine |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/26/2012 : 09:46:37
|
Unfortunately BCP does not have a native option or feature that allows you to include headers in the export.
There are work arounds - for example, creating a view that unions the data rows with the header rows (that can be retrieved from INFORMATION_SCHEMA.COLUMNS). You will find examples if you google. But such work arounds are somewhat unsightly and ugly (in my perception of elegance and beauty of course - you can choose to disagree with me :).
An alternative is to use powershell to export. See here for an example: http://beyondrelational.com/modules/2/blogs/908/posts/18005/exporting-from-sql-server-to-csv-file-using-powershell.aspx |
 |
|
|
kwilliams
Posting Yak Master
194 Posts |
Posted - 11/26/2012 : 10:02:49
|
Very cool. I'll check into this, as it looks like a great alternative. I'm also about keeping the process clean, so I appreciate you directing me to this idea. Thanks.
KWilliams
-------------------
It's the end of the world as we know it...and I feel fine |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
| |
Topic |
|
Export SQL data to CSV file with headers using BCP
