Reading Excel sheet attached to email using email listener and copy data to Clipboard for processing

[danprashant (Prashant Daniel Maxwell Samuel)]

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.

Step 2-

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.

Step 3-

Declare a Local Variable. eg. myAttachStream of datatype string.

Step 4-

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();

Step 6-

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: -

[shashikanthp5683 (shashikanth prabhu)]

@danprashant will this work if attachment is Zipped?

[KPRao (PADMARAO K)]

@danprashant It’s not working getting unrecognized file error.

and follow the same steps

[Razauddin (Razauddin Ahmed)]

@danprashant - Hi, i have a similar requirement wherein i am using the above changes but seeing unrecognised template error while excel parsing. Can you let me know if you faced this issue and any possible solution for it? Also what should be the fs file name param while calling this new parse activity?

[Anoop_Krishna]

@RazauddinA16824638 Were you able to fix this?

[K_K_Sai_Vardhan (Kada Krishna Sai Vardhan)]

@danprashant what is the value you have assigned to the “FSfilename”?, from where did you get the value?

[K_K_Sai_Vardhan (Kada Krishna Sai Vardhan)]

@Razauddin hi, i am also facing the same issue, did you find any way to resolve this?,value for param.FSfileName?

[Anoop_Krishna]

It’s the file name from service export directory. You need to first take file to service export directory, then call pxparseexcelfile by passing FsFileName.

[KishoreR7037 (Kishore R)]

@danprashant if we try to replace the above line of java code with the following :-

byte bytes = myAttachStream.getBytes();

We are getting a error while checking in

Error: USING_PAGE_ERR:Using page was not found. Please try again.

[ARNABP40 (ARNAB PAUL)]

@danprashant It’s not working getting unrecognized file error.

Any other configurations needed?

[Razauddin (Razauddin Ahmed)]

@Anoop Krishna Hi no not yet.. Any solution you may suggest?

[RahulG8884 (Rahul Gurrapu)]

@Anoop Krishna

store pyAttachNames & pyAttachValues from pyAttachmentPage in parameters.

Save as pxParseExcelFile OOTB activity and update Step 3 java code & use attachmentValue parameter.

From line 28, update the code as below

if (templateMap != null) {
byte streamInBytes = new com.pega.pegarules.pub.util.Base64Util().decodeToByteArray(tools.getParamValue(“attachmentValue”));
uploadedFileIS = new java.io.ByteArrayInputStream(streamInBytes);

[Anoop_Krishna]

@RahulG8884 but what would you pass as parameter to FSFileName?

[NoorlinH (Noorlin Hameed)]

@danprashant

We are unable to update step 23 in the activity (pxParseExcelFile).Getting following error. Any suggestions ?

“USING_PAGE_ERR:Using page was not found. Please try again.”