4 ms·

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that

by GuB-42 3y ago

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same language. If you are unlucky, it doesn't, but you can still convert it. If you are really unlucky, then you get commas for both decimal numbers and separators, making the file completely unusable. There is a CSV standard, RFC 4180, but no one seems to care.

Gabriel54 3y ago
If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).
- zztop44 3y ago
  You are mistaken. Probably more countries overall use a decimal comma, but the decimal point is used as convention in many countries, including China, India, Nigeria and the Philippines.
- sfRattan 3y ago
  Going by the Wikipedia article and included map, use of comma versus period as decimal separators is roughly an even split: https://en.wikipedia.org/wiki/Decimal_separator https://en.wikipedia.org/wiki/Decimal_separator https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg
  - fshr 3y ago
    There's definitely a big distribution disparity. 11 of the 15 most populous countries use the period for decimals.
    - micheljansen 3y ago
      Most of Europe is in the fat long tail though, as those countries are counted individually.
  - Larrikin 3y ago
    Seems geographically split, but I wonder what is the actual population split is. Most of the top 10 population countries use the decimal separator. Only Brazil, Russia and Indonesia don't. Maybe someone with a CSV of the world populations and a CSV of the countries broken down by their separator can do that comparison.
layer8 3y ago
There are tools to convert between the formats. Either you have a defined data pipeline where you know what you get at each step and apply the necessary transformations. Or you get random files and, yes, have to inspect them and see how to convert them if necessary. It’s unfortunate that there isn’t a single CSV format, but for historical reasons it is what it is. It’s effectively more like a family of formats that share the same file extension. Excel actually has a convention where it understands when there is a line sep=; at the start of the file. By the way, in addition to differing separators, you can also get different character encodings. Excel does understand a BOM to indicate UTF-8, but some versions of Excel unfortunately ignore it when the “sep=“ line is present…
- eyegor 3y ago
  What kind of voodoo is this? I've always wanted something like this for non technical coworkers across countries but I didn't know it existed. I always just exported TSV and provided steps for how to import it (although I think most excel versions have native tsv support).
- al_borland 3y ago
  >sep=; Thank you! This is a game changer. As I was reading through these comments I was thinking how much better it would be if the separator could be specified in the file, but it would only be useful for my own stuff, if I was to do that on my own. I’ll be trying this first thing tomorrow at work. I don’t do as much with CSVs as I used to, but am currently working with them a lot, and have a single entry that always throws the resulting Excel file off, and I can’t be bothered to figure out how to escape it (I spent too much time in the past messing with that and got nowhere). This sep= lines will hopefully solve my issues.
  - heresie-dabord 3y ago
    I have sometimes distributed a CSV file with a similarly-named text file that contains key-value pairs to aid in the use of the CSV file. A minimal text file of this type would contain: #key=value sep=;
gerdesj 3y ago
If only a totally separate data field separator character had been invented early on and been given its own key on the keyboard, coloured: "only use for field delimiting". You know as well as I do that it would have been co-opted into another rôle within weeks, probably as a currency indicator. You should probably use PSV - Point Separated Variable! Obviously we would need to adjust PSV to account for correct French word order (and actually use French correctly). Britain and USA would use something called PVS2 instead as a data interchange format with France which involves variable @@@ symbols as delimiters, unless it is a Friday which is undefined. The rest of the world would roll its eyes and use PSV with varying success. A few years later France would announce VSP on the back page of Le Monde, enshrine its use in law (in Aquitaine, during the fifteenth century) but not actually release the standard for fifteen years. The world is odd. We have to work within an odd world. Interestingly enough, you and I could look at a CSV encoded data set with commas as decimal separators and work out what is going on. You'll need a better parser!
- lolive 3y ago
  Don’t look at the ASCII table, at entries 28 to 31. You are not mentally ready for what you will find there.
  - junto 3y ago
    > ..what you will find there Made me laugh, especially since you can’t “see” them. In fact, that was maybe an oversight when ASCII was designed, but maybe there was a reason for that. If they were visible, and were actually recognizable as separator types then people would know them better. https://www.lammertbies.nl/comm/info/ascii-characters https://www.lammertbies.nl/comm/info/ascii-characters
- frob 3y ago
  My company has been using DSV for a bit: Dagger Separated Values. Unicode dagger (†) to separate values and double-dagger (‡) to indicate end of row. It allows us to easily handle both commas and newlines.
msla 3y ago
> In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator Heh. Ah, HN. Always good for a laugh line.
- samatman 3y ago
  Yeah, hon hon hon and all, but one of my (US) bank statements exports a CSV which uses commas in numbers in the US fashion, so $1,500 and the like. Writing a custom CSV munger to intake that into ledger-csv was... fun, but then again, only had to do it once.
  - stephenr 3y ago
    Surely if they're putting commas in values they were quoting the values though?
    - samatman 3y ago
      They were not. Fortunately the values are always prefixed by a dollar sign, making parsing deterministic, though ugly.
  - deleted 3y ago
    [deleted]
  - msla 3y ago
    That's exactly my point, and I still got downvoted. This place...
