Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Ells
Ells
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 931
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.
EllsCool
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search