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)
Sixteen Stars TRF
Sixteen 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

Sixteen Stars TRF
Sixteen 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
Sixteen Stars TRF
Sixteen 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
Six Stars

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

Hello,

what if i don't have a component called tfileexcel sheet and also tried tmap component I couldn't do it

Seventeen Stars

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

Thats easy. Use the component tFileExcelSheetList to iterate through the sheets and use within the iteration tFileExcelSheetInput to fetch the data. You can use the return value from the tFileExcelSheetList using as sheet name for tFileExcelSheetInput. Both referencing to tFileExcelWorkbookOpen in which you open the file.

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

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog