# Miscellaneous > General Database Discussions >  using bcp to save query output in excel

## ppvelu

I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.

the command I am using is this.

declare @x varchar(300)

set @x = 'bcp "select * from Northwind..orders" queryout c:\test.csv -S local -U sa -P passwd -c -C RAW -t "," -r \n'

exec master..xp_cmdshell @x

go

----------


## skhanal

use OSQL instead.

----------


## ppvelu

pl show me an example

----------


## ppvelu

Thanks I found it

----------


## Rawhide

I am forced to use BCP instead of OSQL in some of my exports because there are some large varchar fields that exceed the capabilities of OSQL. OSQL has a limit of 1000 characters per line and our PublicRemarks field is 3000 characters.

What I do instead is to have a separate text file with the header line in it. After the data is bcp'd out, I merge the two files together using the DOS command "Type".

If you don't know the headers ahead of time, you could always bcp out the headers first to a header file.

Declare @Headers varchar(1000),
	@x varchar(300)

Select @Headers = IsNull(@Headers + ',', '') + Column_Name
From Northwind.INFORMATION_SCHEMA.COLUMNS
Where Table_Name = 'orders'

set @x = 'bcp "select ''' + @Headers + '''" queryout c:\testheader.csv -S local -U sa -P passwd -c -C RAW -t "," -r \n'

exec master..xp_cmdshell @x

set @x = 'bcp "select * from Northwind..orders" queryout c:\test.csv -S local -U sa -P passwd -c -C RAW -t "," -r \n'

exec master..xp_cmdshell @x

exec master..xp_cmdshell 'type c:\testheader.csv > c:\testfinal.csv'
exec master..xp_cmdshell 'type c:\test.csv >> c:\testfinal.csv'

----------


## ppvelu

Thank you.

----------


## Avadhoot

I am creating CSV file using BCP utility. For the purpose I am using stored procedure to generate CSV File.
But when CSV file got generated, it was without column headings..Is there any way to include column headings if CSV is generated by stored procedure (not direct tables) using BCP ?

----------


## Rawhide

Well you could bcp the headers as simple strings to one file and the data to another and combine them as described above.

So your first bcp query would be:

Select 'FieldName1', 'FieldName2', 'FieldName3', etc.


Another option you may want to think about is seeing if you can create a view with the data you want in a table format. I do that a lot if it's something that gets exported regularly or frequently. The view does all of the data scrubbing for me and all I have to do on a daily basis is bcp out the data and headers.

----------

