How to read excel file in pega

Parse excel in pega?

If we want to upload multiple operators and create operators/access groups and load data instances which are in the spreadsheet (excel sheet), we can do this in pega and pega can parse excel file. Uses OOTB (Out of the box) activity to parse it and give output as List results where we can loop through the results and perform our own logic.

Here, Just uploading an excel file and parsing it to create operators. Need a portal where we can upload an excel file that is there on the machine.

Rules to achieve this process.

• Portal, Harness, Section, Activity, and Binary File.
• Excel sheet template (to upload in Binary file rule)
• Excel sheet containing user data to upload in the portal and parse.

create harness rule to refer to the portal.

Create section rule and add OOTB control and button.

Excel Sheet template: 

The first row is for header information and is an option but will be good if enter to avoid confusion. Second row is for pega properties and we need to give in the format {.pxResults().pyUseridentifier input}

input is the keyword required by excel after each property name and encloses everything in braces.

Excel sheet user data:

Pega will not use the first row when it parses the excel. It will not include in PageList.

How to upload excel file in pega

Create Rule-File-Binary (Binary file) from the Technical category.

Upload excel sheet template.

How to read excel file in pega

Activity to process the user data by using an excel template file.

pxParseexcel activity

Activity steps explanation:

As MSOParseExcelFile activity is deprecated we have used pxParseExcel activity.

Created new activity (UploadUsers) to parse excel data by using template (Rule-File-Binary) binary file there in the system.

Step 1: Page-New "UserList" Code-Pega-List (Page List) to hold the values when parsing activity process the excel file.

Step 2:Property-Set

    Setting parameter values for pxParseExcel activity, this OOTB activity is required file name and template.

Param.FSFileName   = "file://web:/StaticContent/global/ServiceExport/Bulkuserdata.xlsx"

Actually it should be "pxRequestor.pyFileUpload". Since this, on Personal edition, it is throwing an error "FileNotfoudnException" because we will have the value as "C:\fakepath\Bulkuserdata.xlsx". 

Param.TemplateRFB = "excel!UserSheetTemplate!xlsx"

Name of the template created and uploaded in the Binary File rule.

Pass these 2 Parameters in the next step for pxParseExcel activity.

Step 3: Call @baseclass.pxParseExcelFile OOTB activity to parse the excel and returns results on the step page (PageList) mentioned.

Step 4.0:Property-Set We have excel rows in PageList, we can loop through the pages and map the values to parameters.

Step-4.1: Call Data-Admin-Operator-ID.CreateOperator (OOTB) activity to create operator.

Step-5: Page-Remove Removing pages from clipboard memory.

Will look into the run time process and results.

Let's see in pega if the operators are created or not.

  

Possible issues:

1. FileNotFoundException: [MSG][Error in parsing Excel file C:\fakepath\Bulkuserdata.xlsx][STACK][java.io.FileNotFoundException: file://default:/fakepath/Bulkuserdata.xlsx

According to the specifications of HTML5, a file upload control should not reveal the real local path to the file you have selected, so we will have a file path as "C:\fakepath\" on the pxRequestor page in clipboard.

File path will be like this pxRequestor.pyFileUpload = ServiceExportPath+filename

We can check the ServiceExportPath value in clipboard pxProcess pages in System Pages.

pxProcess.pxServiceExportPath = file://web:/StaticContent/global/ServiceExport/

file name = Bulkuserdata.xlsx

Param.FSFileName=pxRequestor.pyFileUpload="file://web:/StaticContent/global/ServiceExport/Bulkuserdata.xlsx"

2.pzUnrecognizedFileTemplate :Data-Admin-Operator-ID)pzUnrecognizedFileTemplate UserSheetTemplate

If we have wrong file path passed pega will throw this error.

3. Access deny exception: if pega don't have access to root folders.

Possible solutions:

• Set Param.FSFileName=pxRequestor.pyFileUpload="file://web:/StaticContent/global/ServiceExport/Bulkuserdata.xlsx"  before calling parse activity and pass these parameters.
• Add pega URL instance to "trusted" sites.
• Place an excel file in the C:\PRPCPersonalEdition\temp\StaticContent\global\ServiceExport folder (if you are running on personal edition) in your local machine.
• Define environment variables like JAVA and JAVA_HOME (install JDK).

Thanks for reading this post, hope it helps. Please share with others 😀