[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1877
  • Last Modified:

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.
0
thotwielder
Asked:
thotwielder
  • 4
  • 2
  • 2
2 Solutions
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
bamboo7431Commented:
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
 
nmcdermaidCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now