topic Re: Insert data from multiple Excel sheets with same structure to different tables in a single database in Talend Studio

Insert data from multiple Excel sheets with same structure to different tables in a single database

Anonymous — Wed, 16 Jan 2019 17:11:32 GMT

I have a database with 3 tables that indicate different statuses with the attributes: id, created, status

I have a single excel file with multiples tabs populated with data I will use to insert into my tables. So I already created an Excel metadata to select 3 sheets that are in the same format with the columns: key, created, id, status. I use Tmap to extract the columns I want to use and re-order them.

My question is now, how can I read data from these 3 sheets (1 sheet at a time) and insert the data into the tables I specify, So something like

Sheet1>Table1

Sheet2>Table2

Sheet3>Table2

Sheet 1,2,3 have the exact same columns.

Table 1,2,3 have the exact same attributes.

I need to use tmap to reorder columns before I insert into database.

I'm new to Talend, so your help would be much appreciated!

Re: Insert data from multiple Excel sheets with same structure to different tables in a single database

akumar2301 — Wed, 16 Jan 2019 17:54:06 GMT

tfixedflowinput ( your sheet name and table name as data ) --> tflowtoitrate ( glob var will be created for sheet and table name automatically) --> tfileinputexcel ( in sheetname section add your globvar created for sheet name) --> tdboutput ( here tablename should be globvar created for table name)

Re: Insert data from multiple Excel sheets with same structure to different tables in a single database

dipanjan93 — Fri, 18 Jan 2019 03:44:04 GMT

You could also use tFileInputExcel (check All sheets) ------->tIteratetoFlow (current Excel sheet) ----->tDBOutput (Use table name as the row link created between tIteratetoFlow and tDBOutput)

Re: Insert data from multiple Excel sheets with same structure to different tables in a single database

Anonymous — Mon, 21 Jan 2019 10:12:36 GMT

thanks for your response. How can I set up tIteratetoFlow please? I just create something like

fileinput(using metadata)----->(iterate link)tIteratetoFlow------>tMap------>dbout

also as I have 3 sheets in file input, from tmap to dbout do I have 3 links? One for each sheet to db table insertion?

Re: Insert data from multiple Excel sheets with same structure to different tables in a single database

dipanjan93 — Tue, 22 Jan 2019 10:44:43 GMT

Inside tIteratetoFlow (after schema definition) use value for the column as the global variable of tFileInputExcel_Current_sheet

Don't forget to give kudos if your issue is resolved!