topic Re: tMap with excel inputs, how to continue if 1 excel fatal errors / fail in Talend Studio

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

Anonymous — Fri, 10 Feb 2017 07:40:39 GMT

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?

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

Anonymous — Mon, 13 Feb 2017 02:59:10 GMT

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

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

Anonymous — Mon, 13 Feb 2017 03:46:24 GMT

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

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

vapukov — Mon, 13 Feb 2017 08:23:52 GMT

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

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

Anonymous — Tue, 14 Feb 2017 02:04:59 GMT

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

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

vapukov — Tue, 14 Feb 2017 02:43:24 GMT

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

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

Anonymous — Tue, 14 Feb 2017 07:08:44 GMT

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.

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

vapukov — Tue, 14 Feb 2017 08:09:14 GMT

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

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

Anonymous — Wed, 15 Feb 2017 06:49:28 GMT

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

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

vapukov — Wed, 15 Feb 2017 22:09:23 GMT

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