Help me with the logic please

Eight 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
Eleven 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
Eleven 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
Eleven 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
Eight 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

Eight 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

 

 

 

 

Eleven 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
Eight Stars

Re: Help me with the logic please

Hi @akumar2301 ,

 

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

Eight 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

Eleven Stars

Re: Help me with the logic please

Import org.apache.poi.*

Same jar is used by talend components.
Regards
Abhishek KUMAR
Eight 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?

Eleven 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
Eight 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
Highlighted
Two 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


I Have same problem

Eight Stars

Re: Help me with the logic please

Hi @mobdrobiz ,

 

what exactly is your problem?

I got a solution for my problem. If it is something similar to mine, may be I can help !!

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog