Forum Discussion
Importing a .csv file to Excel. Not all decimals are present in Excel
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
- All I am doing is, via excel, opening the .csv file. I'm not sure to what you are referring when you say "are you "opening" the CSV file by reading the data in VBA?".
jhbunce wrote: ``All I am doing is, via excel, opening the .csv file``
Amazing! It might help me help you if you could provide both an example CSV file and the Excel file after you open the CSV file.
Ideally, click "browse files" near the bottom of this window, and attach the files to a new response.
Alternatively, upload the files to a file-sharing website, and provide their download URLs.
If the forum does allow you paste bona fide URLs, modify them and enter them as text. For example, the modified URL for this discussion is techcommunity dot microsoft dot com /t5/excel/importing-a-csv-file-to-excel-not-all-decimals-are-present-in/m-p/3786480#M187301.
The CSV file can have just your example, 14759359.090908095 .
In the Excel file, be sure to format the cell as Number with 7 or more decimal places.
And be sure that the cell displays 14759359.0909081, not 14759359.0909080. That's the only thing that I find incredulous.
-----
Also, humor me with some directed experiments. Don't expect them to make sense. They're for my benefit. So follow the instructions precisely, even if they seem strange or unnecessary.
Suppose the cell where you "entered" (by opening the CSV file) 14759359.090908095 is A1. Please do the following.
1. Enter the formula =A1 & "" into some cell (B1). Select B1, press f2, then press f9. Copy the highlighted text in the Formula bar and paste into your response. Then press Esc to restore the formula in the cell.
2. Enter the formula =SUM(A1, -(A1 & "")) & "" into some cell (C1). Select C1, press f2, then press f9. Copy the highlighted text in the Formula bar and paste into your response. Then press Esc to restore the formula in the cell.
-----
In any case, whether you open the CSV file directly in Excel or import it, you will lose precision if your data has more than 15 significant digits and use it numerically.
We can discuss some work-arounds. But they might be too complicated with too little benefit.
Did you find any solution to the problem? I have the same issue : if I open the CSV in NotePad I see all my decimals ( 2316500062.2316500062101, but once I open the CSV in Excel my last decimals become 0 - > 2316500062.2316500000000
