excel with sheets problem

One Star

excel with sheets problem

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.
One Star

Re: excel with sheets problem

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.
One Star

Re: excel with sheets problem

"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?
One Star

Re: excel with sheets problem

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...
One Star

Re: excel with sheets problem

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
One Star

Re: excel with sheets problem

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).
One Star

Re: excel with sheets problem

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.
One Star

Re: excel with sheets problem

let's see later (tomorrow PM, ...)
One Star

Re: excel with sheets problem

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

tFileInputExcel_1 (read all sheets) -> tMap -Out1-> tFileOutputDelimited_1(Append)
-Out2-> tFileOutputDelimited_2(Append)
...

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.
One Star

Re: excel with sheets problem

I will try your solution and I'll let you...
Thanks really much
One Star

Re: excel with sheets problem

Hi
Try this
tFileInputExcel(with all sheets checked)---tMap(add an extracolumn(SheetName) having the sheet name
((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET"))---tFileOutput give the filename as
row1.SheetName.psv with append checked.
This will write into sperate files based on sheet name.
One Star

Re: excel with sheets problem

Hi lijolawrance ,
I don't understand how your solution can be applied to all the sheets of the Excel file.
Could you please detail ?
Thanks
Yves.
One Star

Re: excel with sheets problem

Hi im getting the error message while connecting from excel file to database.... field name is not null.. can anyone reply for me.Its is displaying 0 for the remaining column in the excel field.
One Star

Re: excel with sheets problem

Hi Yves
I am expecting that all you sheets have same schema.
If yes, If all sheets is checked in tFileInputExcel; talend read all the sheets from the excel in one go no iterate is required.
in the next tMap, add an extra column (SheetName) and value for the column is ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).
This value will change based on sheets name so basically if there are are 5 sheets, the column SheetName will have 5 unique value.
tFileInputExcel--------tMap-------tFileOutput
row1 row2
For writing into output file, you can specify the filename as <Location>\row2.SheetName+<extension> with append option clicked
here a seperate output will be created for each unique value of SheetName column.
Hope it help you
@ravishankar.m
Can you explain what is the error you are getting?
One Star

Re: excel with sheets problem

Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
One Star

Re: excel with sheets problem

Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column

Don't hijack posts. Try putting this in a new post.
One Star

Re: excel with sheets problem

Hi
I am new to talend. actually my requirement is
1) i need to extract some particular field from excelfile to another excelfile. Is it possible. please teach me.
2)what is the usage of data profiler and MDM whether i need to install this both are dont want.
please reply soon
One Star

Re: excel with sheets problem

ravishankar.m wrote:
Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
One Star

Re: excel with sheets problem

Hi Yves
I am expecting that all you sheets have same schema.
If yes, If all sheets is checked in tFileInputExcel; talend read all the sheets from the excel in one go no iterate is required.
in the next tMap, add an extra column (SheetName) and value for the column is ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).
This value will change based on sheets name so basically if there are are 5 sheets, the column SheetName will have 5 unique value.
tFileInputExcel--------tMap-------tFileOutput
row1 row2
For writing into output file, you can specify the filename as <Location>\row2.SheetName+<extension> with append option clicked
here a seperate output will be created for each unique value of SheetName column.
Hope it help you
@ravishankar.m
Can you explain what is the error you are getting?

Hi im ravishankar
Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
One Star

Re: excel with sheets problem

HI
how to do timestamp in talend . can anyone reply for me
One Star

Re: excel with sheets problem

Hi ravishankar
Can you please start a new thread and add a screeshot of you error
One Star

Re: excel with sheets problem

Hi This should help.
To get each Sheet in an Excell File...
Use a tJavaFlex with a file name as input and send its output to tfileinputExcel
For example tFileList---(fileName)-->tJavaFlex---(Iterate Through SheetName) --->tFileInputExcel
In the tJava Flex the Start Code should be :
final jxl.WorkbookSettings WBS_1 = new jxl.WorkbookSettings();
WBS_1.setEncoding("ISO-8859-15");
final
jxl.Workbook WB_1 = jxl.Workbook.getWorkbook(
new java.io.BufferedInputStream(new java.io.FileInputStream(
(((String)globalMap.get("rejects.FilePath"))))),
WBS_1);
java.lang.String[] sheetNames = WB_1.getSheetNames();
for (int ixx=0; ixx< sheetNames.length; ixx++) {
String theSheet = sheetNames;
// I don't want this one its crap
if ("DSAFO32ADVVERINF32".equals(theSheet)) continue;
globalMap.put("CURRENT_SHEET", theSheet);
The Main Code Should Be:
Nothing... Put a comment in here...
The End Code Should Be :
)
Thats it.
Then take an iterate flow out of this and it will iterate for each one of the sheets in your file.
The receiver of the iterate could be an Excel input. where you can use globalMap.get("CURRENT_SHEET");
as the sheet name.
This works.
have fun.
Aamer
One Star

Re: excel with sheets problem

hi lijolawrance
I cant able to upload screen sorts of my error in this forum. wat i have to do
One Star

Re: excel with sheets problem

hi is it possible to set the automatic time to run this job.. suppose dialy evening 5 o clock the job should run automatically and the excel valuse should store into database