Support Article
When importing decimal values extra zeros added to some values
SA-75887
Summary
When uploading an Excel file with decimal values, additional zeros are included for some decimal values after the import.
Error Messages
Not Applicable
Steps to Reproduce
- Modify the out-of-the-box MSOParseExcelFile activity and save it as ParseExcelFile in the system.
- Remove some lines, which parse the Excel files, from the Java step.
- Use a link to upload an Excel file.
- Parse the file using the modified version of the MSOParseExcelFile activity.
Root Cause
A third-party product issue.
Resolution
Here's the explanation for the reported behavior:
Excel truncates decimals (for example, 5.1,8.8 etc) whose binary floating-point representation is infinite in order to display in the cell. Instead, it saves 5.1,8.8 as 5.0999999996 in its metadata.
Pega fetches the data from the metadata of the Excel file and stores the value that is present as the metadata. This is related to the MSExcel application.
For any modification, the Microsoft support team must be contacted.
Alternatively, perform the following local-change:
Add a step to loop through the decimal list that is read from the Excel file and format to two decimal precision using the out-of-the-box Rule-Utility-Function Math.Divide(Divisor,Dividend,Scale,Mode).
Published March 28, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.