June 13, 2005 at 7:36 am
Hi,
I want to import excel sheet data to my table.and these xls files are uploaded on server.
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 a sample code for doing this task.
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
June 13, 2005 at 8:58 am
As long as your data is not large or complex openrowset will work for you. You can also write the query so that both excel spreadsheets are queried and your table is populated in one statement. Never tried to link tables but is possible according to this article (page 3)
http://www.databasejournal.com/features/mssql/article.php/3331881
June 15, 2005 at 1:41 am
Thanks for the reply but my data base is very compalex it has around
18 tables with almost 20- 25 excel sheets.
How about DTS can we perform the jobs?
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
June 15, 2005 at 6:06 am
In that case I would suggest separating the task into separate jobs.
Firstly,
Save all the excel tabs as separate text files
Secondly,
Import each text file into a set of staging tables using dts
Thirdly,
Create a stored procedure that links the tables and inserts into your destination table. Call this from the same dts package as last step.
There are other ways that may even be a bit quicker, but this gives you the greatest amount of control and enables you trace a problem back to its source if need be. I am in datwarehousing and have had to do the task mentioned above many times. You cannot beat it as any amendments made to the excel sheet are easily catered for by changing the package. Also, you can do a check on the data in the staging table before importing into your live tables.
If you want to automate the job from beginning to end, I can send you the vb code to save all excel tabs as separate text filesin a folder.
Some people will tell you to directly import from excel using the dts transformation task and mapping columns one at a time etc.
Don't do it!! It will be a nightmare to edit any changes and take you ages to set up. Bulk insert from a text file is so much quicker and cleaner.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply