Problem:
Why Excel rounds of the 18 digit numeric value to 15 places in a CSV file generated by scheduled search?
Resolution:
This is a limitation in MS Excel. If you open the .csv file in text editor then you would see complete 18 digit numeric value.
Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.
Same is mentioned in MS article
For example "testId" is the column having 18 digits values. Say 123456789123456789, if you open the scheduled search generated CSV in Excel, you will see it as 123456789123456000.
There is a work around for that, you can use the CONCAT operator to add a non numeric character to testId, in below i am enclosing testId with double quotes
| concat("\"", testId, "\"") as testId
This way Excel will not detect it as Numeric and will not round up. And you will see testId as "123456789123456789"
Instead if double quotes, you can choose other non numeric characters as per your choice.
Comments
0 comments
Please sign in to leave a comment.