Capgemini America Inc.
Last activity: 2 Feb 2023 1:45 EST
Reading Excel sheet attached to email using email listener and copy data to Clipboard for processing
Recently, while trying to understand if an email Listener can read an xlsx attachment and parse the data, I was unable to find any OOTB options.
Hence, I tried to modify the existing OOTB rules available in Pega and I was able to create an activity that would read the excel attachments from email (given the fact that you create a template of the xlsx file as binary rule in Pega).
Email attachments processed by email listeners will be available in the page pyAttachmentPage belonging to Data-ServiceMessage class. The attachment will be available as an attach stream.
What you need to do?
You need to create an activity similar to pxParseExcelFile with an additional parameter (eg.AttachStream). Please refer attached snapshots for better understanding.
Call this activity on step page (of class Code-Pega-List), and pass the excel stream into the parameter "AttachStream". Please note, you need to create a binary file containing the template of the xlsx that you are trying to read and the same needs to be passed in the parameter "TemplateRFB". Please find snapshot below for reference.
How do you do it?
Step 1 -
Perform a "Save-As" of pxParseExcelFile" or create a similar activity and DO NOT remove any steps from the activity.
Declare a Parameter eg. "AttachStream" of datatype string. This parameter will act as an input parameter to the activity. The attach stream of the excel file available in pyAttachmentPage page needs to be passed as input to this parameter.
Declare a Local Variable. eg. myAttachStream of datatype string.
Add a Property-Set step, to update the value of Local.myAttachStream from Param.AttachStream.
Step 5 -
In the Java code, modify line 23, where pxParseExcelFile tries to get the attach stream value from the location where the file is being uploaded.
Remove the following code from line 23 -
byte tempBytes = new com.pega.pegarules.pub.util.Base64Util().decodeToByteArray(tools.findPage("TemplateFile").getString("pyFileSource"));
Since we want to read the stream value of excel attached to an email, replace the above line of java code with the following :-
byte bytes = myAttachStream.getBytes();
Save the activity and call the new activity on a StepPage, where you want the pagelist to be populated based on the input AttachStream parameter.
Now, execute the activity, and the data from xlsx will be found on the steppage.
Snapshot of how activity needs to be called: -