echoangle 3y ago
Does any tool seriously localize CSVs?
- elric 3y ago
  Any serious CSV tool has the option to pick a delimiter. Usually semicolon or comma, some offer additional options. The only impact it has is on which fields need quoting. When using comma, all decimals in many langugaes need to be quoted. When using semicolon those don't need to be quoted. Overall, semicolon feels like the superior delimiter. Most sensible people don't export formatted numbers (e.g. 100.000,00), but even those are pretty trivial to import.
- nlitened 3y ago
  So many tools do that, even (most importantly) Excel does
stephenr 3y ago
Isn't this exactly what quoting solves? i.e.: ``` "1,20","2,3",hello "2,40","4,6",goodbye ``` If your tool reads CSV by doing `string_split(',', line);`, your tool is doing it wrong. There's a bunch of nuance and shit, which can make CSVs interesting to work with, but storing a comma in a field is a pretty solved issue if the tool in question has more than 5 minutes thought put into it.
- ric2b 3y ago
  Now all your numbers are strings
  - elric 3y ago
    It's a text file. All your numbers were already strings. Nothing has changed.
    - ranguna 3y ago
      There's a difference between "1" and 1. When you import a csv and try to do maths on a "number" you won't get the expected result. Some importers won't even allow you to specify that "number" columns are numbers, they'll outright fail and force you to say it's a string, or you'll have to specify which columns are "numbers" and map the strings to numbers on the importer side. If they are numbers to begin with (not "numbers"), you can just import the csv and you'll get the expected result out of the box. In the end things are just 1s and 0s, but that doesn't mean we only ever do binary operations on data at the abstraction layer we humans operate, so saying it's just 0s and 1s or just strings is not very smart.
      - elric 3y ago
        Sounds to me like you're using a shitty parser. CSV is schemaless. It is up to you to tell the parser what types to use if it isn't unambiguous. Quoted values can be numbers, and unquoted values can be strings. I have not used any CSV tools that don't support this behaviour.
kmmlng 3y ago
In my experience, this is only a problem when you are using Excel. It's ridiculous how bad Excel is at handling CSVs, I really cannot comprehend it. If you use LibreOffice all your problems magically disappear.
- henriquecm8 3y ago
  I find out that Excel follows windows locale, you can change the decimal and the thousand separator on windows, and it will affect how excel exports and reads CSVs.
junto 3y ago
Same in German. We have things like 1.500.021,92 (I.e. 1,500,021.92)

by GuB-42 3y ago

Gabriel54 3y ago

If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).

zztop44 3y ago
You are mistaken. Probably more countries overall use a decimal comma, but the decimal point is used as convention in many countries, including China, India, Nigeria and the Philippines.
sfRattan 3y ago
Going by the Wikipedia article and included map, use of comma versus period as decimal separators is roughly an even split: https://en.wikipedia.org/wiki/Decimal_separator https://en.wikipedia.org/wiki/Decimal_separator https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg
- fshr 3y ago
  There's definitely a big distribution disparity. 11 of the 15 most populous countries use the period for decimals.
  - micheljansen 3y ago
    Most of Europe is in the fat long tail though, as those countries are counted individually.
- Larrikin 3y ago
  Seems geographically split, but I wonder what is the actual population split is. Most of the top 10 population countries use the decimal separator. Only Brazil, Russia and Indonesia don't. Maybe someone with a CSV of the world populations and a CSV of the countries broken down by their separator can do that comparison.

layer8 3y ago

There are tools to convert between the formats. Either you have a defined data pipeline where you know what you get at each step and apply the necessary transformations. Or you get random files and, yes, have to inspect them and see how to convert them if necessary. It’s unfortunate that there isn’t a single CSV format, but for historical reasons it is what it is. It’s effectively more like a family of formats that share the same file extension. Excel actually has a convention where it understands when there is a line sep=; at the start of the file. By the way, in addition to differing separators, you can also get different character encodings. Excel does understand a BOM to indicate UTF-8, but some versions of Excel unfortunately ignore it when the “sep=“ line is present…

eyegor 3y ago
What kind of voodoo is this? I've always wanted something like this for non technical coworkers across countries but I didn't know it existed. I always just exported TSV and provided steps for how to import it (although I think most excel versions have native tsv support).
al_borland 3y ago
>sep=; Thank you! This is a game changer. As I was reading through these comments I was thinking how much better it would be if the separator could be specified in the file, but it would only be useful for my own stuff, if I was to do that on my own. I’ll be trying this first thing tomorrow at work. I don’t do as much with CSVs as I used to, but am currently working with them a lot, and have a single entry that always throws the resulting Excel file off, and I can’t be bothered to figure out how to escape it (I spent too much time in the past messing with that and got nowhere). This sep= lines will hopefully solve my issues.
- heresie-dabord 3y ago
  I have sometimes distributed a CSV file with a similarly-named text file that contains key-value pairs to aid in the use of the CSV file. A minimal text file of this type would contain: #key=value sep=;

