Posted: 18 Feb 2016 17:18 EST Last activity: 4 Oct 2018 13:54 EDT
MSOParseExcelFile Cannot Parse A Date(dd-mmm-yyyy) Cell Data.
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|188.8.131.52 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|184.108.40.206 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
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?
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.
Posted: 5 years ago
Updated: 5 years ago
Posted: 1 Mar 2016 9:32 EST Updated: 1 Mar 2016 9:37 EST
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
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.
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.
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'