topic Re: Read .xlsm sheets but not all in Talend Studio

Read .xlsm sheets but not all

thiagoft — Tue, 14 Dec 2021 20:56:54 GMT

I need to read files in a folder, and these files are not all the same, some have 2 tabs(sheets) and some have more.

These tabs will not all be read, some are data and some are instructions or just a template.

The majority of the files will have one or two tabs that i don't want the data and the other tabs are named as dates so 10-23, 10-24.

I have the regex that is reading the correct tabs but it's only processing one tab from each file. ".*(\\d{2}-\\d{2})$"

i have it as

tFileList --> tJava(printing the file name) --> tFileInputExcel --> tFilterRow(removing empty records) --> tMap --> sHashOutput

I have iterate in the first 2 components and then main links after.

Am I doing something wrong? Do I need to iterate the tabs(sheets) as well?

Thanks in Advance

Re: Read .xlsm sheets but not all

corentin1 — Wed, 15 Dec 2021 08:27:54 GMT

Expression seems correct. Are you sure tFilterRow or tMap or not filtering too much data ? If you put a tLogRow just behind tFileInputExcel, do you have the same problem ?

Re: Read .xlsm sheets but not all

Anonymous — Wed, 15 Dec 2021 10:56:35 GMT

I have made a testing with v8.0.1 and it works as expected, I have 3 sheets in the file, the component read only two sheets, see below.

Regards

Shong

Re: Read .xlsm sheets but not all

thiagoft — Wed, 15 Dec 2021 21:48:18 GMT

I had the "Stop reading on encountering empty rows" checked on the Advanced Settings, once i unchecked that, it started reading all the tabs.

Re: Read .xlsm sheets but not all

thiagoft — Wed, 15 Dec 2021 21:52:31 GMT

Hi @Shicong Hong I'm using 7.3 but the issue is I had the "Stop reading on encountering empty rows" checked on the Advanced Settings, once i unchecked that, it started reading all the tabs.

I was under the impression that this option would apply to each tab not for the whole file, was that supposed to be like that?

Also, is there a way to avoid reading temp files from folders? My tFileList is listing the excel files that have the ~ in front.

Thank you

Re: Read .xlsm sheets but not all

Anonymous — Thu, 16 Dec 2021 06:27:10 GMT

I think this option would apply to the whole file.

In the advanced setting panel, there is 'Use exclude Filemask' option, you can use this option to filter the files.

Regards

Shong

Re: Read .xlsm sheets but not all

thiagoft — Thu, 16 Dec 2021 15:31:20 GMT

Thank you!