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

Four Stars

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

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!

Eleven Stars

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


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)

Regards
Abhishek KUMAR
Six Stars

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

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)

Four Stars

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

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?

Six Stars

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

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!

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch