Forum Discussion
Importing a .csv file to Excel. Not all decimals are present in Excel
I have a.csv file that, when viewing it via Notepad++, I can clearly see numbers in this file with as many as 10 decimal positions to the right of the decimal point. However, when I open this file in Excel, it appears to be rounding/truncating at 8 positions. I've increased the field size, but the additional values present in the .csv are not showing ; just zeroes. Is there a bug here or an advanced option I am missing that will allow all decimal positions to be visible in Excel? I'm on version 2303 (Build 16130.20306).
Please provide some examples.
On input, Excel interprets only the first 15 significant digits (after any leading zeros). Any digits to the right are replaced with zeros.
Also, Excel formats only up to the first 15 significant digits (rounded). But Excel will format only up to 30 decimal places.
So if your values are less than 1E-16, you need to format as Scientific with 14 decimal places to see the max precision.
JoeUser2004 - here is an example. In the .csv file, when viewing it via Notepad++, I have this value -= 14759359.090908095. When I open this file in Excel, the value is 14759359.0909081. If this is a field size limitation, is there any plans by MS to expand the size?
jhbunce wrote: ``this value -= 14759359.090908095. When I open this file in Excel, the value is 14759359.0909081``
I'm surprised and incredulous.
When I enter that number in Excel, I get 14759359.0909080, which is the truncated first 15 significant digits of the string 14759359.090908095.
I wonder: are you "opening" the CSV file by reading the data in VBA?
VBA does not truncate the data entry. So, when we type 14759359.090908095, we get that exact binary value.
Caveat: But since VBA displays only the first 15 significant digits (rounded), like Excel, that value will change to the binary value of 14759359.0909081 if the line is edited.
Aside.... The binary value of VALUE("14759359.0909080") is different from the binary value of VALUE("14759359.0909081"). And both are different from the binary value of vbvalue("14759359.090908095"). See the implementation of vbvalue below.
-----
Re: ``is there any plans by MS to expand the size?``
I would have no idea, since I do not work for MSFT. I would say that it is unlikely, since a change would present significant backward compatibility issues. However, MSFT could provide an option.
Although Excel does have an arbitrary limit on data entry, the primary limitation is the internal binary representation, which is 64-bit binary floating-point.
Contrary to most online documentation, the internal representation is not limited to 15 significant digits.
But it can be approximated by up to 17 significant digits with no loss of precision when converting between decimal and binary.
I suspect that is the data that you see. For example, 14759359.090908095 is the 17 significant digit approximation of the value that we might write as 14759359.0909080 + 9.50E-08 or 14759359.0909081 - 5.59E-09.
-----
Function vbValue(s As String) As Double
vbValue = CDbl(s)
End Function
For the numbers Excel keeps 15 digits totally, the rest is truncated.
PS....
jhbunce wrote: ``when I open this file in Excel, it appears to be rounding/truncating at 8 positions. I've increased the field size, but the additional values present in the .csv are not showing``
When you open a CSV file directly, initially the cells are formatted as General. If you increase, the width of the column, the General format still displays only 11 characters, including decimal point and any minus sign.
So, by default, 12.3456789012345 is displayed as 12.3456789 or with less precision.
But you should still see 12.3456789012345 in the Formula Bar.
And for that example, you can format as Number with 13 decimal places to see the full precision in the cell.
