topic Re: Help me with the logic please in Talend Studio

Help me with the logic please

nivedhitha — Tue, 29 Jan 2019 15:44:54 GMT

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

Re: Help me with the logic please

akumar2301 — Tue, 29 Jan 2019 16:06:38 GMT

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.

Re: Help me with the logic please

nivedhitha — Tue, 29 Jan 2019 16:18:43 GMT

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

Re: Help me with the logic please

nivedhitha — Tue, 29 Jan 2019 18:56:45 GMT

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

Re: Help me with the logic please

akumar2301 — Tue, 29 Jan 2019 22:04:54 GMT

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.

Re: Help me with the logic please

nivedhitha — Thu, 31 Jan 2019 16:04:40 GMT

Hi @uganesh ,

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

Re: Help me with the logic please

nivedhitha — Fri, 01 Feb 2019 17:19:28 GMT

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

Re: Help me with the logic please

akumar2301 — Fri, 01 Feb 2019 18:11:41 GMT

Import org.apache.poi.*

Same jar is used by talend components.

Re: Help me with the logic please

nivedhitha — Fri, 01 Feb 2019 18:41:40 GMT

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?

Re: Help me with the logic please

akumar2301 — Fri, 01 Feb 2019 21:22:03 GMT

Its weird ,

Same code works for me.

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.

Re: Help me with the logic please

nivedhitha — Sat, 02 Feb 2019 03:10:14 GMT

Hi @uganesh ,

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

Re: Help me with the logic please

Anonymous — Sun, 03 Feb 2019 07:43:36 GMT

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

Re: Help me with the logic please

Anonymous — Thu, 10 Oct 2019 12:38:44 GMT

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

https://mobdro.biz/ Lucky Patcher/ Kodi

I Have same problem

Re: Help me with the logic please

nivedhitha — Thu, 10 Oct 2019 15:35:45 GMT

Hi @Moe ,

what exactly is your problem?

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