Question
Kelly Services
US
Last activity: 4 Oct 2018 13:54 EDT
MSOParseExcelFile Cannot Parse A Date(dd-mmm-yyyy) Cell Data.
Hello All,
I got a requirement were client wants to upload a spread sheet which contains business data. So this spread sheet contains different verities of data (text, numbers, decimals, date, etc...).
So, the date cells in the spread sheet are in dd-mmm-yyyy format and client is very rigid on this format. Client doesn't want to change the format of that cell to text nor remove it.
So, in Pega, we have created a binary file (template) where all the pega properties are mapped.
And we user the MSOParseExcelFile OOTB activity to parse the same.
The problem we are facing here is MSOParseExcelFile OOTB activity is not able to parse the date cells out of the excel sheet. Pega is reading the date from the excel sheet as some junk value as per below error. Please find below the complete stack trace from log file FYR.
Request all to help me how can we achieve this:
Reading an excel sheet (only the second sheet) and map that data to the Pega clipboard directly.
Hello All,
I got a requirement were client wants to upload a spread sheet which contains business data. So this spread sheet contains different verities of data (text, numbers, decimals, date, etc...).
So, the date cells in the spread sheet are in dd-mmm-yyyy format and client is very rigid on this format. Client doesn't want to change the format of that cell to text nor remove it.
So, in Pega, we have created a binary file (template) where all the pega properties are mapped.
And we user the MSOParseExcelFile OOTB activity to parse the same.
The problem we are facing here is MSOParseExcelFile OOTB activity is not able to parse the date cells out of the excel sheet. Pega is reading the date from the excel sheet as some junk value as per below error. Please find below the complete stack trace from log file FYR.
Request all to help me how can we achieve this:
Reading an excel sheet (only the second sheet) and map that data to the Pega clipboard directly.
2016-02-18 14:14:05,449 [p-bio-8443-exec-1051] [TABTHREAD1] [ ] [ DPDA:01.01.01] ( internal.access.DatabaseImpl) ERROR bmsbiologics-1.pegacloud.com| Proprietary information hidden dpdascientist - A commit cannot be performed because a deferred save of instance BMS-DPDA-WORK DPDA-64 failed
2016-02-18 14:14:05,449 [p-bio-8443-exec-1051] [TABTHREAD1] [ ] [ DPDA:01.01.01] ( internal.access.DatabaseImpl) ERROR bmsbiologics-1.pegacloud.com| Proprietary information hidden dpdascientist - The deferred save failed with the following exception
com.pega.pegarules.pub.database.BadInputException: Trying to save an invalid page: Trying to save an invalid page: page is not valid
.StudyInfo.InitialDate: 42374 is not a valid date value
From: (H57D204B3725DFF616C7E74D2A76406A1: Proprietary information hidden)
at com.pega.pegarules.data.internal.access.Saver.validate(Saver.java:1325)
at com.pega.pegarules.data.internal.access.Saver.saveDeferred(Saver.java:327)
at com.pega.pegarules.data.internal.access.DatabaseImpl.saveDeferred(DatabaseImpl.java:4832)
at com.pega.pegarules.data.internal.access.DatabaseImpl.saveDeferred(DatabaseImpl.java:4816)
at com.pega.pegarules.data.internal.access.DatabaseImpl.saveDeferred(DatabaseImpl.java:4810)
at com.pegarules.generated.activity.ra_action_saveattachment_613d72462e140a21a2dcd20bd0fe791c.step4_circum0(ra_action_saveattachment_613d72462e140a21a2dcd20bd0fe791c.java:422)
at com.pegarules.generated.activity.ra_action_saveattachment_613d72462e140a21a2dcd20bd0fe791c.perform(ra_action_saveattachment_613d72462e140a21a2dcd20bd0fe791c.java:122)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_attachfile_c61fb0f1989f5cd69602bc73d33c43fd.step14_circum0(ra_action_attachfile_c61fb0f1989f5cd69602bc73d33c43fd.java:1322)
at com.pegarules.generated.activity.ra_action_attachfile_c61fb0f1989f5cd69602bc73d33c43fd.perform(ra_action_attachfile_c61fb0f1989f5cd69602bc73d33c43fd.java:288)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_saveattachment_390bc14473f4b75ddf95f91f85681add.step8_circum0(ra_action_saveattachment_390bc14473f4b75ddf95f91f85681add.java:699)
at com.pegarules.generated.activity.ra_action_saveattachment_390bc14473f4b75ddf95f91f85681add.perform(ra_action_saveattachment_390bc14473f4b75ddf95f91f85681add.java:198)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_saveallattachments_1139eaf5683425c836e33e925266f101.step8_circum0(ra_action_saveallattachments_1139eaf5683425c836e33e925266f101.java:831)
at com.pegarules.generated.activity.ra_action_saveallattachments_1139eaf5683425c836e33e925266f101.perform(ra_action_saveallattachments_1139eaf5683425c836e33e925266f101.java:196)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_uploadstudy_de60a1bb9dfc222f98e6ff3334a5b53a.step6_circum0(ra_action_uploadstudy_de60a1bb9dfc222f98e6ff3334a5b53a.java:571)
at com.pegarules.generated.activity.ra_action_uploadstudy_de60a1bb9dfc222f98e6ff3334a5b53a.perform(ra_action_uploadstudy_de60a1bb9dfc222f98e6ff3334a5b53a.java:171)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pegarules.generated.flow.ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.RunTaskActivity(ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.java:4201)
at com.pegarules.generated.flow.ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.Task_Utility1_circum0(ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.java:2812)
at com.pegarules.generated.flow.ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.perform(ra_action_enterstudydetails_19e0fbf9dfda84038da85fe5f5e6e8ab.java:614)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pegarules.generated.StartFlow_071001_pHqeTXSalkhabVGTYNWp6A.StartFlow07_10_01(StartFlow_071001_pHqeTXSalkhabVGTYNWp6A.java:167)
at com.pegarules.generated.StartFlow_071001_pHqeTXSalkhabVGTYNWp6A.invoke(StartFlow_071001_pHqeTXSalkhabVGTYNWp6A.java:76)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.resolveAndinvokeFunctionViaReflection(LibraryRuntime.java:133)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.invokeLibraryRuntime(LibraryRuntime.java:110)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeLibraryRuntime(Executable.java:8786)
at com.pega.pegarules.priv.generator.LibrarySupport.resolveAndInvokeFunctionViaReflection(LibrarySupport.java:181)
at com.pegarules.generated.pega_procom_flow.StartFlow(pega_procom_flow.java:605)
at com.pegarules.generated.activity.ra_action_completeassignment_7c229c203839163ba035bdb1bc564749.step24_circum0(ra_action_completeassignment_7c229c203839163ba035bdb1bc564749.java:2385)
at com.pegarules.generated.activity.ra_action_completeassignment_7c229c203839163ba035bdb1bc564749.perform(ra_action_completeassignment_7c229c203839163ba035bdb1bc564749.java:517)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pegarules.generated.callActivity_071001_KhcknoEKbSvWlpqH0tlTMw.callActivity07_10_01(callActivity_071001_KhcknoEKbSvWlpqH0tlTMw.java:122)
at com.pegarules.generated.callActivity_071001_KhcknoEKbSvWlpqH0tlTMw.invoke(callActivity_071001_KhcknoEKbSvWlpqH0tlTMw.java:82)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.resolveAndinvokeFunctionViaReflection(LibraryRuntime.java:133)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.invokeLibraryRuntime(LibraryRuntime.java:110)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeLibraryRuntime(Executable.java:8786)
at com.pega.pegarules.priv.generator.LibrarySupport.resolveAndInvokeFunctionViaReflection(LibrarySupport.java:181)
at com.pegarules.generated.pega_rules_utilities.callActivity(pega_rules_utilities.java:1432)
at com.pegarules.generated.activity.ra_action_performflowaction_cc66ac9b214b3cec592f3634031988bc.step18_circum0(ra_action_performflowaction_cc66ac9b214b3cec592f3634031988bc.java:1754)
at com.pegarules.generated.activity.ra_action_performflowaction_cc66ac9b214b3cec592f3634031988bc.perform(ra_action_performflowaction_cc66ac9b214b3cec592f3634031988bc.java:382)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_finishassignment_d0045083e1b45f7e2330fe3464a87513.step10_circum0(ra_action_finishassignment_d0045083e1b45f7e2330fe3464a87513.java:1435)
at com.pegarules.generated.activity.ra_action_finishassignment_d0045083e1b45f7e2330fe3464a87513.perform(ra_action_finishassignment_d0045083e1b45f7e2330fe3464a87513.java:229)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:641)
at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.runActivitiesAlt(PRThreadImpl.java:435)
at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.runActivities(HttpAPI.java:3252)
at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequestInner(EngineAPI.java:384)
at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1188)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:926)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:811)
at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequest(EngineAPI.java:330)
at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.invoke(HttpAPI.java:839)
at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl._invokeEngine_privact(EngineImpl.java:315)
at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:263)
at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:240)
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngineInner(JNDIEnvironment.java:278)
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngine(JNDIEnvironment.java:223)
at com.pega.pegarules.web.impl.WebStandardImpl.makeEtierRequest(WebStandardImpl.java:485)
at com.pega.pegarules.web.impl.WebStandardImpl.doPost(WebStandardImpl.java:290)
at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:338)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:379)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingThrowable(AppServerBridgeToPega.java:216)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethod(AppServerBridgeToPega.java:265)
at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doPost(WebStandardBoot.java:118)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Kelly Services
US
Thanks a lot to all for your thoughts.
I like the idea and suggestion of John where you read the 42370 number, and write some own function to convert from EXCEL<->JAVA DATES.
But, this is blowing up with in the MSOParseExcelFile OOTB activity in some java step. So, hence, using this OOTB activity, first of all we are not even able to read that plane number 42370 in order for later activities to process that using some other functions.
As of now, we have convinced end user that Pega will not be ready that cell from the imported excel spread sheet, instead, user has to select it on screen after importing it into the Pega. Right now, this work around is working fine as we are not considering this cells data from spread sheet instead it is been taken from Pega screen by user selection.
Thanks every one for your thoughts and suggestions.
Appreciate all your help.
Virtusa IT Consulting
AE
Hi Giri Konatham,
As the error suggests that you might be using a property to get the date (please correct me if I am wrong), if yes, Are you using 'Date' as property type of that property or 'Text'?
Could you please also let us know what's the PRPC version?
What's the Date format specified in Database?
Thanks,
Habeeb Baig
Kelly Services
US
Hi Baig,
I am using the 'Date' as the type of property in Pega.
I do tried using a 'Text' as the type of property in Pega, but no luck.
We are using 7.1.9 Pega cloud.
There is nothing specified in the data base yet.
As I said, we want to import the data in a spread sheet and one of the cells data is of type 'dd-mmm-yyyy' date formatted cell containing date in it. And pega is not able to read and import that cells data.
So what can be done from Pega side for us to read that date formatted cells data in the spread sheet?
Please suggest.
PEG
GB
42370 doesn't appear indeed to be a valid value.
Could you attach a sample spread sheet and point which specific row/column is facing the issue?
Is Excel able to recognise the cell as a date?
Pegasystems Inc.
DE
42370 stands for 2016-01-01 (January 1st this year) - but it is a date, not a time/date value. Unfortunately, the way Microsoft translates date values in Excel into numbers is a bit strange, compared with other approaches: see here. As long as the format of the column is provided to the cell, not to the contents, it is not relevant, no matter if it is dd-mmm-yyyy or yy/dd/mmmm. Internally, it is always a number, like that 42370 for the date 2016-01-01 or with a fraction for a time/date.
Based on that, a work around could be to handle the column as Number, and to convert the value to milliseconds since the begin of the epoch and from there to a java.util.Date.
Updated: 1 Mar 2016 9:37 EST
Pegasystems Inc.
GB
Note: EXCEL stores DATEs as DECIMAL numbers : see this link for more of an explanation : https://stackoverflow.com/questions/981655/how-to-represent-a-datetime-in-excel
The formatting is applied to this number based on your Windows Locale (or explicit formatting for the Cell); so the the values are not actually stored in a the format 'yyyy-mm-dd' etc.
The value 42370 (no numbers after the decimal) is 1st-January-2016, you can show this by:
1. Create a new blank EXCEL sheet and paste the number 42370 into the first cell:
2. Now right-click the cell and bring up the 'Format Cells' option:
3. Select 'Date' from the 'Category', leave the 'Type' as the default, click OK.
Note: EXCEL stores DATEs as DECIMAL numbers : see this link for more of an explanation : https://stackoverflow.com/questions/981655/how-to-represent-a-datetime-in-excel
The formatting is applied to this number based on your Windows Locale (or explicit formatting for the Cell); so the the values are not actually stored in a the format 'yyyy-mm-dd' etc.
The value 42370 (no numbers after the decimal) is 1st-January-2016, you can show this by:
1. Create a new blank EXCEL sheet and paste the number 42370 into the first cell:
2. Now right-click the cell and bring up the 'Format Cells' option:
3. Select 'Date' from the 'Category', leave the 'Type' as the default, click OK.
4. Now the Number is formatted/intepretted as a DATE when it is presented to you:
Try 42371; this will be 2nd-January-2016 etc.
And just for completeness , if you add in the DECIMAL component (which represents the fractional part of the day); and format as EXCEL 'DATE' - you will see the TIME COMPONENT (only when you click on the Cell itself though).
So for instance : 42370.5
Gives:
Secondly: I believe that MSOParseExcelFile is an Activity which was designed to be used from some OOTB (wizards for specs I think?) PRPC functionaility; and although many people use the MSOParseExcelFile for their own purposes - it is not designed to be a general solution for reading in EXCEL files; and therefore doesn't necessarily provide a mechanism that is able to deal with any type of EXCEL data.
(That said: if you were using an OOTB PRPC Functionality here, and that used MSOParseExcelFile, and that was found not to be coping correctly with some EXCEL file that it should do, that might require further investigation by Pega SMEs).
What you *might* be able to do here; is read the number (the 'Date') as an (EXCEL Decimal Format) Number: and then convert that (using a Function - which you will probably need to write yourself)
PRPC ships with Apache POI libraries out of the box: and this library provided functions from converting to and from EXCEL<->JAVA DATES.
See this Javadoc for instance: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DateUtil.html
(I haven't checked the version of Apache POI for those Javadocs, you will need to check this on your PRPC system to make sure you have the matching docs).
See also: this post - which gives some examples of working with Apache POI from PRPC Activities.
Accepted Solution
Kelly Services
US
Thanks a lot to all for your thoughts.
I like the idea and suggestion of John where you read the 42370 number, and write some own function to convert from EXCEL<->JAVA DATES.
But, this is blowing up with in the MSOParseExcelFile OOTB activity in some java step. So, hence, using this OOTB activity, first of all we are not even able to read that plane number 42370 in order for later activities to process that using some other functions.
As of now, we have convinced end user that Pega will not be ready that cell from the imported excel spread sheet, instead, user has to select it on screen after importing it into the Pega. Right now, this work around is working fine as we are not considering this cells data from spread sheet instead it is been taken from Pega screen by user selection.
Thanks every one for your thoughts and suggestions.
Appreciate all your help.
Virtusa
IN
There are two ways that excle can send. If we select the excle date column 'General' then it will send correct date to the Pega and we can set it as text format (Catch block will execute as getJavaDate() will accept double in the below function)
If excle column has 'Date' format then it will send as double so try block will excute and it will convert the date.
Approach: Write custom function and apply to the double values returned by the MSOParseExcelFile.
Custom Function: ConvertExcelDateToPegaDate
There are two ways that excle can send. If we select the excle date column 'General' then it will send correct date to the Pega and we can set it as text format (Catch block will execute as getJavaDate() will accept double in the below function)
If excle column has 'Date' format then it will send as double so try block will excute and it will convert the date.
Approach: Write custom function and apply to the double values returned by the MSOParseExcelFile.
Custom Function: ConvertExcelDateToPegaDate
The below funciton will allow 'General' and 'Date' formats of excle dates. If the excle column has 'General' then it will go to catch block and send the cand if the date column is in 'Date'
String DateVal =InputDate;
try{
double var= Double.parseDouble(DateVal);
java.util.Date DateConv=org.apache.poi.ss.usermodel.DateUtil.getJavaDate(var);
InputDate= new java.text.SimpleDateFormat("MM/dd/yyyy").format(DateConv);
}
catch(NumberFormatException e){
InputDate=DateVal;
}
return InputDate;
Note: Once we converted we need to set the values to "Text" instead of DateTime or Date at pega side.
PDN:
https://collaborate.pega.com/question/converting-double-value-date