Question
Frontier BPM, Inc.
US
Last activity: 27 Sep 2019 17:01 EDT
ExcelReader - capturing invalid date formats
We are using the ExcelReader jar (from insurance) to intake an excel file. We've defined some columns as "Date" fields (in the hashmap passed to the readExcel method). It looks like if the date isn't a valid format (in excel), the value is getting returned as blank. We need to capture that condition as an error (some of these date columns are not required and will therefore not get caught by any validate rules).
couple of questions:
1) is there any documentation on the ExcelReader class (methods, etc.)?
2) someone had pointed us at the getErrorList() method, but it's not capturing the invalid date (or we can't interpret the ArrayList. Is there another way to capture these format errors?
3) is there any way to get the "raw" value from the cell? we could then check if it's truly a blank or just an invalid format
Bits in Glass
IN
Hi @Jason.Lavigne,
You can import the excel with the help of OOTB activity MSOParseExcel and it is simple to use.
Frontier BPM, Inc.
US
unfortunately we need the column mapping ability in the ExcelReader jar (the order of columns changes from file to file)
Pegasystems Inc.
IN
Hi,
Regarding your question#1 Documentation on the ExcelReader is below :
Apache POI 3.11 API has been used to process excel files. Excel processing code has been written in java and packaged in jar file with name “ExcelReader.jar”. This jar has following classes. The source code has been shipped in the jar file.
1.Package: com.pega.insurance.pui.excelreader.processor − ExcelReader.java – Used to read the excel file and returns the list of data objects − ExcelWriter.java – Takes an array list of data and write its contents into an excel sheet (XLSX) − *DataLoader.java – Takes an array list of data and loads it into data base table – Applicable only for census − *DataCleanser.java – Validates the data – Applicable only for census − CensusValidator.java – Performs business validations needed for census import − LocationValidator.java - Performs business validations needed for property locations upload − LossHistoryValidator.java – Performs business validations needed for prior claims history
2.Package: com.pega.insurance.pui.excelreader.utils − CommonUtils.java – Contains utility methods − DBConn.java – Returns a data base connection − Test.java – Test stub − TestPropertyLocationsUpload.java - Test stub − WriterTest.java - Test stub
3.Package: com.pega.insurance.pui.excelreader.beans − ErrorInformation.java – Java bean to hold the error information
Hi,
Regarding your question#1 Documentation on the ExcelReader is below :
Apache POI 3.11 API has been used to process excel files. Excel processing code has been written in java and packaged in jar file with name “ExcelReader.jar”. This jar has following classes. The source code has been shipped in the jar file.
1.Package: com.pega.insurance.pui.excelreader.processor − ExcelReader.java – Used to read the excel file and returns the list of data objects − ExcelWriter.java – Takes an array list of data and write its contents into an excel sheet (XLSX) − *DataLoader.java – Takes an array list of data and loads it into data base table – Applicable only for census − *DataCleanser.java – Validates the data – Applicable only for census − CensusValidator.java – Performs business validations needed for census import − LocationValidator.java - Performs business validations needed for property locations upload − LossHistoryValidator.java – Performs business validations needed for prior claims history
2.Package: com.pega.insurance.pui.excelreader.utils − CommonUtils.java – Contains utility methods − DBConn.java – Returns a data base connection − Test.java – Test stub − TestPropertyLocationsUpload.java - Test stub − WriterTest.java - Test stub
3.Package: com.pega.insurance.pui.excelreader.beans − ErrorInformation.java – Java bean to hold the error information
Some re-usable Pega 7 Platform rules have been coded as part of the utility.
You can refer below PUI userguide.
https://community.pega.com/system/files/pdfs/pui-group-74-user-guide.pdf