Jump to content

SQL Server 2008: How to export SQL data to CSV file with headers using BCP


kwilliams
 Share

Recommended Posts

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-----ColumnName3Joe Schmo CustomerJane Doe CustomerTim Tiny MusicianQUERY WITHIN SP:SELECT ColumnName1 AS Col1, ColumnName2 AS Col2, ColumnName3 AS Col3FROM TABLENAMEQUERY RESULT:Col1-----Col2-----Col3 <<<<----- WHAT I WANT!Joe Schmo CustomerJane Doe CustomerTiny Tim MusicianHere'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 CustomerJane Doe CustomerTim Tiny MusicianAs you can see the headers are missing. What am I doing wrong?

Link to comment
Share on other sites

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.
What do you mean "when done manually"? How exactly do you do it? Column names are not part of the rows that a query will return, so it sounds like it is only exporting the data that is returned. You may need to have the SP output the column names first, then output the data. You can use a UNION query to do that. The limitation is that you'll need to case all columns as character types if they aren't already, because the types need to match the text column names.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...