Opening a CSV with Numbers show different number values sometimes

Question

Level 1

8 points

Opening a CSV with Numbers show different number values sometimes

Hello, my name is David, and I'm having a problem opening CSV files with Numbers. There are some columns where all values must be between 0 and 1, but when opening the file with Numbers, it shows only the decimal part as integers. Here's an example:

All values from columns "b" to "f" must have values between 0 and 1. In column "e", as you can see, there are some values higher than 1 such as 912 or 192, but, in reality, those values are 0.912 and 0.192. Opening the file with LibreOffice shows the correct values:

Something I also noticed is that when you select a cell which value is correct, in the bottom left part of the screen appers the word "Text", followed by the cell value. In the bad cells, instead of "Text", "Actual" is written.

As additional info, I generate this CSV files using a python program where I assert that those specific values are not greater than 1. I haven't touched any of the basic configuration that Numbers has. The strange part of all of this is that the values not well shown are only some of them, not all nor none of them.

Since I realised that I could try opening the files with another program I had no clue if what I was doing was well or not, but seeing this, I'm sure the problem only concerns Numbers app, but I still want to use it as I like it, so anyone can help to solve this problem? Thanks in advance for reading. Here I'm writing the file as additional text.

CSVfile.csv

MacBook Pro 16″, macOS 10.15

Posted on Aug 27, 2020 10:28 AM

Reply

Answer 1

Halliday

Level 4

2,677 points

Aug 27, 2020 11:55 AM in response to Daalma

Daalma:

I copied 441 lines of your CSV file, and pasted into Numbers 10.1 on my iPad, and everything worked: Columns “b” through “g” have values in the 0 through 1 range, and all numbers are correctly recognized as such.

I recommend you double-check your import settings.

What version of Numbers are you using?

I also agree with Badunit, that there may be some sort of interaction with your localized number system, if the radix (decimal) separator is the comma and the thousands separator is the period.

Reply

Answer 2

jaxjason

Level 5

5,063 points

Aug 27, 2020 2:13 PM in response to Daalma

I copied the entire CSV file out and imported it into Numbers and it does not have any issues.

I think this means its a localization issue.

Look under System Preferences application on your computer

click Language and Region

then click advanced

Can you send us a screen shot of that dialog that pops up?

thanks

Jason

Reply

Answer 3

Daalma Author

Level 1

8 points

Aug 30, 2020 7:28 AM in response to jaxjason

Hi Jason!

Thanks a lot for your answer, that was exactly the cause why it didn't work. As you can see, this was my original Language and Region advanced settings initially:

so its obvious that the problem came from there. As I'm from Spain, those character settings were set to be as they are here, but as I always have worked in English, I always use that notation. After setting the values as in your computer, everything worked as it should be!

Notations, measurement units and all those kind of things are still differences that should disappear really soon, as they're the cause of misconceptions, errors, and problems that could be easily avoided. Even if we all had the same language, we could understand better to anybody and everybody will benefit. The problem is and will keep being which to choose.

Fortunately, here seems that there are no barriers and everybody is willing to help. Thank you all for the fast and accurate answers! :)

David

Reply

Answer 4

Daalma Author

Level 1

8 points

Aug 30, 2020 7:10 AM in response to Badunit

Hello Badunit! My numbers version is 10.1 as well, so it hasn't to do with there version. I also reinstalled, so can't be by any kind of app configuration issue.

As you said, the problem has to be related with the kind of decimal separator. You know, I'm from Spain and here the decimal separation character is comma, instead of dot, so it will be related to that.

Your answer was really helpful, and now I understand the difference between those two kinds of internal interpretation of the imported data, being "Text" and "Actual". Thanks a lot!

David.

Reply

Answer 5

Daalma Author

Level 1

8 points

Aug 30, 2020 7:17 AM in response to Halliday

Hello Halliday!

Yes, as answered to Badunit, my Numbers app is updated as yours, with version 10.1 and it has to do with my system's local separators. In fact, the strange part keeps being why happens to only some values the misinterpretation, but as the error cause of the error comes from the system interpretation, when solved the problem will fully disappear and will not matter anymore.

Thank you a lot for you answer!

Reply

Answer 6

Badunit

Level 7

30,342 points

Aug 27, 2020 11:20 AM in response to Daalma

What version of Numbers? I imported a large portion of your CSV into 10.1 and it imported correctly. Given the words in this file, I wonder if your number system usually uses a comma for a decimal separator and if this problem is related to that (i.e., maybe a bug where it flips back to the comma decimal occasionally).

"Text" means the cell is text and it shows you the actual text in the cell. For instance, a custom format could have appended a few letters or something else to the text. The info in the corner tells you what is really in the cell.

"Actual" indicates it is other than text and tells you the actual value in the cell. If it is a decimal number, the cell could have been formatted to round to a few decimal places but the "actual" number in the cell is what shows down in the corner.

All of your cells other than those with letters in them should have imported as numeric and should say "actual" down in the corner. The fact that they do not again makes me think it has something to do with the decimal separator.

Reply

Opening a CSV with Numbers show different number values sometimes

Similar questions