importing from excel sheets

Question

importing from excel sheets

afgone

SSCommitted

Points: 1551
More actions
June 18, 2008 at 5:58 am

#122094

hi,
I have managed to successfully load muliple excel files to database.
now i want to load multiple sheets inside the excel file. can you guys show me a simple example of how to do this.
thank you

Viewing 12 posts - 1 through 11 (of 11 total)

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

Putts SSCommitted Points: 1857 More actions · Answer 1

Is this going to be a scheduled type job where you are always going to be loading the same Excel sheet or is this something like you want to be able to pick up any Excel and do imports on any sheets that might be in the document?

The latter would require some dynamic handling of looping through possible sheets in the doc.

The former would require you just using a sample Excel document to build your Data Flow and then making that Excel source dynamic via a Foreach loop.

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

Remember that you will need a connection manager per layout.

Therefore, if sheet 1 looks different to sheet 2, you will need 2 different connection managers to handle this.

The rest is pretty dynamic as Putts suggests

~PD

afgone SSCommitted Points: 1551 More actions · Answer 3

Thank you guys I managed to get the sheets. but now i have another problem.

one of the sheets has constant name say 'Sheet1' the other one will change depending on the filename. e.g. if filename is 'file18062008.xls' then the sheet would be 'sheet18062008 string'.....

now i managed to get the first sheet by creating a variable, then in excel source change the excel sheet to my variable.

how would i do it for second sheet????

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

Same like you did for the first sheet.

Variable var2SheetName = "16082008"

Change the variable with a scripting task

afgone SSCommitted Points: 1551 More actions · Answer 5

Cool thanks for all your help.

one more question(sorry to be a pain):

how would I insert columns from two tables into one table??

say i have one table called table1 which has three columns, 2nd table called table2 2 columns. now i want to get these 5 columns and insert into a third table.

thanks

Putts SSCommitted Points: 1857 More actions · Answer 6

INSERT INTO TABLE3 (col1, col2, col3, col4, col5)

SELECT T1.col1, T1.col2, T2.col1, T2.col2, T2.col3 FROM TABLE1, TABLE2

Put something like that into a SQL task and it should do what you're wanting

afgone SSCommitted Points: 1551 More actions · Answer 7

thanks isn't there any way to do it using the ssis components

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 8

pduplessis-723389

SSChampion

Points: 10599

June 19, 2008 at 12:11 am

#830899

Merge join or lookup

afgone SSCommitted Points: 1551 More actions · Answer 9

i have tried the insert statmenet .... but doesnt insert anything.

here is the code

insert into table3(ID, name,Phone, MPhone)

Select table1.ID, table2.name,table1.Phone, table1.MPhone

FROM table2 INNER JOIN

table3 ON table2.ID = table3.ID INNER JOIN

table1 ON table3.ID= table1.ID

Putts SSCommitted Points: 1857 More actions · Answer 10

Are the names of your tables actually table1, table2, table3 and table4?

If that is the case, and your select statement is set up correctly, then it should work.

To test the Insert into statement, in whatever query tool you use, execute just the Select statement of it and make sure that returns the rows you're expecting

afgone SSCommitted Points: 1551 More actions · Answer 11

afgone

SSCommitted

Points: 1551

June 20, 2008 at 6:02 am

#831675

thanks that sorted it