Question
Caprus IT Inc
US
Last activity: 13 Oct 2017 5:14 EDT
Parsing Excel from stream
Hi,
I have an excel saved in DB, while processing it is moved to clipboard. I am trying to parse the excel stream but MSOParseExcelFile takes filename. Is there any OOTB option to parse an Excel stream. I tweaked the MSOParselExcel file to take ByteInputStream but its not working.
Thanks..
Pegasystems Inc.
IN
Is your excel file saved as bytestream in DB? as in a BLOB column?
Caprus IT Inc
US
Yes its saved in DB. The reason to do it is to have this file available to agents running on all nodes. Due to security we cannot have shared folders between nodes. Please consider a node as 1 physical node, not a virtual node.
Pegasystems Inc.
GB
When you say it isn't working - do you receive an error that you could share here ? (Is the Binay BLOB not being found, or not being decoded correctly or is the File showing as corrupt or something else?)
Caprus IT Inc
US
I have attached the document which explains what I am doing...I have couple of other performance questions revolving this POC.
Task 1 : Make Excel stream parsing using OOTB.
Task 2 : If excel has 40,000 rows it would be better I load first 500 rows, process them and load next 500. This would reduce the memory usage and ensures it works fine on multiple file processings.
This post is for task 1. Once we figure out the solution for task 1 next work will be on task 2. We have third party libraries like Apache POI which can do both task 1 and task 2. Instead of adding these libraries and build a custom solution in Pega I am thinking to expose Apache POI solution as an integration point for Pega.
Before that I am researching if there is any feasible option in Pega. If Task 1 works, there is a chance to request users to break down the large excel file as an alternate solution for Task2.
Pegasystems Inc.
GB
Thanks for this.
Can you post the code which sets the variable 'xlFileName' - is that loaded from the Property 'Content'
Can add a logging statement to output (looks correct) base64 content of 'xlFileName'; just to make sure it is correctly loaded.
Caprus IT Inc
US
Hi John,
Attached file shows the code.
Regards,
Naveen
Pegasystems Inc.
GB
Thanks; did you double-check that the PARAM 'FSFileName' is being set or not.
Maybe add the following Java or similar and check the PRPC logs after running Activity:
String testParamName="FSFileName";
oLog.infoForced( testParamName+":"+tools.getParamValue(testParamName) );