Tuesday, November 3, 2015

Import Excel file in PEGA PRPC

Parse Excel File
Many a times, we need to import data from excel into PRPC data structures. There are quite a few ways to do so, here I am describing an OOTB internal activity to import data from Excel (XSLX) into PRPC clipboard.

As a pre requisite we need a template excel file uploaded as Rule-File-Binary.  Later this template file is used to interpret the uploaded spread sheets values and map into appropriate clipboard structures. Always have the first row as heading, and second row should have following syntax

{.PageListName().PropertyName input}

Next write an activity, to get the file location of the uploaded excel file (use FileUpload control to upload the excel sheet), and then call OOTB internal activity named MSOParseExcelFile. This activity takes two mandatory properties, first is the location and name of the uploaded excel file, second is the name of the template file.

With this MSOParseExcelFile would map the contents of the excel file in to appropriate clip board structures.

Here is a scenario illustrating the same

Scenario: Upload Employee Details (Name, ID, Manager, Age) from an excel file

Step1: Create Template File and upload it is Rule-File-Binary

 Create a entry in Rule-File-Binay and import the created Template

Step 2: Write an activity to call MSOParseExcelFile 

Param Supplied:

FSFFileName : Name and Location of the uploaded excel file.
TemplateRFB: Name of the uploaded binary file, it should follow <Directory name>!<Rule Name>!<extension>

Step 3: Run the activity to check results


PSEDO code to Import Actual file
Import processing
a.       Import the data file and open template
b.      call MSOParserExcelFile
c.       Covert the java page to property page
d.      Get the key values,
e.      To decide add/update Match key in existing page
f.        Update or add the page property
g.       Completed the update

Hope the above article is covers most of the required stuff. Please leave a comment with your practical experiences on how useful this article was and also any thing needs further explanations.