Results 1 to 8 of 8

Thread: using bcp to save query output in excel

  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Question using bcp to save query output in excel

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    use OSQL instead.

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    pl show me an example

  4. #4
    Join Date
    Jan 2004
    Posts
    8
    Thanks I found it

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    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'

  6. #6
    Join Date
    Jan 2004
    Posts
    8

    thank you

    Thank you.

  7. #7
    Join Date
    Aug 2004
    Posts
    52

    Problem with Column Headers using BCP

    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 ?

  8. #8
    Join Date
    Feb 2003
    Posts
    1,048
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •