Sometimes Excel converts CSV integers into scientific notation and/or truncates decimal points. This behavior is undesirable in some cases. There is an easy way to avoid this (see below). But before we apply the solution we need to know: when exactly does this happen?
10 digits is safe for integers, 11 digits is converted to scientific notation.
Inserting a tab character before an 11 digit integer prevents Excel from converting the number to scientific notation.
Here is some Python code that will insert the tab for appropriate cell values.
if len(cell_value) > 10: return "\t%s" % s
For example, the CSV input would be:
And the CSV output would be:
Notice that a tab was inserted before the integer that was greater than 10 digits long, while the integer that was exactly 10 digits long was untouched.
15-n decimal places are kept the rest are thrown out, where n is the number of digits left of the decimal point. For example, if 2 digits are to the left of the decimal point then 13 digits are kept to the right of it.
Inserting a tab character before a decimal with more than 15 total digits will make Excel keep all digits.
These two behaviors when opening CSV in Excel are consistent regardless of the column location of the value. In other words, it can be at the beginning, end or middle of a series of columns.
In each case, inserting a tab character will stop Excel from converting integers to scientific notation and truncating decimals.
These tests were performed with Microsoft Excel for Mac, version 16.9 (180116).