Excel processing in Pega

In this blog article, we will see how pega can read the Excel data. In the file listener series articles, we saw how you can read a CSV file. In this post, we will not be using listener, instead we will see about stand-alone APIs that can be used to parse the Excel file. The same can be plucked in file listener rules.

The article was created using earlier Pega 8 version! but the core concept remains the same! Use this blog article to learn the concepts and you can try the entire tutorial in the latest versions as well.

CSV stands for comma-separated values, is a plain text file and you can use comma as a delimiter to identify individual values. CSV files can be opened even in Notepad

What is XLS file?

Highly structured and organized file format represented in tabs to relate the data from independent tables.

Unlike CSV plain text, excel saves the data in binary file format (You see images, charts and other data representations can also be saved in Excel)

Now how to read a simple Excel file and process it in Pega?

As usual, Pega comes up with their own Out Of The Box implementation for Excel processing.

Let’s start step by step, with how we can implement Excel processing.

API details

Pega activity – pxParseExcelFile (8+versions) or MSOParseExcelFile (deprecated in Pega 8)

This simple 4-step activity does the entire parsing for Excel. All we need is to provide the right parameters for this activity and call it at the right place.

In Activity step 2 – We are opening a Rule-File-Binary instance (a binary file rule).

What Pega expects here is a Binary file rule that already exists in the system. You may get a question why?

We know that to parse any file or say parse any service response, Pega needs to know the data model. For example, In case of file listener, service file integration we need to tell Pega that this is the expected data model response by creating parse rules (delimited or structured or any) and right properties at the right layer so that the parsed data can be mapped. Pega engine takes care of the data mapping.

Similarly for Excel processing, Pega needs to know the data model in the Excel (header columns) and the property to which the parsed data should be mapped to.

Note: For Excel processing in Pega, do not use the separate parse rules, instead the parsing is handled in Java step in the parse activity.

So now why binary file rule?

Help the Pega engine 😀

The binary file will be used as a template for the incoming/uploaded Excel file. Java code in step 3, decodes the binary file and uses the header objects to map the column header in Excel file. So, we are helping Pega to understand that this is the expected Excel data.

In Activity Step 3, Java code is entirely responsible for parsing the Excel file.

The activity parameters

You see two mandatory input parameters.

FSFileName – This should hold the file name along with the location.

TemplateRFB – Rule file binary name specified in the format <Appname>!<FileName>!<FileType>

For example – webwb!sampletemplate!xlxs

Other parameters are not mandatory, but you can specify sheets to parse and the order. You can also delete the file after parsing. The description explains those parameters’ usage clearly 😊

Now we know some basics about how excel parsing works. Let’s get our hands dirty.

Business scenario: Organization ABC uses loan application to create and process new personal loans. The application gets the loan details with in an input file daily and the same gets processed by a file listener ( we will see this in my next post, where we can parse the excel from file listener). The employees using the loan application can also upload excel file from the user portal to create loans from the excel data.

Step 1: Decide the content of Excel file and prepare the data model in Pega.

Let’s say the file holds the minimum mandatory details – Loan ID, Customer ID, Loan start date, loan end date, loan amount

First, you can create a data class that can hold the data properties.

Below is the data model I created for this tutorial.

Step 2: Prepare a template excel file with two rows.

Header row describing the column name or content

Second row displaying the pega property it should map to.

The format for the second row should be

{.<Pagelist property>().<single value property> input}

In the below excel I have used the best known pagelist property – pxResults()

{.pxResults().LoanID input}

Note: Make sure to provide the right index in the second row. The Java code parse excel data based on this syntax)

As I said, help Pega to map the data to pega properties easily 😊

I created a new excel file – LoanDetailsTemplate.xlsx

Step 3: Create a sample template excel binary file rule form.

Records -> Technical -> Binary file

Upload the template Excel file we created in step 2.

Once uploaded, save the rule form.

Now it’s time to test the Excel parsing!

Our business requirement is end users can upload the Excel file from the user portal. You can easily implement this by adding attachment control to upload the file.

You need to know the file location. So, where does the file get saved?

First the answer 😊. If you are in the personal edition, then you can find all the files imported via filepath can be seen in – C:PRPCPersonalEditiontempStaticContentglobalServiceExport

You can find the default service export path location in pxProcess page

Global static content service export directory.

You can see all my import zips as well refactor backup zips are by default saved in Service Export directory.

Let’s see what happens when we upload a file from the user portal.

For testing purposes, I am using OOTB attach content from an existing case. I add a PNG file.

Now check the service export directory.

On yeah, here it is 😊

Okay now the big question, how should we pass the file location to the pxParseExcel activity.

Remember the first mandatory input parameters

FSFileName – This should hold the file name along with the location.

Pega handles it with the file upload control – FilePath. If you want to make use of the file path where the file is uploaded, then you should use this control to upload the file.

Let’s start implementing the file path control.

Step 1: Search and open the FilePath control. This is a non-auto-generated control.

It accepts two parameters

SetEncodingNow – name explains. Set it true to apply the encoding immediately.

pySetValueFromFileName – Updates any property with the value from file name.

The HTML control using the javascript event listener to achieve encoding and updating value as on change event.

So why do we need the javascript event listeners for these two actions.

Think of server-side and client-side refresh. The filepath control does not hit the server as soon as you upload the file (happens at the submit button or other server interactions) and hence, if you want to encode or update any property, you need to do at the client level. That is why we use the JS event listeners. We will see how it works in action soon.

Step 2: Incorporate the FilePath control in the right User Interface.

Include the custom control in a cell. For now I left both the parameters as empty.

Now upload the file from user portal.

Step 3: search the pxRequestor.pyFileUpload property value in the clipboard. You will not find in clipboard.

Step 4: Click on submit button (server-side invocation) and check the clipboard again.

You will see the file uploaded in the ServiceExport directory.

You see the value as file:// relative path location. This is after the server-side interaction.

Step 5: Now enable the encoding and update pyFilePath in the second parameter.

Step 6: Now if you check the clipboard as soon as you upload the file, then you will see the value updated in the pxRequestor page (without server hit) because of javascript event listener.

Why do Pega update the pxRequestor page?

As end users, they can upload different files at different times. So pega effectively tags the last uploaded file in the pxRequestor page.

We got the filepath along with the file name in the clipboard. Now we can use these values as input parameter for the api pxParseExcel 😊

Let’s test the pxParseExcel API.

Step 1: Prepare the right test data for loan details.

Create a new Excel file with the header and loan data.

Step 2: Upload the excel file in the filepath control.

Step 3: Check the ServiceExport directory for the file.

Step 4: Check the clipboard page pxRequestor.

Step 5: Now create a wrapper activity for pxParseExcel and pass the right parameters.

FileContent page was initialized and mentioned in the pages & Classes tab.

Invoke the pxParseExcel activity by passing the below parameters and step page as FileContent.

Step 6: Now run the wrapper activity and check the clipboard.

You see the value from Excel is successfully parsed in the clipboard.

With this data, you can do whatever you want 😊

Now a small tutorial for you guys. Include the Filepath control in an assignment. Use the parse wrapper activity as the flow action post-processing activity and check the parsed data after submitting the assignment.