Libreoffice 26 - decimal place deleted when pasting data

English
#1

Good day

I have a Calc file where I copy and paste data into from an external 3rd party source. The data comes in as a *.csv file. It has spaces, tabs and decimal numbers.

Since upgrading from Calc ver 7, I have noted a problem with Ver 26. The data in the *.csv file is correctly formatted. The moment I paste it into Calc, many mulitple numbers lose the decimal place. So if the csv file has a comma that marks the decimal place, Calc sometimes keeps the comma in the correct position and sometimes imports without the comma at all - this leaving whole numbers in cells as opposed to decimal numbers.

Further to this, I note that the formulas (like =sum) no longer add all the cells in a range, therefore all my totals are incorrect. I have to manually re-type the numbers in cells and re-create the formula in order to get the spreadsheet to function. When one is dealing with over 8000 rows of data, that is ALOT of double-checking, cross-checking and correcting of data. Never had this problem with Ver 7.

Any suggestions?

#4

In this image, we see what the data is supposed to look like.

LibreCalc_Error 2

#5

In this image we see what LibreCalc does with the data after importing / inserting / pasting.

LibreCalc_Error 1
LibreCalc_Error 1488×650 108 KB

#6

As in your screenshot, when there are three digits after the comma, the comma is interpreted as a thousands separator.
You must change the Locale value in the Text Import dialogue, so that the comma is taken as decimal separator in all instances.
image

This will solve…

…since numbers with comma are interpreted as text. See the difference between numbers and text with Ctrl+F8 (menu View - Value Highlighting).

#2

why do you copy? and paste instead ⇒ Sheet ⇒ insert_Sheet_from_File ?

#3

The program that I am exporting from allows two methods “copy to clipboard” or “save to file”. I have historically, over years, copied to clipboard and then pasted in Calc.

However I tested your suggestion now, using the “save to file” method and then “insert sheet from file”. However the problem persists. The comma gets erased from many multiple numbers

#7

The import process remains the same when you import from clipboard or file. Just set the right locale in the import dialog.

#9

If you upload a .csv file or a fragment thereof, we can provide precise recommendations.
To upload to the forum, you can first place the .csv file inside a .zip archive (as uploading .csv format is not supported on the forum).

#8

Prior to pasting or importing data, the column format (right click column header (“A”) - format cells) should be something like:

  • Language French (Canada)
  • User Defined category with format code: 0,00## (3rd in the Format list)
  • No thousands seperator, one leading zeroes.

Set the import data to “Standard” column type on selecting the column in the paste prompt import preview, or Data>Text to Columns import preview.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.