Hi, I have an excel file with multiple sheets, I don't know each time how many sheets there will be in the file. How can I iterate the same talend elaboration on each sheet. I would like to have an output file for each sheet of the excel file. Thanks in advance for your precious help.
Hi, you can read each sheet (all sheets matching regex) or iterate (tLoop) using position ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) Then you should process according to CURRENT_SHEET.NAME ... to the specific output. Regards.
"I don't know each time how many sheets there will be in the file" so what to put in the tloop setting "To"? And then how to manage the output the n-sheets output?
some workaround could be to loop from 0 to MAX (context var) and handle component error (sheet not found beyond the real number of sheets).
You could also do a first step reading (ExcelFile) to set the context var or globalVar (within a tJava ...) to get your "upperBound" (tLoop).
To manage the output (n-sheets), using the name and matching criteria you could use it within a tMap to write specific output(s).
It's just an idea...
i'm really new to talend....
how many outputs have i to create in the tmap component if i don't know before the number of the sheets of the excel file?
has it to be a "dynamic" creation of ouputs, isn't it?
Could you please be more detailed?
Thanks
In that case, it's not dynamic.
You have to define with more details your needs to know if you can use ETL features or custom code...
If you only have to read Excel and write output (CSV, database, ...) according to a define schema it could be easy.
1 output <-> 1 schema.
You could also use only one output component and move from one file to the other (-changing name according to Excel sheetname).
Given the number of sheets fixed could you please explain me in more details how could i get the n-fixed (3 for example) out from the tmap component?
Thanks really much.
If you are not familiar to Talend you should read the technical documentation about the components you would use.
You need a matching criteria to manage your flow ouput.
You'd better use a join link to a reference lookup flow; to select the appropriate output.
But you could also match to the ExcelSheet name (regex: "*" to match Sheet1, Feuil2, ...).
If you use a tMap, you could activate filter to ouput to write from one to the other.
OutputFiles would be written in "append mode".
Filter condition to match "aa1" as one of the sheetname (equals, startsWith, ...)
Out1: ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).equals("aa1")
Out2: ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).equals("aa2")
...
You could also use local Var to add java code or call a specific routine (java code outside the tMap).
From that sample you would set the filter directly into the tFileInputExcel_1 but it's just to show you about your last question...
Regards.
