Post reply

Help in bcp Utility

  • November 22, 2006 at 12:05 am

    #153308

    Hi All,

    I am trying to export the data in a excel file using "bcp". right now i am exporting data from two different table in two separate files and it is running fine. But, i want the data of two tables in a single excel file but in separate sheets.

    Please suggest.

    Thanks in advance.

  • November 22, 2006 at 7:23 am

    #673451

    not sure about bcp, but you can easily do it like this:

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\testing.xls;',

    'SELECT * FROM [Sheet1$]') select * from SQLServerTable

     

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\testing.xls;',

    'SELECT * FROM [Sheet2$]') select * from SQLServerTable2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 22, 2006 at 12:48 pm

    #673570

    Or, if there is a relation between the tables, you could rephrase your query to do a JOIN between the tables and bcp out into one file. If there is no relation between the tables, but the columns and datatypes of the result set are same, you coud do an UNION ALL between the two tables and bcp out into a single file.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • November 23, 2006 at 7:50 am

    #673678

    Plus

    1. The file spreadsheet file must exist

    2. Each sheet referenced must exist

    3. Each sheet must have the correct number of columns in row 1 (Header Row)

    3. The data will be appended to the sheet (first insert starting at row 2)

    4. You cannot delete data 

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 3 (of 3 total)

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