Defect #37581
closed
Incorrect number and CSV separators in several locales
Added by Attila Telek about 4 years ago. Updated 4 days ago.
Description
Hungarian csv separator is semi-colon and decimal separator is comma.
Latest locale config contains wrong settings for this.
root / trunk / config / locales / hu.yml @ 13552
general_csv_separator: ',' -> ';'
general_csv_decimal_sperator: '.' -> ','
Can you please fix this?
thank you
Files
| general_csv_decimal_separator.patch (12.1 KB) general_csv_decimal_separator.patch | Karel Pičman, 2022-08-22 14:54 | ||
| number_format_separator.patch (4.9 KB) number_format_separator.patch | Karel Pičman, 2022-08-22 15:12 | ||
| format_hours.patch (1.11 KB) format_hours.patch | Karel Pičman, 2022-08-23 09:22 | ||
| regional.png (143 KB) regional.png | Attila Telek, 2022-08-23 15:40 | ||
| example2.png (26.4 KB) example2.png | Attila Telek, 2022-08-24 16:05 | ||
| general_csv_separator.patch (10.5 KB) general_csv_separator.patch | Karel Pičman, 2022-08-25 10:13 | ||
| 37581.patch (12.8 KB) 37581.patch | Go MAEDA, 2026-08-22 12:56 | ||
| 0001-Sync-number-format-separators-and-delimiters-with-ra.patch (5.66 KB) 0001-Sync-number-format-separators-and-delimiters-with-ra.patch | Go MAEDA, 2026-08-30 00:54 | ||
| 0002-Align-CSV-separators-with-Windows-locale-conventions.patch (15.8 KB) 0002-Align-CSV-separators-with-Windows-locale-conventions.patch | Go MAEDA, 2026-08-30 00:54 |
Updated by Go MAEDA about 4 years ago
Can you please provide documentation showing that the current values of general_csv_separator and general_csv_decimal_sperator in Hungarian are incorrect?
For example, a CSV file output by the Hungarian version of the spreadsheet software, or web pages or screenshots that contains decimal values.
Updated by Karel Pičman about 4 years ago
I think that Attila is right concerning the decimal separator. The most of European countries use comma. Please see https://en.wikipedia.org/wiki/Decimal_separator#Usage_worldwide for reference.
Concerning the csv separator I wouldn't agree. As the name suggests it should be always comma - Comma Separated Values.
Updated by Karel Pičman about 4 years ago
Here is a patch bringing CSV decimal separators in sync with the wiki page.
Updated by Karel Pičman about 4 years ago
Update of the numbers' format according to the same rules.
Updated by Karel Pičman about 4 years ago
Concerning the number format separator, I have realized after I had changed it to comma, that it is not taken into account in displaying spent and planned hours on tasks. If I set it to comma, the time is still presented with dot. I think that the function Redmine::I18n.format_hours(hours) should be changed as follows:
def format_hours(hours)
return "" if hours.blank?
if Setting.timespan_format == 'minutes'
h = hours.floor
m = ((hours - h) * 60).round
"%d:%02d" % [h, m]
else
-- "%.2f" % hours.to_f
++ sprintf("%.2f", hours.to_f).gsub('.', l(:"number.format.separator"))
end
end
Updated by Karel Pičman about 4 years ago
- File format_hours.patch format_hours.patch added
Updated by Attila Telek about 4 years ago
- File regional.png regional.png added
Karel Pičman wrote:
I think that Attila is right concerning the decimal separator. The most of European countries use comma. Please see https://en.wikipedia.org/wiki/Decimal_separator#Usage_worldwide for reference.
Concerning the csv separator I wouldn't agree. As the name suggests it should be always comma - Comma Separated Values.
Maybe the csv extension has been founded before the regional settings or they didnt find a better name ('Something Separated Values' wouldn't be very professional)
With a hungarian region, comma separator couldn't be used because it is the decimal separator.
By the way I found that the separator character comes from windows regional settings:
more: https://www.koskila.net/how-to-change-the-delimiter-when-exporting-csv-from-excel/
It is deeply integrated to all office applications.
Here is an example csv content saved from my excel:
1st col: half;2nd column: Pi 0,5;3,1415
Updated by Karel Pičman about 4 years ago
With a Hungarian region, comma separator couldn't be used because it is the decimal separator.
If you try to export issues with time entries into CVS in Redmine, you will see that it could:
values,"0,5","3,1415"
Updated by Attila Telek about 4 years ago
- File example2.png example2.png added
Karel Pičman wrote:
With a Hungarian region, comma separator couldn't be used because it is the decimal separator.
If you try to export issues with time entries into CVS in Redmine, you will see that it could:
[...]
This could have been a good solution but Microsoft decided to use Windows Regional Settings as the column separator in csv files.
Excel uses semicolon in Hungary (and in 80 additional countries) and cannot interpret comma separated CSV files.
I have no info about other spreadsheet softwares but your example export cannot be loaded directly into excel:
Maybe Microsoft mis-interpreted the standard but they are the market leaders so redmine might be adapted to this solution.
Updated by Karel Pičman about 4 years ago
So, in order to support Microsoft Office, we should use semicolon as CVS separator, if the decimal separator is comma. Here is a patch.
Updated by Go MAEDA 19 days ago
- File 37581.patch 37581.patch added
I have prepared an updated patch combining general_csv_decimal_separator.patch (#note-3) and general_csv_separator.patch (#note-10), with a few corrections.
To determine the values, I checked the decimal and list separators used by Windows and Excel for each locale, using this table of 258 Windows locales.
- bg, bs, cs, el, et, eu, fi, hu, id, lv, mk, no, sk, sl, sq, sr, sr-YU, sv, tr, uk: separator ";" and decimal separator ",". These values are unchanged from the original patches.
- lt: separator ";" and decimal separator ",". Windows lt-LT uses "," and ";", like the locales above, so both values are changed.
- ro: general_csv_separator was "." (a bug since r2852) and is changed to ";". The decimal separator was already ",".
- ar, mn: only the CSV separator is changed to ";". Windows uses "." as the decimal separator for these locales, so it remains ".".
- vi: only the decimal separator is changed to ",". Windows vi-VN is one of the few locales that uses "," for both the decimal and list separators, so the CSV separator remains ",".
- fa: unchanged. The original patch set general_csv_separator (not general_csv_decimal_separator) to "٫", which appears to be a mistake.
Updated by Go MAEDA 11 days ago
- File 0001-Sync-number-format-separators-and-delimiters-with-ra.patch 0001-Sync-number-format-separators-and-delimiters-with-ra.patch added
- File 0002-Align-CSV-separators-with-Windows-locale-conventions.patch 0002-Align-CSV-separators-with-Windows-locale-conventions.patch added
- Subject changed from Wrong csv separator in Hungarian to Incorrect number and CSV separators in several locales
I have updated Karel Pičman's number_format_separator.patch and general_csv_separator.patch.
The first patch (0001-Sync-number-format-separators-and-delimiters-with-ra.patch) updates number_format_separator.patch. It aligns number.format.separator (decimal separator) and number.format.delimiter (thousands delimiter) in config/locales/*.yml with the latest rails-i18n locale files (https://github.com/svenfuchs/rails-i18n, commit 8bea339).
The second patch (0002-Align-CSV-separators-with-Windows-locale-conventions.patch) updates general_csv_separator.patch. It sets general_csv_decimal_separator to the corresponding number.format.separator and general_csv_separator to each locale's Windows list separator, based on https://sigmdel.ca/michel/program/fpl/list_sep_en.html.
There are two exceptions:
- Persian (fa): the Windows list separator is U+061B (Arabic semicolon), which Redmine's CSV import/export does not support yet, so
general_csv_separatoris left unchanged. - Serbian Latin (sr-YU): rails-i18n has no Latin-script Serbian locale, so its
number.formatvalues are left unchanged.