How to iterate through multiple sheets in Excel file, retrieve sheet name and data

One Star

How to iterate through multiple sheets in Excel file, retrieve sheet name and data

Hi,

  I am new to Talend and trying to read through multiple dynamic worksheets in the excel workbook and load data to table. 

tFileInputExcel -> tMap -> tOracleOutput works to load the data from all sheets to table.

 

But I need to get each worksheet name and check the name to derive a boolean value in addition to load the data in worksheet to table. The table columns looks like below

 

 SheetColumn1;SheetColumn2;...;Src_Sheet_Name;Etl_Flag

 

Any help or comments are helpful.

 

Tags (1)
Fifteen Stars TRF
Fifteen Stars

Re: How to iterate through multiple sheets in Excel file, retrieve sheet name and data

Hi,

In the tMap component you may access the current sheetname using the following expression (replace the tFileInputExcel # by yours):

((String)globalMap.get("tFileInputExcel_4_CURRENT_SHEET")) 

So, you can compare its value with what you want to populate the boolean field as desired.

 


TRF
Seventeen Stars

Re: How to iterate through multiple sheets in Excel file, retrieve sheet name and data

Fifteen Stars TRF
Fifteen Stars

Re: How to iterate through multiple sheets in Excel file, retrieve sheet name and data

@jlolling, good to know but in this case, tFileInputExcel is enough.

TRF
Fifteen Stars TRF
Fifteen Stars

Re: How to iterate through multiple sheets in Excel file, retrieve sheet name and data

@learner2018, does this help?
If so, please mark your case as solved.
Kudos also accepted.

TRF

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

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

Downloads and Trials

Test drive Talend's enterprise products.

Downloads