Hello all.
I’m having big problems with zeros disappearing from cells where they should really exist.
As you can see from the attached image, all the “3” and “4” that are there, should instead read as “0.003” and “0.004”, but for some reason, if the number has only zeros after the third spot, all zeros before that spot also disappear.
Now, I’m operating on a .csv file that’s supposed to be exported from somewhere that didn’t screw the zeros up itself, but for now I can’t guarantee that.
Hopefully that’s not the case and it’s LibreOffice Calc doing this.
I know I can also turn a “3” into a “0.003” by typing an apostrophe in front of the numbers, but that is to absolutely no avail since the value in the cell gets back to what it was before the edit.
I’m guessing that is because of the file format.
Is there any way to make sure that it’s actually LibreOffice Calc which is removing zeros where it shouldn’t? Heads up, I do not have access to any other software to compare my .csv in.
Hoping it’s somehow helpful, I’m on Windows 10 Enterprise LTSC Build 17763, and my LibreOffice Calc version is 7.4.4.2.
Thank you to whoever will try to help.
The normal way seems to me to be to check that the source data is correct.
What language have you got set for LibreOffice and what language was csv created in?
Some countries use a stop for decimal separator, others use a comma. The other punctuation is then used for thousands separator
Sure you have. Any editor will do, as well as notepad.
Either click on the file with right mouse key and select another program to open the .csv-file
Or you copy the file and rename the copy to .txt Then double-click on the new .txt-file. It will usually open in a text-editor.
.
If you csv is ok, open it again with LibreOffice. Note you actually can change import on the dialog wich comes first. You can select a column head, then change the type in the dropdown above. The grid below shows a preview of import.
.
Maybe you can ask your admin. Otherwise you may upload a shortened version of your csv here to be analysed.
I tried importing the .csv to Notepad++ but I really can’t make heads or tails of what’s in it when it’s formatted like that.
I’ve also tried to search for exactly “3” or “4” and I did find some, but I’m not entirely sure that’s true however.
Gave a shot at changing some parameters on file opening, too.
No dice.
I’ll be waiting on the info about the source data being correct, because at this point I’m starting to think it’s more likely to be the case that it’s not.
Thank you.
@EarnestAl gave a screenshot below in this thread. Maybe note also the scrollbars at the sides of the import-grid.
So you can scoll to check on your lines 134 and see import directly while changing parameters.
It is not “formatted like that”. A text editor shows the content character by character as is. If you can’t make heads or tails of it, how could any software? May be we can if you would share your file.
No, not to “0.03”, but to “0.003” - and this is crucial.
Your data uses dot as decimal separator. Your locale uses dot as thousand separator. Unless you tell to the CSV import, that your data does not follow your locale, and needs another locale to be properly imported, any string that looks like a proper number with thousands will convert this way.
I.e.: 0.003 is meant in the CSV as “three thousandths”; but in your locale, this is read as “zero thousand, zero hundred three”, and is imported as such. On the other hand, 0.0033 can’t be read as proper number in your locale (because the thousand separator is put not after three digits, but after four), and so this is imported as string.
Just tell the CSV import to use, e.g., en-US locale.
Yup, 0.003, I missed a zero there cause I can’t type.
I tried to change the locale to en-US, however it didn’t seem to do the trick.
But thank you for the input nonetheless!
Don’t change the locale of your spreadsheet. You need to change the Locale on the Text Import filter. The Text Import filter needs to know the original settings of the existing csv so it can import it correctly.
That’s what I did, I’ve also just tried it again to make sure.
I’m still getting flat “3” and “4”. Tried opening the .csv with Google Drive as well under the suggestion of the support team of the website where I’m getting this data, but nothing differs.
At this point I’m really thinking it could be the source being uncorrect, I will post again to let you guys know so I don’t waste too much of your time.
Thank you.
Open the csv with Notepad to see what is actually in there and to see what separator is used
You could also upload your csv here, if contents are not sensitive. You may shorten the file in notepad/edit but obviously we need around 150 lines to see the place of error…
Did you save it?
I’ve got new info, apparently the source isn’t responsible for screwing the data up.
It’s something to do with Calc I guess.
I’ve exported another file and didn’t touch anything, imported it with En-Us locale, and the data shows up correctly.
However, when I apply some modifications (namely just deleting some useless data), nothing that would impact a .csv file really, and save it, then the data is forever screwed up.
How do I fix this?
Provide a sample csv before changing, and describe what you changed and how did you save, and what you did to see it’s broken - so that others could see it step-by-step themselves, and suggest.
When describing steps, showing screenshots at each step would be very helpful.
A note: saving the normally imported csv to ODS would indeed avoid the problem.
I’m linking a .rar with the untouched original file exported directly from the source.
There will be a lot of “NaN” here and there, that’s on purpose and irrelevant.
Now, if I just happen to delete the “time” column which I have no use for, and save the file, even if I never change the text import locale from English (USA) to anything else, all those “0.003” will change to “3” forever.
That file is also present in the .rar file attached to this comment.
Apparently any change at all to the file screws the data up.
But I really need to make adjustments to this file and have it work without values changing around.
https://ufile.io/bgsc4c2d
I should also add, my Windows 10 version is in Italian, and I’m starting to think it’s got something to do with this issue, because a friend that has everything set in English (USA) can actually edit the original file without having the zeros disappear.
Plus, he’s not using Windows but Arch Linux, and has let me know that it uses an UTF-8 encoding, while Windows apparently uses something different.
Aha. This explains everything.
You have imported the CSV that used decimal dot, using en-US locale. Your LibreOffice knew how to import numbers correctly. The numbers were shown correctly in the cells.
Then you pushed the Save button, thinking that the resulting CSV would use the same syntax as before.
But it didn’t. Namely, it didn’t use en-US locale at export - it used your it-IT locale. And the numbers were saved using decimal comma.
Compare the CSV before:
and after:
Now if you try to open this updated CSV, which now uses it-IT locale, using en-US locale again, you get exactly the same problem as initially - but this time, the en-US locale treats existing commas in the numbers as thousand separator, when they separate three digits.
So: the second time, open the saved CSV using it-IT locale.
Yyyyep. You are absolutely right.
Thank you so much (and to everyone else) for helping me on this one, much appreciated!