Spreadsheet formatting issue

V by viktor on 5 Jan 2025, edited 7 Jan 2025 #

I need to format a number for *.ods spreadsheet export, for example an area of a slab 24.16123648 -> 24,16 (goal is to get two decimals and a comma as a delimiter)

Here is my settings number(round({{value}}, 0.01), ",")

The issue is that in the *.ods output there is an extra apostrophe character before the number:

T by theoryshaw on 5 Jan 2025 #

I can't replicate this. What version are you on?

V by viktor on 5 Jan 2025 #

@theoryshaw said:

I can't replicate this. What version are you on?

currently I am at bonsai_py311-0.8.1-alpha250103-linux-x64
stable version bonsai 0.8 was also affected
Blender 4.3.2
Kubuntu 24.04

Just in case, I am enclosing the .ifc test file and corresponding CSV Settings (I have changed the file extension to .txt, because attaching of *.json is forbidden).

S by steverugi on 6 Jan 2025 #

I don't have the single quote either, but I noticed some odd behavior with comma separator in LibreOffice (not using it that much)

by the way, if you need to remove the preceding single quote you can use Data > Text to Columns like

V by viktor on 6 Jan 2025 #

The USA locale in your screenshot @steverugi gave me an idea to try to change the Libre Office locale settings.

with English (USA) locale the very same *.ods file doesn't show the extra single quote character
with Czech locale (see the screenshot) (and I also tried a German locale) the single quote is shown

However I am not sure it is 100% Libre Office's fault because when I don't use the CSV formatting in Bonsai, the single quote does not appear even with the czech locale.

V by viktor on 6 Jan 2025 #

Hmm, so even with the USA locale, despite the single quote is not shown, the number is not treated as a number in my Libre Office, so I cannot do operations like SUM...

A by avico on 6 Jan 2025 #

@viktor,

a workaround would be export as "*.csv", edit with a text editor (like notepad), changing separator to ";" and decimal separator to ",". Later, import with libreoffice calc

S by steverugi on 6 Jan 2025 #

OK

I set my LibreOffice Calc with Czech Locale Setting

I select the cells having a single quote before the number

I go to Data > Text to Columns and set Tab as below:

to make them a number I can operate with (see sum at the bottom)

V by viktor on 6 Jan 2025 # +1 votes

Thanks for the workarounds @avico @steverugi I can also omit the Bonsai's formatting step and set the desired decimal places afterward in LO's Format Cells

Do you think I should file a bug report?

S by steverugi on 6 Jan 2025 #

@viktor said:

Thanks for the workarounds @avico @steverugi I can also omit the Bonsai's formatting step and set the desired decimal places afterward in LO's Format Cells

I was actually about to suggest it..

Do yoy think I should file a bug report?

Yes, I think it's worth mentioning it

V by viktor on 7 Jan 2025 # +2 votes

Ok, reported . And I found a funny workaround:

there is no issue when I use number(12345, ",") instead of the number({{value}}, ",")