Jump to content
Sign in to follow this  
kwilliams

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

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?

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...