Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.
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
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?
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.