Getting data from Excel file using run rule

IdentityIQ (IIQ) IIQ Discussion and Questions
, , ,
1

Hi All,

Is it possible to write a run rule in SailPoint IdentityIQ to extract data from an Excel file? If so, can you please anyone guide me on this

Thanks,
Ranjith M

2

Hi @Ranjith25,

see this topic:

3

Hi @Ranjith25,

As pointed out by other experts it is just about using the library and java beanshell code so more or less you can achieve everything that can be achieved via java.

Let us know if you still need any specific help.

Thanks

4

Hi @ashutosh08

I’m importing every package, but I’m still getting the error ‘class XSSFWorkbook not found in namespace.’ Can you please guide me on how to resolve this issue?

import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
File file = new File(“C:\demo\Demofile.xlsx”);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheet(“DemoSheet”);

I checked other posts, and they suggest using the Apache POI library. How can we implement this in our Iiq (IdentityIQ) setup

Thanks,
Ranjith M

5

Have you imported the library in lib folder?

6

Hi @Ranjith25,

Primarily you will need below jar as dependency and the same should be part of your WEB/INF/lib folder if you want to run it from Rule. Please add it and let us know if you still face issue.

poi-3.12.jar
poi-ooxml-3.12.jar
poi-ooxml-schemas-3.12.jar

Thanks

1 Like
7

Hi @ashutosh08

Is it possible to convert an .xls file to .csv or reading the data from xls without adding new JAR files, as I do not want to include additional JAR files in my scenario

Could you please guide me on this

8

Hi @Ranjith25,

Can you help with the use-case for which you want to read excel, So that accordingly we can suggest some workaround.

Thanks

9

Hi @ashutosh08

I imported the following jar file in webInf.lib folder
poi-3.12.jar
poi-ooxml-3.12.jar
poi-ooxml-schemas-3.12.jar
and Then, I tried running this code:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
File file = new File(“C:\demo\Demofile.xlsx”);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheet(“DemoSheet”);
but it shows the error: 'exception running rule: org/apache/xmlbeans/XMLObject.

So my doubt is whether I need to import additional JAR files that are present in the POI zip folder, or could you please provide a solution for this issue

Thanks
Ranjith M

10

Hi @Ranjith25,

There might be some dependency this jar have on other.
My recommendation will be to add all this dependency jar in your project in your IDE and once you have concluded about jar then import it in your WEB-INF/lib folder.

Thanks

11

Hi @Ranjith25,

Is it resolved?

Thanks

12

better use the CSV file reader and upload the file as CSV despite of XLS , so that you don’t have to add any additional jar files and also you can use inbuild sailpoint class to read the required data .

13

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.