Why are Form Data and Retrieve CSV Reports replacing numbers after the 15th digit when viewed in Excel?Issue
Form Data and Retrieve CSV Reports are replacing numbers when longer than 15 digits.
Excel uses a Floating Point Number system by default, this causes numbers larger than 15 digits to be represented in Exponent form.
Please see the following article from Blogs.Office.com for more information regarding why this is occurring:
Confirmation of the Behavior
For confirmation, in this example we will use an envelope created for testing purposes. The Envelope has three radio buttons, one signature, one initial, then data fields for the name, two lines of billing address, CC number, and Verification Code. The CC Number has had the mask set to Number and the value hidden by *******.
For the Credit Card, we’ll use the number 12345678912345689, this is three digits over the limit for floating point numbers.
Viewing the Form Data, we can see that this information is correctly populated.
After opening the CSV in Excel, the number listed has been replaced with 1.234567E+17. When viewing the cell you can see the value of the number has changed to 123456789123456000.
If you view this same information in another application, like PowerShell or Notepad++, you can see in the following screen shot that the information is accurate.
Opening this same CSV in another spreadsheet application, in my example I’ll be using Google Docs, you can see that the information is correctly populated outside of Excel.
Very Important Note regarding viewing CSVs in Excel:
When viewing these reports, keep in mind that if you open Excel and make any basic changes even if it is to cells other than the CC number, Excel will Overwrite all numerical values greater than 15 digits with their exponent form, which will essentially invalidate the report.
From the Sample Spreadsheet, I’ll make a change to the value of the card holder, to Sample Change.
After saving and reopening the new incorrect value is displayed
Currently the viable workarounds for viewing these kind of reports is to either view them outside of Excel, Or use Excel's Data Import feature to automatically format the cells as Text when the data is imported.
Instead of opening the CSV directly in Excel, you can import the data as text. This ensures it is displayed correctly.
- Open a new blank workbook in Excel.
- From the Data tab, select From Text from the Get External Data section.
- In the Import Wizard step 1, select Deliminted and My Data Has Headers (If viewing Retrieve reports that include headers).
- In step 2, select the Comma delimiter.
- In step 3, change the Data Format of any column that will contain 16+ digits from General to Text. Setting the Text format forces Excel to display the absolute value instead of rounding off.
Note: When you select your column, the radio option for General / Text / Date is going to change to the format of the column you select. IE: If the default column is General, the radio option will switch back to General once you click the column. If you do not select the Text option after clicking the column the import will not appear to have no effect.
Note: You will need to select the column before changing it to Text, once selected it will outline in black as shown above.
keywords: Retrieve, Form Data, Comma Separated Values, Excel, CDSE
- Click Finish, then OK. Once this is done you should see the correct information in Excel.