Featured post

GetNextWork functionality in pega

What is GetNextWork in pega? Pega provided a functionality to get the most urgent assignment(task) for user to work on. This is configured o...

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 spread sheet (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 gives out put as List results where we can loop through the results and perform our own logic.
Here, Just uploading excel file and parsing it to create operators. Need a portal where we can upload excel file which is there in 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 portal and parse.
access group and portal
create harness rule to refer in portal.
harness
Create section rule and add OOTB control and button.
section and button props

OOTB Control AttachFile

Excel Sheet template: 
First row is for header information and is 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 enclose everything in braces.
binary file template

Excel sheet user data:
Pega will not use first row when it parses the excel. It will not include in PageList.
excel data

How to upload excel file in pega

Create Rule-File-Binary (Binary file) from Technical category.
binary file rule
Upload excel sheet template.
rule-binary-file
upload template

How to read excel file in pega

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

pxParseexcel activity

activity parse excel
activity parse excel2

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 parse 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 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.

upload file

tracer

page list

Lets see in pega if the operators are created or not.
users in pega
  
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 file path as "C:\fakepath\" on pxRequestor page in clipboard.
pxrequestor page

File path will be like this pxRequestor.pyFileUpload = ServiceExportPath+filename
We can check the ServiceExportPath value in clipboard pxProcess pages in System Pages.
pxServiceExportPath

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.
access deny exception

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 excel file in C:\PRPCPersonalEdition\temp\StaticContent\global\ServiceExport folder (if you are running on personal edition) in your local macine.
  • Define environment variables like JAVA and JAVA_HOME (install jdk).
Thanks for reading this post, hope it helps. Please share with others 😀


4 comments:

  1. One of my favourite topics. Very well explained here and like the part specifying probable issues people can encounter!!

    ReplyDelete
    Replies
    1. Thank you Raj, hope you will like other posts as well.

      Delete
  2. well explanation with examples.

    ReplyDelete
  3. I tried the above approach but I get fileNotFound exception. I tried the solution and as well hard coded the file path still the issue is same. Wrote small java code to check if the uploaded file is present in file://web:/StaticContent/global/ServiceExport and after running I got know it does not exists there. How to locate and embed the file path to successfully process excel.

    ReplyDelete