import xls data to tables

Question

Post reply

import xls data to tables

Pavas Malviya

Ten Centuries

Points: 1109
More actions
June 13, 2005 at 12:26 am

#67103

Hi,
I want to import excel sheet data to my table.
My scenerio is
Data to one table comes from multiple excel sheet (xls).
For example Table Personal will fetch data from name_age.xls and Location.xls
Table Personal
id | Name | Age | Location
Now name and age comes from name_age.xls and location comes from Location.xls.
My idea is to import data to some logical table and then import it in relevent table.Is this the way to do this?If not What will be the best approch for doing this.
I want this to be done through Stored procedure, to be done programatically.
Can any one give me an sample code for doing this task.

Kindest Regards,
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 1

This sounds like a job for DTS.

You could import into two staging tables and then insert the combined data into the final result.

The DTS package can be executed from within a stored procedure using xp_cmdshell.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

My vote goes with Paul, this kind of task is what DTS thrives on.

Have fun

Steve

We need men who can dream of things that never were.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 3

Thanks aPaul and steve.

I will place the same question In DTS section ion order to get some more expert advice.

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 4

Thanks Paul and steve.

I will place the same question In DTS section ion order to get some more expert advice.

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 5

Also look up DTS in BOL to get an overview. It's graphical so it's reasonably easy to get to grips with.

Basically, without going into depth, you'll need one Excel connection for each spreadsheet, then a SQL Server connection for your database, and a transform data task running from each spreadsheet into the database connection (into separate staging tables).

Then, progressing from the SQL Server connection with an "On Success" or "On Completion" workflow, you can include an Execute SQL task to insert the data from the two tables into the final one.