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
Highlighted
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!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog