Four Stars

How to read excel file with dynamic sheet names?

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!!

 

 

Tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

@pratikpandya, if your sheetname is formatted like "OCT 2016 (all data)" the regex must be:

".*\\(All Data\\)$"

wich means any string finishing by the substring "(All Data)".

 

If your string may contained any string between the (), change the regex like this one: 

".*\\(.*\\)$"

wich means any string finishing by any substring contained between ().

 

@Victor, if your excel file contains only 1 sheet, the sheetname doesn't matter, just tick the option "All sheets".

Else, the following regex should work (don't forget to tick the option "Use Regex"):

".*_PP$"

which means any string finishing by "_PP". 


TRF
18 REPLIES
Community Manager

Re: How to read excel file with dynamic sheet names?

Hi
tFileInputExcel component supports Use Regex to set the sheet name.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

Hi,

If the sheetname changes but not the sheet order, you may also replace the sheetname by its position (from 0 to n) or, if there is only 1 sheet per file, tick the option "All sheets".

 


TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

Hi,

Could you please further elaborate how to achieve this, I too have the similar requirement. The sheet name changes every time, and there is a fixed pattern such as "Nov 2015 ALL Data" Where "Nov 2015" will keep on changing but the last two words "ALL DATA" will remain as is. Is there a way i read based on sheet name matching as per pattern?

Tags (1)
Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

Hi,
Enter "*ALL Data" (including the quotes) in the "Sheet list" area then tick the "Use Regex" option.

TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

thanks, but it's not working. 

 

Sheet name is "OCT 2016 (all data)" 

 

Applied regex as "*all data" 

 

 

 

 

Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

It doesn't work because it doesn't match the example you've given.
Try this one:
"*(all data)"

TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

That's the whole point, I need only a pattern to match, enclosed "(" and ")" may not exist in subsequent data sets.  Text "all data" will always remain though. 

Five Stars

Re: How to read excel file with dynamic sheet names?

Same error , 

 

java.util.regex.PatternSyntaxException: Dangling meta character '*' near index 0
*(all data)
^

Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

@pratikpandya, if your sheetname is formatted like "OCT 2016 (all data)" the regex must be:

".*\\(All Data\\)$"

wich means any string finishing by the substring "(All Data)".

 

If your string may contained any string between the (), change the regex like this one: 

".*\\(.*\\)$"

wich means any string finishing by any substring contained between ().

 

@Victor, if your excel file contains only 1 sheet, the sheetname doesn't matter, just tick the option "All sheets".

Else, the following regex should work (don't forget to tick the option "Use Regex"):

".*_PP$"

which means any string finishing by "_PP". 


TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

@TRF - Thanks very much, this worked. 

Five Stars

Re: How to read excel file with dynamic sheet names?

@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? 

Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

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
Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

@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.

TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

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. 

Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

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

Kudos also accepted.


TRF
Seventeen Stars

Re: How to read excel file with dynamic sheet names?

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.

Forteen Stars TRF
Forteen Stars

Re: How to read excel file with dynamic sheet names?

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

TRF
Five Stars

Re: How to read excel file with dynamic sheet names?

thanks, @TRF and @jlolling - 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,