Date Format In Excel Import
I am working on Excel Import in Pega 7. i have 10 columns in Excel Sheet in that i have 2 date columns when I set the Cell format of the date value to text its working fine for me. But when I set the cell format of the date value to date its not parsing properly and showing some integer values. I am parsing the excel using MSParseExcelFile.
appreciate your help .
**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.
Updated: 14 Sep 2015 9:22 EDT
Pegasystems Inc.
GB
Hi Anand - I believe you mean the 'MSOParseExcelFile' (not 'MSParse...') here.
This is an OOTB Activity which is designed specifically for the 'Project Sizing' spreadsheets, so I'm not sure how well this works as a general-use Activity. (Although many people are using this Activity for their own specialized uses)
Are you able to provide screenshots (Steps/Params/Page&Classes) of your 'wrapper' activity you are using here, and any other supporting rules; as well an example EXCEL file which works, and an example EXCEL which doesn't work ?
What error do you get ?
Additionally: what if your PC Locale Set to ? I wonder if the date-format of the Windows Client is the same as what the backend PRPC system is expecting ?
And lastly - can you confirm exactly what version of 7.x you are using here ?
Cheers
John
Wells Fargo
US
Hi John
Yeah its MSOParse ExcelFile Activity which i m using . I am using this activity to read the data from excel sheet . I didnt modified any thing in the activity but while reading its not able to show the date as it is from excel sheet it is showing some integer number.
I can provide you the sample data in excel sheet ass screen shots here.
as you the see the date which is in excel sheet cell is of date format. If it is text its working fine but if the user selects date format and given some date then the date is being converted to some random decimal number .
as you can see in the second image if we select the number format for the same cell. The number which is shown in the above image reflects on the clipboard instead of date which is not valid.
Pegasystems Inc.
GB
So I *think* the (seemingly) random number is probably an EXCEL 'serial number' : which is the number of days since 1st of January 1900 (plus a time component after the decimal point for the portion of the day: ie) the time). You might therefore be able to convert this serial number to a Java Date (maybe Apache POI library - which MSOParse makes use of under the covers has a function already for this?).
See here: http://www.exceltactics.com/definitive-guide-using-dates-times-excel/#How-Excel-Stores-Dates
You might consider writing your own specific Activity using Apache POI here - where you might be able to specify that certain cells are expected to be of certain types : using MSOParse as a starting point ? Dunno.
John
Pegasystems Inc.
GB
As way of helping illustrate what you are doing : can you post your 'wrapper' Activity (Steps,Pages, Params: expanding any steps) which is calling 'MSOParse' here - that would give us the chance to build something here to replicate the issue.
(I don't know how to use MSOParse for instance - I would have to work it out : but if you already have it 'wrapped-up' that would be a good starting point for other to help out).
Cheers
John
Virtusa
IN
Refer the below link:
https://collaborate.pega.com/question/msoparseexcelfile-cannot-parse-datedd-mmm-yyyy-cell-data