Apache OpenOffice (AOO) Bugzilla – Issue 3516
save/import of CSV files troubles in german localization environment
Last modified: 2013-08-07 15:15:02 UTC
If you run windows (also any other operating system like linux/kde or gnome) and you use a localization where the decimal-point is not a "." but a "," (like in german) the export/import of CSV files makes troubles. example: if you save this column (3 numbers: in american notation they are 21 22.1 and 24.34): [A] 21 22,1 24,34 you get [A] | [B] 21 | 22 | 1 24 | 34 it is possible to workaround this, but if you don't know about this when you open such a file you stumble into this trap and all your floating-point numbers are splitted in seperat columns. my suggestion is to replace the "," with a ";" as a seperator ... but i don't know if this is possible or if there is another convenient solution.
Hi Harald, this is not a bug. If you open such a file you get an dialog for choosing the separation character. If you select a space or a tab as separator you get your numbers into one column. The most significant point is that the file format is based on commas which is reflected by the extension CSV. This is COMMA SEPARATED VALUE. So I have to close it as INVALID Frank
closed as invalid
hi again. i think you didn't get the point. please take a second to read my concern and sorry for being so annoying. i know CSV is not a good format, but this is IMHO important: after short discussion and rethinking this the main point is that floating-point numbers are written in the same number-format, the number is formatted in each cell. there are two different types of comma-seperation: 1) "." for american english (and more) 2) "," in germany (and some others). unfortunately OOo writes the numbers in the same way as they are represented in the cells, but it would be much better if they are ALWAYS stored with a "." floating-point-number-seperation. to clarify: (integer-part).(floating-part) [e.g. 23.233] and not: (IP),(FP) [e.g. 23,233] in german. at least, there should be an option in the import/export menu to choose, which of the both possibilites are currently the case! the other way round, somebody had the problem to import a script-generated CSV file into calc. in that file floating-point-numbers have a "."-comma but in a german environment they are interpreted as dates. and there was no way for him to workaround this. this is indeed not acceptable for an average user. so, here is my beg: let OOo Calc write floating-point-numbers into a CSV only with a "." comma (i mean the comma in the number, NOT the comma as column-separator), and don't let this be depending of the language. again, an option in the export-dialog for this would be nice. and the other way round, tell the import-filter that a "."-comma in a floating-point-number is always a number and and has to be rewritten with a ","-comma in a german (or other) environment. i hope this is possible! very short example: cell a1 has german-number-format, cell b1 has english-number-format ___|__A1___|___B1___| 1 | 2,3 | 4.55 | save as CSV: ---begin--- 2,3,4.55 ---end----- importing impossible (and this is independent of the seperator-sign !) so it should be: ---begin--- 2.3,4.55 ---end----- but imported in german environment results in dates instead of floating-point-numbers. something to choose the floating-point-comma-sign ("2.4" or "4,55") would be nice!
changed to enhancement, because an additional option in the export-dialog (and also import perhaps?) to choose the floating-point-comma-sign ("2.4" or "4,55") for numbers would be nice!
Hi Harald, such a feature is still implemented. If you open an csv file you get the CSV Import dialog, here you can choose the column type. Possible settings are Standard, Text, Date(DMY), Date(MDY), Date(YMD), US English, Hide. So I have to close it again. Best regards Frank
er->fst: You didn't get the point. It's not a matter of import but of export instead. Export dialog should have an additional entry where the decimal separator to use can be specified, maybe defaulted to "." but anyways with an additional option to leave all separators untouched and write like defined by used number formats.
taking ownership
accepted
Target to OOo 2.0
Bettina, As nowadays all RFEs involving UI and the like go through the "User Experience" hands I reassign this task to you.
I think that if the decimal separator is the same as the csv delimiter, which is a standard case e.g. in Germany, values containing decimals should be enclosed with quotes during csv export. Example: product = "apple" price = 10,50 EUR quantiy = 30 Current behaviour as of 1.1RC4: product,price,quantity "apple",10,5,30 --> wrong import in the target application: quantiy = 5!!! Desired behaviour: product,price,quantity "apple","10,5",30 Now the importing application is able to parse the line correctly.
Enclosing those values in double quotes would solve the direct problem, true. However, it would be desirable to give the user the choice what separators should be used, and whether number formats should be exported as displayed, which may lead to unparsable numbers, or raw numbers should be written instead.
Summary: Export dialog should have an additional entry where the decimal separator to use can be specified, maybe defaulted to "." but anyways with an additional option to leave all separators untouched and write like defined by used number formats. Hello Niklas, Eike offered ownership on this UI issue. As you are the project lead, you get this RFE for consideration for OO.o 2.0. I think we should take that one for OO.o 2.0 (Q).
See also issue 4925 that discusses CSV export options too. Additionally, issue 13331 is closely related because of the double quotes thingy. I don't see the need for an export option for this very problem of separators here, instead the common CSV file format handling should be applied correctly, which means to put quotes around any value that contains the field separator. However, Calc must then also be able to read those values again, which it currently doesn't because it erroneously assumes that anything in quotes is a string, which is what issue 13331 is about. I would not go for additional export options including UI changes, as we simply wouldn't have time to implement that for OOo2.0. Instead, leave the export option changes to issue 4925. I'm taking this issue as a complement of 13331 for the "export values that contain the field separator delimited with double quotes" solution.
Accepted.
Raise prio to P3 (only for statistical reasons to indicate that this really should go into OOo2.0).
Actually this is a defect, even if the result will be an enhancement.
On branch cws_src680_csvio: sc/source/ui/docshell/docsh.cxx 1.72.22.2
Reopen to reassign.
Reassign to QA.
Restore status.
Found fixed on Solaris, Linux and Windows using CWS cvsIO
Found fixed on master src680 m62 using Solaris, Linux and Windows build
*** Issue 12366 has been marked as a duplicate of this issue. ***