SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Export SQL data to CSV file with headers using BCP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwilliams
Posting Yak Master

194 Posts

Posted - 11/26/2012 :  09:26:53  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/26/2012 :  09:46:37  Show Profile  Reply with Quote
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
Go to Top of Page

kwilliams
Posting Yak Master

194 Posts

Posted - 11/26/2012 :  10:02:49  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 11/29/2012 :  02:36:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer method 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000