Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to read a data from an excel file having multiple sheets and insert into multiple tables tables Expand / Collapse
Author
Message
Posted Tuesday, July 21, 2009 4:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 5:34 AM
Points: 9, Visits: 56
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
Post #756440
Posted Tuesday, July 21, 2009 9:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:23 AM
Points: 739, Visits: 848
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
Post #756695
Posted Thursday, August 6, 2009 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 5:34 AM
Points: 9, Visits: 56
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
Post #766059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse