Number, i18n and CSV download

[yohanboniface]

Hi there, me again! :)

It seems that when Grist is in French, the number columns are exported as text.

Here is an example document:

Untitled document(2).grist.txt

Column A is type Text, column B is type Number, column C is type Number with percent (but useless in this test).

Here is the downloaded CSV:

A,B,C
12.34,"12,34","0,1234"

I'd expect all column to be treated as number in the CSV:

A,B,C
12.34,12.34,0.1234

So any other tool that will consume this CSV could cast it correctly.

I may be missing something in my settings!

Thanks for your lights on this :)

And keep up the good work on Grist!

Cheers

[hexaltation]

Hello,

CSV isn't normalized. RFC4180 is just a description of most common practices.
c.f. rfc4180

When I export a document opened with french (france) locals containing numbers represented as quoted values, such as "1,234", LibreOffice can consume it without any problem.

Grist fails, casting it as text when importing it back.

I haven't tested MS excel by now.

@yohanboniface can you tell us which tools you tested that can't consume this .csv file?

IMO it's rather an import problem in grist than an export one.

Two, non excluding, options look good to me :

• never internationalize numbers in csv exports considering i18n is only representation, only matching

  ^[-+]?[0-9]*\.?[0-9]+$
  

  or maybe

  ^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$
  

• import french styled quoted comma represented floats (ex. "1,234") as number rather than as text.

Gristlabs people do you have an opinion on the wanted behavior in grist ? (@paulfitz @dsagal @anaisconce)

Best regards

[yohanboniface]

LibreOffice can consume it without any problem

I guess because your LibreOffice has a French locale too ?

@yohanboniface can you tell us which tools you tested that can't consume this .csv file?

https://github.com/mapbox/csv2geojson

But for example parse it with python or any lib, you'll have a src while I'd expect to have an int.

IMO it's rather an import problem in grist than an export one.

I do not agree. Exporting a number in its localized representation means that each tool consuming it later should be smart enough to know how to parse that localized number.
IMHO, a CSV export should contain data, not a localized representation of that data.

My two cents :)

[paulfitz]

Gristlabs people do you have an opinion on the wanted behavior in grist ? (@paulfitz @dsagal @anaisconce)

Yohan's arguments about the sanest behavior are persuasive to me.

In general for this kind of situation, if there is some behavior common to Excel and Google Sheets, we would follow that as a de facto standard.

Checking Google Sheets, I think I'm seeing behavior contrary to what Yohan would like? I make a spreadsheet, set it in French locale, add some numbers, export as CSV, check the CSV and see numbers like "10,3" rather than "10.3".

It would be worth checking Excel too. If Excel and Google Sheets are exporting in localized form, that would be a strong vote against Grist doing differently. Adding configurable behavior here could be reasonable.

[dsagal]

Confirming that the current behavior is intended to match Excel/Google, and that Excel also exports what you see on the screen. If I change my preferred "Number format" system preference, and reopen a file in Excel, the export to CSV will look different:

==> Downloads/Book1.csv <==
"1,231.04","January 4, 2024", € 55.66
==> Downloads/Book1b.csv <==
1.231,04;January 4, 2024; € 55,66

Note also that this isn't just about decimal separators. The output match the full range of formatting options: currency symbols, percents, number of decimals, date formats, etc.

So the best solution seems to be configurable behavior, like Paul suggested.

Let me note also a related "mirror" wish on the API side, collected into this issue: #880. In short, API endpoints return raw data, but sometimes it's very handy to be able to get it formatted, the way it's visible in CSV (this matters particularly for dates and references).