etltalend

How to do error handling with tFileInputExcel


We receive dozens of Excel files per day and want to handle them with Talend. The information in these files do not always meet the requirements. We receive PDF, Word, XML files. But we also receive Excel file which have different Sheetnames. Or doe not have the required layout.

If the layout is correct, the sheet has always the correct name. The sheet has a header part with 5 rows of information. (Fixed) And a information part, which can be 1 to 10 rows, with 15 columns. And if everything is filled in correctly, the job runs fine.

But if the job encounters for example a wrong sheet name, the job interupts with an exception.

Exception in component tFileInputExcel_2 (Jobname_sub_xlsx) java.lang.RuntimeException: Special sheets not exist!

If the sheetname is incorrect, most of the times, it's an Excel file which has nothing to do with it and I just want to move the file to a 'Failed' folder and continue.

How can I do this?


Solution

  • So for you answer usually when I have to handle files that pose a problem like this I decompose my process into two a job main that is used to iterate on the files to process and a child job that reads and process thee file in wich I use a tFileCopy and a onComponentError link.

    For example I have three excel file with one that has a different sheet name (test2.xslx).

    enter image description here

    My parent job is configured like this :

    enter image description here

    I pass the filepath to the child job via context :

    enter image description here

    Make sure that the option "stop in case of child job error" is unchecked !

    And my child job is configured this way :

    enter image description here

    If you have the option "stop in case of error" option checked in you tFileInputExcel component, uncheck it :

    enter image description here

    And then copy your file with a tFileCopy into your error / archive folder and check the option "delete source file" :

    enter image description here

    And then your file should be in the destination folder :

    enter image description here

    Hope this helps