Help me with the logic please

Seven Stars

Help me with the logic please

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.

screenshot-11.png

 

Appreciate any help. Thanks

 


Accepted Solutions
Highlighted
Nine Stars

Re: Help me with the logic please

 

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.

 

 

Regards
Abhishek KUMAR
Nine Stars

Re: Help me with the logic please

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.

 

Regards
Abhishek KUMAR

All Replies
Nine Stars

Re: Help me with the logic please

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.
Regards
Abhishek KUMAR
Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

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

Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

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);

Screen Shot 2019-01-29 at 1.45.56 PM.png

 

 

in the tLoop I have the condition as shown below Screen Shot 2019-01-29 at 1.47.38 PM.png

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.

 

screenshot-12.png

 

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

 

 

 

 

Highlighted
Nine Stars

Re: Help me with the logic please

 

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.

 

 

Regards
Abhishek KUMAR
Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

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

Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

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

Nine Stars

Re: Help me with the logic please

Import org.apache.poi.*

Same jar is used by talend components.
Regards
Abhishek KUMAR
Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

I tried using the java functions you provided for getting the number of sheets like thisScreen Shot 2019-02-01 at 1.39.15 PM.pngbut ended up with compilation error again. Screen Shot 2019-02-01 at 1.39.25 PM.png

Any idea what is going wrong here?

Nine Stars

Re: Help me with the logic please

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.

 

Regards
Abhishek KUMAR
Seven Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

My job has only tJava component as im trying to test it to get the no of sheets. But when I used tFileInputExcel component, it just worked fine .

And the java code for tFileInputExcel component has "org.apache.poi.xssf.usermodel.XSSFSheet" and works without a problem

Four Stars

Re: Help me with the logic please

Hello,,

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.

Regards

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads