tMap with excel inputs, how to continue if 1 excel fatal errors / fail

[Anonymous]

So I got a bunch of excel files, all in 1 folder.  The data is on several sheets in the excel file, so I get data like:

id	name	Address
1	Tom	Green street
2	Dick	Blue Street
3	Harry	Red Street

Sheet 1

id	status
1	valid
2	valid
3	invalid

Sheet 2
So I'm using tMap to join the 2 sheets, and eventually pass the values to a database.
My problem is, not all excel sheets have  sheet 2. So if there is no sheet 2, I'm supposed to give them the status of invalid.
Whats the best way to do this? Right now I keep getting a Fatal Exception, java.lang.RuntimeException: Special sheets not exist!
And then the loop stops. even if I put a "OnComponentError" flow, the overall job still stops when it encounters the first excel with no sheet 2
Any ideas on how to solve this?

[Anonymous]

Hi,
How did you join your 2 sheets by using tMap component? Would you mind posting your job setting screenshots into forum so that we can see if there is any wrong setting in your work flow?
Best regards
Sabrina

[Anonymous]

There is my flow. 
Very new to talend so, any critique /advice / best practice is welcome

[vapukov]

as variant (may be not the best), but You can write by tExcellOuput (Append to existing file, Append to Existing Sheet)
somewhere outside You column (like in column 150, first row) - it will create sheet if it not exists

[Anonymous]

I don't see how that helps my problem.
My problem is the excel file is missing sheet 2. 
So When the tFileInputExcel tries to read the file, it gets a error:
: testtalend.stationsubjobtest_0_1.StationSubjobtest - tFileInputExcel_1 Special sheets not exist!
Even though I put Die on Error as unchecked, it still dies. tFileList stops iterating due to the error in tFileInputExcel 

[vapukov]

I don't see how that helps my problem.
My problem is the excel file is missing sheet 2. 
So When the tFileInputExcel tries to read the file, it gets a error:
: testtalend.stationsubjobtest_0_1.StationSubjobtest - tFileInputExcel_1 Special sheets not exist!
Even though I put Die on Error as unchecked, it still dies. tFileList stops iterating due to the error in tFileInputExcel 

it simple - make ALL files with Sheet 2, some of them will be empty, some of them not
Output component - create sheet if it missed, and leave all other information unchanged
if You insert any data outside You real structure - You avoid next step runtime exception 

[Anonymous]

I don't see how that helps my problem.
My problem is the excel file is missing sheet 2. 
So When the tFileInputExcel tries to read the file, it gets a error:
: testtalend.stationsubjobtest_0_1.StationSubjobtest - tFileInputExcel_1 Special sheets not exist!
Even though I put Die on Error as unchecked, it still dies. tFileList stops iterating due to the error in tFileInputExcel 

it simple - make ALL files with Sheet 2, some of them will be empty, some of them not
Output component - create sheet if it missed, and leave all other information unchanged
if You insert any data outside You real structure - You avoid next step runtime exception 
How do I create sheet if missed?
In the long run we will be getting files off a FTP server so I need a way to verify the files to prevent errors. 
Furthermore the people providing the files have said some excels will have 1 sheet, some will have 2, so it is something I need to be able to catch. 

[vapukov]

what are You want hear from me? 🙂
Just do this, and test
between download and input insert 1 more element - for each iteration, it create sheet automatically if it missed, and will use if it already there, AND(!!!) not overwrite original information
Why - I can test it before suggest, but You do not want spend 1 min for create quick test? 🙂
THIS IS FULLY WORK - effective or not, it other question (but it still quick)
This is if You have less than 150 columns in You schema, just test it
tFTP -> then for each iteration -> dummy input (like tfixedflowinput) -> tExcelOutput

[Anonymous]

Why - I can test it before suggest, but You do not want spend 1 min for create quick test? 🙂

I did test it... however the process was extremely slow (30 seconds) and the files became very big (900kb to 20mb) and some files threw errors 
Warning:  Unrecognized token 60 for cells C323-C65536
Exception in component tFileOutputExcel_1
java.lang.NullPointerException
at jxl.biff.formula.TokenFormulaParser.getFormula(TokenFormulaParser.java:471)
at jxl.biff.formula.FormulaParser.getFormula(FormulaParser.java:172)
at jxl.biff.DVParser.<init>(DVParser.java:669)
at jxl.biff.BaseCellFeatures.getDVParser(BaseCellFeatures.java:501)
at jxl.write.biff.SheetWriter.writeDataValidation(SheetWriter.java:1137)
at jxl.write.biff.SheetWriter.write(SheetWriter.java:581)
at jxl.write.biff.WritableSheetImpl.write(WritableSheetImpl.java:1558)
at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:950)
at testtalend.testarea_0_1.TestArea.tFileList_1Process(TestArea.java:2043)
at testtalend.testarea_0_1.TestArea.runJobInTOS(TestArea.java:2426)
at testtalend.testarea_0_1.TestArea.main(TestArea.java:2260)
: testtalend.testarea_0_1.TestArea - tFileOutputExcel_1 null

So I don't think  I can use that as a solution. It's a nice idea but I think writing to excel files is a very intensive, and might cause more issues in the long run. 

Plus it does not solve the root problem, How do I continue the loop, if a component gets a fatal error?
How to do error handling

[vapukov]

Why - I can test it before suggest, but You do not want spend 1 min for create quick test? 🙂

I did test it... however the process was extremely slow (30 seconds) and the files became very big (900kb to 20mb) and some files threw errors 


Plus it does not solve the root problem, How do I continue the loop, if a component gets a fatal error?
How to do error handling

Not sure - what also wrong in Your case:
- I was make test with files with original size 3.5Mb (40000 rows)
- size not growing after operations
- slow, but much faster then described by You
- last portion of error - do not know, You must investigate it

in any case:
You can use Community components from Jan Lolling:
tFileExcelWorkBookOpen -> tFileExcelList -> iterate over list ->Split flow by RunIf "SheetName present or not" for 2 separate flow
One more alternative - write Java routine for check if sheet exists or not and add it to Job

About main - question
You can open the ticket on Jira, for add exception - not die "if Sheet not found"
because if component Die - job could be continued to next iteration, 
but in this case (runtime NullPointer exceptions) completely Job Die and You can not handle it on same level
You can create top level Job and run internationally SubJobs with parameters, if You uncheck Die on error - top level Job will continue to next iteration if SubJob Die