Post reply

How to read a data from an excel file having multiple sheets and insert into multiple tables tables

  • July 21, 2009 at 4:15 am

    #155991

    i have an excel file with say 5 sheets..

    each sheets have table with data..

    all tables are inter related...

    ie ex:- i have data for a table Course in sheet1 and Coursedetails in sheet2..

    the data is sheet1 has its corresponding data in sheet2.

    I need to read that data and save in the tables...in sqlserver..

    ie data from table course should save in coursetable and data from sheet2 should save in Coursedetails table...

    How to read data from multiple sheets?

    do i need to use for loop Container?

    do we need sqlserver datasource since i have to insert into mutiple columns or can i have a OLEdb Data source....

    my tables are having indentity on ...so i need to insert identity from db..and no need to read that from excel...

    Thanks,

    Naveen

  • July 21, 2009 at 9:31 am

    #1027708

    Create a package. Create a connection to the Excel file. Create an OLE DB connection for where the data is going to go. The Excel connection will contain the directory and file name. If you have five sheets then place five Data Flow Tasks on the Control flow of the package. Link the five data tasks with control flow so that they execute one after another.

    Open up the first data flow task place an Excel Source Task in the data flow and an OLE DB Destination task (if appropriate). The Excel source will allow you to connect to a specific sheet the file. Alternatively you are offered the chance to use a SQL Command. The SQL Command can be very good as it is not really SQL it just treats the sheet as if it were a table and if you select this there is a build query button. Do this all for the first sheet. If the data is not of the right format you can use a data conversion to correct it.

    Repeat the above till all five sheets are done.

    You can do this in a loop, you can store the sheet and SQL required in variables but I wanted to make it as simple as possible.

    Hope this helps.

    Ells:cool:

  • August 6, 2009 at 4:13 am

    #1035251

    Sorry friend..for that late reply..

    just got deviated to some other stuffs..

    Thanks anyways...

    i tried out your recommended way and it worked..

    Thanks,

    Naveen

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

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