gerdesj 3y ago

If only a totally separate data field separator character had been invented early on and been given its own key on the keyboard, coloured: "only use for field delimiting". You know as well as I do that it would have been co-opted into another rôle within weeks, probably as a currency indicator. You should probably use PSV - Point Separated Variable! Obviously we would need to adjust PSV to account for correct French word order (and actually use French correctly). Britain and USA would use something called PVS2 instead as a data interchange format with France which involves variable @@@ symbols as delimiters, unless it is a Friday which is undefined. The rest of the world would roll its eyes and use PSV with varying success. A few years later France would announce VSP on the back page of Le Monde, enshrine its use in law (in Aquitaine, during the fifteenth century) but not actually release the standard for fifteen years. The world is odd. We have to work within an odd world. Interestingly enough, you and I could look at a CSV encoded data set with commas as decimal separators and work out what is going on. You'll need a better parser!

lolive 3y ago
Don’t look at the ASCII table, at entries 28 to 31. You are not mentally ready for what you will find there.
- junto 3y ago
  > ..what you will find there Made me laugh, especially since you can’t “see” them. In fact, that was maybe an oversight when ASCII was designed, but maybe there was a reason for that. If they were visible, and were actually recognizable as separator types then people would know them better. https://www.lammertbies.nl/comm/info/ascii-characters https://www.lammertbies.nl/comm/info/ascii-characters
frob 3y ago
My company has been using DSV for a bit: Dagger Separated Values. Unicode dagger (†) to separate values and double-dagger (‡) to indicate end of row. It allows us to easily handle both commas and newlines.

msla 3y ago

> In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator Heh. Ah, HN. Always good for a laugh line.

samatman 3y ago
Yeah, hon hon hon and all, but one of my (US) bank statements exports a CSV which uses commas in numbers in the US fashion, so $1,500 and the like. Writing a custom CSV munger to intake that into ledger-csv was... fun, but then again, only had to do it once.
- stephenr 3y ago
  Surely if they're putting commas in values they were quoting the values though?
  - samatman 3y ago
    They were not. Fortunately the values are always prefixed by a dollar sign, making parsing deterministic, though ugly.
- deleted 3y ago
  [deleted]
- msla 3y ago
  That's exactly my point, and I still got downvoted. This place...

echoangle 3y ago

Does any tool seriously localize CSVs?

elric 3y ago
Any serious CSV tool has the option to pick a delimiter. Usually semicolon or comma, some offer additional options. The only impact it has is on which fields need quoting. When using comma, all decimals in many langugaes need to be quoted. When using semicolon those don't need to be quoted. Overall, semicolon feels like the superior delimiter. Most sensible people don't export formatted numbers (e.g. 100.000,00), but even those are pretty trivial to import.
nlitened 3y ago
So many tools do that, even (most importantly) Excel does

stephenr 3y ago

Isn't this exactly what quoting solves? i.e.: ``` "1,20","2,3",hello "2,40","4,6",goodbye ``` If your tool reads CSV by doing `string_split(',', line);`, your tool is doing it wrong. There's a bunch of nuance and shit, which can make CSVs interesting to work with, but storing a comma in a field is a pretty solved issue if the tool in question has more than 5 minutes thought put into it.

ric2b 3y ago
Now all your numbers are strings
- elric 3y ago
  It's a text file. All your numbers were already strings. Nothing has changed.
  - ranguna 3y ago
    There's a difference between "1" and 1. When you import a csv and try to do maths on a "number" you won't get the expected result. Some importers won't even allow you to specify that "number" columns are numbers, they'll outright fail and force you to say it's a string, or you'll have to specify which columns are "numbers" and map the strings to numbers on the importer side. If they are numbers to begin with (not "numbers"), you can just import the csv and you'll get the expected result out of the box. In the end things are just 1s and 0s, but that doesn't mean we only ever do binary operations on data at the abstraction layer we humans operate, so saying it's just 0s and 1s or just strings is not very smart.
    - elric 3y ago
      Sounds to me like you're using a shitty parser. CSV is schemaless. It is up to you to tell the parser what types to use if it isn't unambiguous. Quoted values can be numbers, and unquoted values can be strings. I have not used any CSV tools that don't support this behaviour.

kmmlng 3y ago

In my experience, this is only a problem when you are using Excel. It's ridiculous how bad Excel is at handling CSVs, I really cannot comprehend it. If you use LibreOffice all your problems magically disappear.

henriquecm8 3y ago
I find out that Excel follows windows locale, you can change the decimal and the thousand separator on windows, and it will affect how excel exports and reads CSVs.

junto 3y ago

Same in German. We have things like 1.500.021,92 (I.e. 1,500,021.92)