Importing a .csv file to Excel. Not all decimals are present in Excel

Copper Contributor

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). 

10 Replies

@jhbunce 

 

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.

@jhbunce 

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.

 

@Joe User - 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.

@Joe User 

 

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

 

 

@Otilia89 

That is unavoidable. 2316500062.2316500062101 has 24 significant digits. But Excel displays numbers only up to the first 15 significant digits (rounded), replacing any digits on the right with zeros (courtesy of @Joe User),

@Hans Vogelaar

 

I actually already found a solution in the meantime. Instead of just opening the CSV file in EXCEL you use the button from the menu DATA ->  click FROM Text / CSV

 

when the import window opens you must click : do not detect data types. This way excel imports all the data from CSV, with all the decimals. 

 

Worked for my above example.

 

Otilia89_0-1690470553987.png