How to parse an excel using pyAttachStream

General
, ,
1

We have a requirement to parse an excel file using the file stream

  1. Attach an excel file received from another system via Service and store it in Data-WorkAttach-File table (not in any Content management system)
  2. Later browse the for the excel file and parse it using MSOParseExcelFile activity
  3. Getting error when trying to pass the pyAttachStream data to the FSFileName param in MSOParseExcelFile activity

as MSOParseExcelFile activity expects a File location.

2

@GayathriL

This is a bit of a hack but I’ve got this working by doing the following:

In your activity

  1. Open find the pzInsKey of the Data-WorkAttach-File instance that contains the excel file you want to parse.

  2. Write some java to call the following function, this will save a copy of the file to the temp directory on the node filesystem:

java.lang.String sendFile(java.lang.String aInstanceHandle,
                          java.lang.String aFileSourceReference,
                          boolean aIsBase64Encoded,
                          java.lang.String aFileNameReference,
                          java.lang.String aFileName,
                          boolean aPersistFileToServiceExport,
                          StringMap aHttpHeaders,
                          boolean aSendForDownload)

eg.

String strInsHandle = tools.getParamValue("InstanceHandle"); // pzInsKey of the Data-WorkAttach-File instance you want to open
String strFileSourceRef = "pyAttachStream";
Boolean boolIsBase64Encoded = true;
String strFileNameReference = tools.getParamValue("FileNameReference"); //File name, this is arbitrary but make sure you use the same when calling the parse excel activity.
Boolean boolPersistFileToServiceExport = true; // instructs the function to save file in the temp directory.
Boolean boolSendFileForDownload = false;

tools.sendFile(strInsHandle, strFileSourceRef, boolIsBase64Encoded, strFileNameReference, null, boolPersistFileToServiceExport, null, boolSendFileForDownload);

  1. Set param.FSFileName = pxProcess.pxServiceExportPath + <<>>

  2. Call pxParseExcelFile (or the MSOParseExcel in older versions of Pega). Remember to set param.bDeleteFile to true to ensure the temp file is deleted from the service export filepath.

3

@GayathriL since both out-of-the-box activities (MSOParseExcelFile and its successor pxParseExcelFile) require a file name and not a base64-encoded string, your options are somewhat limited. Depending on your use case you may find it easier to create an activity from scratch, either reusing Parse-XML rules - if you can expect Office Open XML files.

There may be another option to temporarily save a file from Data-WorkAttach-File, but I am not sure if this is appropriate.

4

@JoeH9464

Hi Joe, Thanks for the update and this seems to help me in parsing the file… But one issue what I see is.. the file gets downloaded after being parsed. How can I avoid it?

As per my requirement, I don’t want the file to be downloaded.

5

@GayathriL

I’ve found this activity which contains java to write a file to the temp directory from parameters.

CL:
Data-Artifact

ID:
pxCreateFile

This is working for me, just create a temp page of class Data-Artifact to run it (or save as the activity to a different class, it has no dependencies on the class).

Then you can call pxParseExcelFile in the same way as before.

6

Hi Gayathri

JoeH9464 solution worked for me, I tried in my local server.

  1. As soon as you receive the file from another system , if it’s necessary to store the file you can store it in Data-Work-Attach-File / Pega-Social-Document (if you are using 8.x version) else you can run pxCreateFile (Data-Artifact) activity to store the file in static content directory for parsing.
  2. Verified : Pega is storing created file in static directory
  3. file://web:/StaticContent/global/ServiceExport/{filename}, you can form this path and pass it to either pxParseExcelFile/MSOParseExcelFile activity and make sure that you are passing true for bDeleteFile param as best practice after parsing the file, removing the file from StaticContent directory

Thanks,
​​​​​​​Vinay

7

@JoeH9464

Thanks for the solution.

While calling pxParseExcelFile what did you put in the mandatory field TemplateRFB?

In this case you are not using any binary file but TemplateRFB is expecting the binary file, right?

8

@VinayKumarL16594575

For Joe’s solution above,

While calling pxParseExcelFile what did you put in the mandatory field TemplateRFB?

In this case you are not using any binary file but TemplateRFB is expecting the binary file, right?

9

@Deba2512 ,

To parse excel, we need to create a binary file with incoming excel format.

Can you please explain your requirement ?

Thanks,

Vinay

10

@VinayKumarL16594575 Hi , we have a requirment to parse the excel that not only has rows but also some other data.May i know if we can create a template for this , if yes please provide a sample template.

Attaching a sample excel that we need to parse. Thanks in advance.

Assessment Name test assessment name
Assessment Number 123
Template Name Test template name
Organization Name Human Resources
Question Description Response Justification Question Type
1.1 question 1 test description

Test

 Text
1.2 question 2 test description

Test

 Text
1.3 question 3 test descriptions

Test

 Text
1.4 test question 4 Test Option1

Test

 MultiChoice - single select