Export SQL data to CSV file with headers using BCP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Export SQL data to CSV file with headers using BCP

Author

Topic

kwilliams

194 Posts

Posted - 2012-11-26 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 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

194 Posts

Posted - 2012-11-26 : 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

22864 Posts

Posted - 2012-11-29 : 02:36:24

Also refer method 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources