I have data that I am exporting from an application in CSV format. I'm creating a pivot table with that data and for one larger set of values the pivot table is converting the resulting value to scientific notation. When I try to format the cell to change it to numeric it sets the value to zero.
Here's an example of the pivot table result:
Sum of Amount Months Entry Date
<1/1/2021
Account Title Account no Line of Business ID Location ID Months Jan Feb Mar
Revenue 4000 DEPT00200 LOC0101099 -1.5134E-09 -2.09548E-09 -3.46336E-09
The raw data looks like this (these are just a few rows):
Account title Account no Line of Business ID Location ID Entry date Amount
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2312.04
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -105.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -25.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1460.57
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1105.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -6526.08
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -112.32
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2796.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -401.51
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1953.75
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1440.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -829.92
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -276.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -168.96
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2673.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -460.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -50.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -474.20
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -211.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -289.55
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -25.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2370.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -847.20
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -7966.91
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -633.60
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -4037.22
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -566.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -54607.95
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1020.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -94.85
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1905.30
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -3240.15
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -31202.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -80.19
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -262.08
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2431.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -14521.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -833.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -180.00
I've scoured a few posts in the forums here and followed the suggestion NOT to open the CSV file but to import it as external data. I've tried that, along with converting the cells in the raw data to numeric before creating the pivot table but no matter what I try it results in the scientific notation and converting that results in zero.
Office 365.
Thanks.
Here's an example of the pivot table result:
Sum of Amount Months Entry Date
<1/1/2021
Account Title Account no Line of Business ID Location ID Months Jan Feb Mar
Revenue 4000 DEPT00200 LOC0101099 -1.5134E-09 -2.09548E-09 -3.46336E-09
The raw data looks like this (these are just a few rows):
Account title Account no Line of Business ID Location ID Entry date Amount
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2312.04
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -105.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -25.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1460.57
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1105.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -6526.08
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -112.32
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2796.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -401.51
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1953.75
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1440.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -829.92
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -276.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -168.96
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2673.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -460.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -50.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -474.20
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -211.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -289.55
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -25.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2370.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -847.20
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -7966.91
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -633.60
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -4037.22
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -566.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -54607.95
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1020.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -94.85
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1905.30
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -3240.15
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -31202.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -80.19
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -262.08
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2431.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -14521.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -833.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -180.00
I've scoured a few posts in the forums here and followed the suggestion NOT to open the CSV file but to import it as external data. I've tried that, along with converting the cells in the raw data to numeric before creating the pivot table but no matter what I try it results in the scientific notation and converting that results in zero.
Office 365.
Thanks.