how to use bcp to save query output in excel with header

I need to export some select/ stored procedures results to excel files. I tried
Exec master..xp_cmdshell 'bcp TransactCIB.dbo.test out c:\Temp.xls -c -q -S"(local)" -U"sa" -P""'
to get the results to a excel file. But I can not get the headers. Is there anyway to include the header in excel file? please show details, thanks.
I use sqlserver 2000.
thotwielderAsked:
Who is Participating?
 
bamboo7431Connect With a Mentor Commented:
For complex stuff, I'd do the following.
Instead of a simple .CSV file I will write a query that will create an html file. Just TABLE, THEAD, TFOOT, TBODY, TH, TR, TD tags, nothing else. Save it with the .xls extension. Excel is smart enough to recognize the format and use it. Here you could go crazy with the formatting. Something like this:
(replace square brackets with angle ones)
[table]
[thead]
[th colspan="2"]This is a split cell[/th]
[th]This is a regular[br]cell with a line break in the name[/th]
[/thead]
[tbody]
[tr]
[td]Row1-Field1[/td]
[td]Row1-Field2[/td]
[td]Row1-Field3[/td]
[/tr]
[tr]
[td]Row2-Field1[/td]
[td]Row2-Field2[/td]
[td]Row2-Field3[/td]
[/tr]
[/tbody]
[/table]
Here you can go crazy with the formatting. Of course, this is a hell of an SQL query to write to get all the tags right...
0
 
bamboo7431Commented:
bcp by itself does not export headers.
here's a workaround (taken from http://www.sql-server-performance.com/bcp.asp )

use pubs
if object_id('workaround')>0
drop view Workaround
go
create view Workaround as
select
au_id
, au_lname
, au_fname
, convert(char, contract) 'contract'
, 1 as SeqNo from authors
union
select
'au_id'
, 'au_lname'
, 'au_fname'
, 'contract'
, 0 as SeqNo
go
exec master..xp_cmdshell
'bcp "select au_id, au_lname, au_fname, contract from
pubs..Workaround order by SeqNo, au_id" queryout "C:\text.txt" -c -T -Sx'
0
 
nmcdermaidCommented:
If the fields are fixed, you can create a file containing just the headers, then concatenate the header and data file with a COPY command (also run from xp_cmdshell)

COPY YourHeaderFile.TXT + YourDataFile.CSV YourFinalFile.CSV
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
thotwielderAuthor Commented:
bamboo's approach works, but has limitations to use. The header to use is not a single line seperate words, but a a little more complex one with some split cells, etc. So bamboo's approach won't satisfy.

nmcdermaid's approach also can't satisfy, because csv file can not support complex header.

 I have tried to combine to excel files, but failed. Is there any way to do this? Thanks.
0
 
nmcdermaidCommented:
I don't understand your definition of complex header.

To combine CSV (text) files. You just use the copy command.

To combine Excel files, the simplest method is probably using DTS to do it.
0
 
nmcdermaidConnect With a Mentor Commented:
hmmm that has absoultely nothing to do with CSV headers or Excel. (just rereading your original question)

If you want to export a flat table its pretty simple to wrap records up in various HTML tags.

But if its a complex nested table you're better off exporting it as XML, then combining that XML with a stylesheet (CSS) to create a table.
0
 
thotwielderAuthor Commented:
Well, thanks you guys for the answers.  I have done some research these days, and found maybe some BI tools like Crystal report may be better to generate reports.
0
 
nmcdermaidCommented:
Be aware that SQL Server comes with its own reporting platform, Reporting Services. Its all included.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.