How to read excel file with dynamic sheet names?

[Anonymous]

Hi !

I'm new to Talend and I'm tasked to create a job to read in excel files and output as CSV. I know I can read in excel file in Talend but how do I programatically read in files with different sheet names? For example, sometimes the sheet name might be "october_PP", "Oct_PP", "10_PP",etc.. It is dynamic but follows a pattern of "*_PP".

Appreciate all help!!

[Anonymous]

@TRF - Thanks very much, this worked. 

[Anonymous]

@TRF while this worked, now there is another issue. There are multiple sheets with the same pattern in same excel and talend is able to read only one sheet.  

example - Sheet 1 - OCT 2016 (all biz) , sheet 2 - NOV 2016 (all biz). Excel source schema defined using OCT 2016 (all biz). Data read only from Oct 2016 (all biz) . any suggestions, please? 

[TRF]

Hi,

Works exactly as expected for me, every sheets with a name corresponding to the regex are read.

Are you sure the schema is allways the same?

What is your TOS version? (mine is 6.4.1).

Don't forget to mark your case as solved (as it is regarding to your original post).

[TRF]

@pratikpandya, maybe a space after the ).
In this case the name doesn't match with the regex and it may be difficult to detect when checking from Excel.

[Anonymous]

Thanks, the issue is resolved. The problem was with source data and there were spaces in numeric data expected. I handled that and it worked. thanks. I will mark the case as closed. 

[TRF]

@Victor, thank's to mark this topic as solved.

Kudos also accepted.

[Anonymous]

You can do that with the component suite tFileExcelSheet*

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%...

The component tFileExcelSheetList iterates through the list of sheets and provide metadata for every sheet. Now you could check the names of the sheets and get the appropriated sheet name and use this name in the component tFileExcelSheetInput as sheet name.

This component allows also to find column names per regex.

[TRF]

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

[Anonymous]

thanks, @TRF and @lli - i have created on more topic inline with this solution. Can you please help advise?  There are other threads with solutions but none of them elaborate better, 

[Ray0801]

@Fred Trebuchet​  I tried this for my file Which has a sheet format of July All.

".* All" This is the regrex I am using this regexbut it is extracting only the first sheet.