topic Re: how to catch error in tFileInputExcel in Talend Studio

how to catch error in tFileInputExcel

_AnonymousUser — Sat, 16 Nov 2024 13:42:03 GMT

Hello,
I want to implement a tFileList that iterates on multiple Excel files.
When one of the Excel files does not match with expected format (for exemple, the sheet name is not the expected one), I would like to log the error and to be able to continue the process on the other excel files.
For that, I try to use onComponentError but it does not work as I expected.
In fact, the tFileInputExcel stops with following error trace :
Exception in component tFileInputExcel_1 java.lang.RuntimeException: Special sheets not exist!
What is the best way to manage that ?

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 05 Nov 2009 09:41:46 GMT

Hello,
The only way would be to insert a tJava before the tFileInputExcel and coding this test using JExcel API : http://jexcelapi.sourceforge.net/

Re: how to catch error in tFileInputExcel

_AnonymousUser — Thu, 05 Nov 2009 09:56:08 GMT

Thank you for this suggestion. But this means that I have to implement all possible error cases : bad sheet name, bad column names... ?
I was expecting that the tFileInputExcel does this job and that it was possible to catch something in case of problem.
So what is really the usage of onComponentError ?

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 05 Nov 2009 10:11:32 GMT

For testing sheet name, it's not that hard:
U could do something like this :

import java.io.File; 
import java.io.File; 
import jxl.*; 
//use context variable to easily integrate a test in Talend
context.fileHasRequiredSheet = false;
String requiredSheetName="Special";
Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));
String[] sheetNames = workbook.getSheetNames();
for(int i=0;i<sheetNames.length;i++)
{
  if(sheetNames.equals(requiredSheetName))
  {
     context.fileHasRequiredSheet = true;
     break;
  }
}

I don't think u could have error in column names, Excel doesn't have any concept of schema, so Talend or any software can't implement such logic.

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 05 Nov 2009 11:08:17 GMT

hi all,
I 've also have to manage with a lot of excel files and sheet name never what it expected to be
In the same way as 'Morbo' as use jxl.* read some information and loop all excel sheets .

	public static String[] getExcelSheetList(String path){
		String[]sheetsName = null;
		try{
			//System.out.println(path.split("."));
		if(path.substring(path.indexOf(".")+1, path.length()).equalsIgnoreCase("xls")){
			/** 
			 * on recupere la liste des noms des feuilles
			 */
			int nbrXls = 0;
			
			//Sheet sheet = null;
			Workbook workbook = Workbook.getWorkbook(new File(path)); 
			for(int i = 0; i < workbook.getNumberOfSheets(); i++){
				nbrXls += workbook.getSheet(i).getRows();
			}
			
			// liste des noms de feuilles
			sheetsName = workbook.getSheetNames();
			for(int j = 0; j < sheetsName.length; j++)
				System.out.println(sheetsName<J>);
		}
		}
		catch(ArrayIndexOutOfBoundsException e){e.printStackTrace();} catch (JXLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return sheetsName;
	}</J>

there you can catch error and make something when it occured.(use Morbo's context variable tip and use a boolean 'flag' for example.
ps : in my example I use very helpfull exchange Component tFileChooser
hope it could help you
laurent

Re: how to catch error in tFileInputExcel

joreun — Thu, 05 Nov 2009 14:57:56 GMT

Thank for your propositions. As I would like to not introduce specific javacode, I will in fact now work with an XML export of the excel files that will offer better control in our case.
On the other hand, note that I had a look in the source of tFileInputExcel and in particular in tFileInputErrorExcel_begin.javajet file.
I contains the following line that raises the previous error trace that I have mentionned :
if(sheetList_<%= cid %>.size() <= 0){
throw new RuntimeException("Special sheets not exist!");
}
I'm convinced that it is possible to not throw an error here but only if the existing variable dieOnError is true.
This could be a good enhancement for tFileInputExcel.

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 05 Nov 2009 15:27:40 GMT

If u get rid of the exception throwing, U'll have to handle some other way (adding if conditions) the case when the sheet doesn't exist to stop further processing of the component.
Another way would be to englobe the processing of the component by a "try" block catching this execption and ouputing a warning.
Feel free to modify yourself the tFileInputExcel, U can post your enhancement to the bugtracker.
I've done it myself in the past, Talend integrated my changes after some times.

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 16 Jan 2014 08:47:40 GMT

when i try to read the excel file following error is coming :-
Exception in component tFileInputExcel_2
java.lang.RuntimeException: Special sheets not exist!

Re: how to catch error in tFileInputExcel

Anonymous — Thu, 16 Jan 2014 08:57:43 GMT

Hi pgandhi,
Would you mind opening a new topic for your issue? In addition, please give us more info about your current situation(what talend product build version are you using, how did you set your tFileInputExcel component).
Best regards
Sabrina