Help me with the logic please

[nivedhitha]

Hi,

I trying to load data from an excel into db and the excel can have multiple sheets with the same schema.

So the logic I'm trying to implement is, I assign a global variable or a context variable with 1(I don't need the first sheet.so starting with 1) and keep increasing it by 1 for each loop and when the error message is has "out of range" substring(meaning all sheets have been read) , I will end the loop.

But I couldn't figure how to increment the global/context variable by 1 for each loop and use it in the "sheet(name or position)" field in tfileinputexcel component.

Appreciate any help. Thanks

[akumar2301]

1) why you cannot use all sheet option ?

2) will tLoop before input excel work. In sheet position you could use ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))

Let me know if both doesnot worK.

[nivedhitha]

Hi @uganesh ,

Thanks for your reply. I can't use all sheets because I don't want the first sheet. I have to start from second and continue for all the rest.

I will try your other suggestion and get back

[nivedhitha]

Hi @uganesh ,

I tried your other logic and it worked. 

But the problem now is , I don't know how to break the loop.

screenshot of my job attached below

Im using a Boolean variable to control the loop. I'm initializing it to true in a tJava component like this

globalMap.put("check",true);

in the tLoop I have the condition as shown below 

iFileInputExcel config as shown below. I have 2 sheets in my excel so when the current_iteration is 3 this component throws an "out of range" exception. At that point I have to reset the "check" global variable to "false" so that the loop stops execution.

I added an if condition to tFileinputExcel component with 

((String)globalMap.get("tFileInputExcel_1_ERROR_MESSAGE")).contains("out of range") == true as the condition

and added a tJava to set it to false but this doesn't work. 

Can you please suggest me a way to do this?

Thanks in advance

[akumar2301 · Accepted solution]

1)

If you already know your 1st sheet Name , you could exclude that sheet by regex in sheet name section 

regex : "^(?!sheetnametobexcluded$).*$" 

2) If it can change , you could find the number of sheet in advance and  use it in tLoop.

below code in tJava to find number of sheet :

org.apache.poi.xssf.usermodel.XSSFWorkbook workbook1 =
new org.apache.poi.xssf.usermodel.XSSFWorkbook(xlsxfilename);

System.out.println(workbook1.getNumberOfSheets());

3) There are many excel components on exchange portal which could be useful dealing with sheets. 

Let me know if it works.

[nivedhitha]

Hi @uganesh ,

The regex worked so perfectly. Thank you so much !!

[nivedhitha]

Hi @uganesh ,

I'm trying to use XSSFWorkbook in tJava for a different requirement but getting compilation error 

"XSSFWorkbook cannot be resolved to a type" 

Do I need additional JARs for using this?

If yes please also let me know how to include these JARs in Talend.

Thanks in advance

[akumar2301]

Import org.apache.poi.*

Same jar is used by talend components.

[nivedhitha]

Hi @uganesh ,

I tried using the java functions you provided for getting the number of sheets like thisbut ended up with compilation error again. 

Any idea what is going wrong here?

[akumar2301]

Its weird , 

Same code works for me.

1)

Can you please search in Java code of your job  if you are able to find the string "org.apache.poi.xssf.usermodel.XSSFSheet"  ( apart from tjava)? 

2) do you have tFileInputExcel component in the same job and does it works fine ?

Usaully "org.apache.poi.xssf.usermodel.XSSFSheet" is used by tFileInputExcel  xlsx file read.