bcp queryout to xls

Question

bcp queryout to xls

petey20

SSCommitted

Points: 1741
More actions
June 21, 2005 at 1:28 pm

#86648

Is there a way to have the first row include the headers (column names) when using bcp and queryout option?
I wrote a VB Script that takes the output of my bcp and copies it to a template with headers and resave it, but that seems like a lot of extra processing if I can just do it up front first.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

I don't know how relavant this is, it still requires some extra work... But here goes nothing:

I think I saw an example (It might have been from Frank Kalis) about doing a view that first SELECTed the column names, UNIONed with the table data, ordering by a computed column (0 for header and 1 for the rest of the data) that was not in the output (to be sure to get the Column Headers as the first row) and then did a BCP from the view instead of from the table.

//Hanslindgren

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 2

I don't remember where I got this, but perhaps it's the example you were referring to. Works fine.

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 D:\text.xls -c -T -Sname'

Linda

Hans Lindgren SSChampion Points: 10496 More actions · Answer 3

Exactly!

That was the example I as referring to.

Thanx Linda Johanning!

//Hanslindgren

petey20 SSCommitted Points: 1741 More actions · Answer 4

Yup,

Using a union was the answer, but I had to convert all my values to varchar since the matching fields must be the same data types

Thanks

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

Yepp. I guess it takes a few CPU cycles more but I wouldn't worry abou those. Maybe annoying if you have many columns though. Then maybe a SELECT from SYSCOLUMNS and the use of Excel (to automatically copy the CAST( or CONVERT( VARCHAR for every column) could make it less repetitive...

//Hanslindgren