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

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

Anonymous — Sat, 16 Nov 2024 08:54:34 GMT

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.

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

TRF — Sat, 30 Dec 2017 09:20:20 GMT

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.

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

Anonymous — Mon, 01 Jan 2018 01:03:06 GMT

The component tFileExcelSheetList is designed to do that.

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%252F1%252Fproducts%252F542&pi=marketplace%252F1%252Fproducts%252F542%252Fitems%252F2739

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

TRF — Mon, 01 Jan 2018 08:06:28 GMT

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

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

TRF — Tue, 02 Jan 2018 22:29:15 GMT

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

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

TharunJoshi — Mon, 27 May 2019 18:15:55 GMT

Hello,

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

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

Anonymous — Mon, 27 May 2019 22:17:57 GMT

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.