CODEWITHSUNDEEP
javaexcelopencsv
Vignesh_A
Vignesh_A

Reputation: 548

Opening CSV file in Excel converts some string fields with numbers(length>20) to scientific notation

I am writing a csv file using opencsv. I have a csv field which is of type string and can have only alphanumeric of size 20 . At times the value contains only numbers (i.e) without alphabets.

while opening that file in notepad provides complete value < 66688888888831111111 >, the same file when opened in excel converts the string like : <6.66888888888311E+019>

Is that the expected behaviour or is there any way to avoid conversion to scientific notation.

Upvotes: 0

Views: 1357

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86774

Numbers in Excel are floating point. If you open a CSV containing the value 

66688888888831111111

it will display as 

6.66889E+19

If you format it as text the display does not change. If you format the cell as Number (preventing the change of display to exponential notation) the result is 

66688888888831100000

This indicates truncation of low-order mantissa bits, and the number of significant digits (14) indicates the internal representation is a 64-bit long, as you can see by entering the value in this online converter.

It gets even worse. Since CSV is a pure text format and cannot store cell attributes, there's no way to tell Excel to handle that column as text instead of interpreting it as a number. Even if you put quotes around it in the CSV, Excel will still interpret it as a number. If you then save the loaded sheet as a CSV you'll get the truncated value.

Upvotes: 1

Related Questions