3 ms·

9. Excel hates CSV It clearly means CSV must be doing something right. This is one area where LibreOffice Calc shines in comparison to Excel. Importing CSVs

by Qem 2y ago

9. Excel hates CSV It clearly means CSV must be doing something right. This is one area where LibreOffice Calc shines in comparison to Excel. Importing CSVs is much more convenient.

trzeci 2y ago
I don't get it - why the world, Excel can't just open the CSV, assume from the extension it's COMMA separated value and do the rest. It does work slightly better when importing, just a little.
- TuringTest 2y ago
  It could, but it doesn't want to. The whole MS Office dominance came into being by making sure other tools can't properly open documents created by MS tools; plus being able to open standard formats but creating small incompatibilities all around, so that you share the document in MS format instead.
- Qem 2y ago
  Probably Microsoft treats a pure-text, simply specified, human-readable and editable spreadsheet format that fosters interoperability with competing software as an existential threat.
- boricj 2y ago
  Your comma isn't my comma. French systems use the comma as a decimal point for numbers and we use semicolons to separate fields in CSV files.
  - mort96 2y ago
    No, french systems also use comma to separate fields in CSV files. Excel uses semicolon to separate fields in France, meaning it generates semicolon-separated files rather than comma-separated files. It's not the fault of CSV that Excel changes which file format it uses based on locale.
    - boricj 2y ago
      It's even worse than that. Office on my work computer is set to the English language, but my locale is French and so is my Windows language. It's saving semicolon-separated CSV files with the comma as a decimal point. I need to uncheck File > Option Advanced > Use system separators and set the decimal separator to a dot to get Excel to generate English-style CSV files with semicolon-separated values. I can't be bothered to find out where Microsoft moved the CSV export dialog again in the latest version of Office to get it to spit out comma-separated fields. Point is, CSV is a term for a bunch of loosely-related formats that depends among other things on the locale. In other words, it's a mess. Any sane file format either mandates a canonical textual representation for numbers independent of locale (like JSON) or uses binary (like BSON).
      - mort96 2y ago
        > It's saving semicolon-separated CSV files with the comma as a decimal point. It's not though, is what I'm saying. It's saving semicolon-separated files, not CSV files. CSV files have commas separating the values. Saying that Excel saves "semicolon-separated CSV files" is nonsensical. I can save binary data in a .txt file, that doesn't make it a "text file with binary data"; it's a binary file with a stupid name.
        oezi 2y ago
        Sorry, but what Excel does is save to a file with a CSV extension. This format is well defined and includes ways to specify encoding and separator to be readable under different locales. This format is not comma separated values. But Excel calls it CSV. The headaches comes if people assume that a csv file must be comma separated.
        mort96 2y ago
        I don't care what Excel calls it. As I said, if I name a file .txt but stuff it with binary data, it's not a text file.
    - orwin 2y ago
  - criddell 2y ago
    Most of the people most of the time aren't importing data from a different locale. A good assumption for defaults could be that the CSV file honors the current Windows regional settings.
    - anilakar 2y ago
      If it only was that easy. Experience has shown that the only reliable way is to run heuristics against the first few lines of the file. There are office programs that save CSV with the proper comma delimiter regardless of the locale. There are people who run non-local locales for various good reasons. There are technically savvy people who have to deal with CSV shenanigans and can and will send it with the proper comma delimiter.
gibibit 2y ago
Excel won't import ISO 8601 timestamps either, which is crazy these days where it's the universal standard, and there's no excuse to use anything else. You have to replace the "T" separator with a space and also any trailing "Z" UTC suffix (and I think any other timezone/offset as well?) for Excel to be able to parse as a time/date.
- macintux 2y ago
  Honestly I’m happier when Excel doesn’t try to convert anything. too much bugginess.
  - inglor_cz 2y ago
    Especially gene names. It was so bad that the scientific community renamed the genes in question rather than suffering from the same horror endlessly. [0] https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
    - gibibit 2y ago
      Just wrong!!
- TrackerFF 2y ago
  Have you tried using the "from text/csv" importer under the data tab? Where it will import your data into a table. Because that one will import ISO 8601 timestamps just fine.
  - Suppafly 2y ago
    This, it's dumb but Excel handles csv way better if you 'import' it vs just opening it. I use excel to quickly preview csv files, but never to edit them unless I'm OK only ever using it in Excel afterwards.
    - 2y ago
Night_Thastus 2y ago
I just wish Excel was a little less bad about copy-pasting CSVs as well. Every single time, without fail, it dumps them into a single column. Every single time I use "text to columns" it has insane defaults where it's fixed-width instead of delimited by, you know, commas. So I change that and finally it's fixed. Then I go do it somewhere else and have to set it up all over again. Drives me nuts. How the default behavior isn't to just put them in the way you'd expect is mind-boggling.
nly 2y ago
Search and replace + text to columns after the fact works fine.
jaza 2y ago
This. I got burnt by the encoding and other issues with CSV in Excel back in the day, I've only used LibreOffice Calc (on Linux) for viewing / editing CSVs for many years now, it's almost always a trouble-free experience. Fortunately I don't deal much with CSVs that Excel-wielding non-devs also need to open these days - I assume that, for most folks, that's the source of most of their CSV woes.

by Qem 2y ago

9. Excel hates CSV It clearly means CSV must be doing something right. This is one area where LibreOffice Calc shines in comparison to Excel. Importing CSVs is much more convenient.

trzeci 2y ago

I don't get it - why the world, Excel can't just open the CSV, assume from the extension it's COMMA separated value and do the rest. It does work slightly better when importing, just a little.

TuringTest 2y ago
It could, but it doesn't want to. The whole MS Office dominance came into being by making sure other tools can't properly open documents created by MS tools; plus being able to open standard formats but creating small incompatibilities all around, so that you share the document in MS format instead.
Qem 2y ago
Probably Microsoft treats a pure-text, simply specified, human-readable and editable spreadsheet format that fosters interoperability with competing software as an existential threat.
boricj 2y ago
Your comma isn't my comma. French systems use the comma as a decimal point for numbers and we use semicolons to separate fields in CSV files.
- mort96 2y ago
  No, french systems also use comma to separate fields in CSV files. Excel uses semicolon to separate fields in France, meaning it generates semicolon-separated files rather than comma-separated files. It's not the fault of CSV that Excel changes which file format it uses based on locale.
  - boricj 2y ago
    It's even worse than that. Office on my work computer is set to the English language, but my locale is French and so is my Windows language. It's saving semicolon-separated CSV files with the comma as a decimal point. I need to uncheck File > Option Advanced > Use system separators and set the decimal separator to a dot to get Excel to generate English-style CSV files with semicolon-separated values. I can't be bothered to find out where Microsoft moved the CSV export dialog again in the latest version of Office to get it to spit out comma-separated fields. Point is, CSV is a term for a bunch of loosely-related formats that depends among other things on the locale. In other words, it's a mess. Any sane file format either mandates a canonical textual representation for numbers independent of locale (like JSON) or uses binary (like BSON).
    - mort96 2y ago
      > It's saving semicolon-separated CSV files with the comma as a decimal point. It's not though, is what I'm saying. It's saving semicolon-separated files, not CSV files. CSV files have commas separating the values. Saying that Excel saves "semicolon-separated CSV files" is nonsensical. I can save binary data in a .txt file, that doesn't make it a "text file with binary data"; it's a binary file with a stupid name.
      - oezi 2y ago
        Sorry, but what Excel does is save to a file with a CSV extension. This format is well defined and includes ways to specify encoding and separator to be readable under different locales. This format is not comma separated values. But Excel calls it CSV. The headaches comes if people assume that a csv file must be comma separated.
        mort96 2y ago
        I don't care what Excel calls it. As I said, if I name a file .txt but stuff it with binary data, it's not a text file.
  - orwin 2y ago
- criddell 2y ago
  Most of the people most of the time aren't importing data from a different locale. A good assumption for defaults could be that the CSV file honors the current Windows regional settings.
  - anilakar 2y ago
    If it only was that easy. Experience has shown that the only reliable way is to run heuristics against the first few lines of the file. There are office programs that save CSV with the proper comma delimiter regardless of the locale. There are people who run non-local locales for various good reasons. There are technically savvy people who have to deal with CSV shenanigans and can and will send it with the proper comma delimiter.

gibibit 2y ago

Excel won't import ISO 8601 timestamps either, which is crazy these days where it's the universal standard, and there's no excuse to use anything else. You have to replace the "T" separator with a space and also any trailing "Z" UTC suffix (and I think any other timezone/offset as well?) for Excel to be able to parse as a time/date.

macintux 2y ago
Honestly I’m happier when Excel doesn’t try to convert anything. too much bugginess.
- inglor_cz 2y ago
  Especially gene names. It was so bad that the scientific community renamed the genes in question rather than suffering from the same horror endlessly. [0] https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
  - gibibit 2y ago
    Just wrong!!
TrackerFF 2y ago
Have you tried using the "from text/csv" importer under the data tab? Where it will import your data into a table. Because that one will import ISO 8601 timestamps just fine.
- Suppafly 2y ago
  This, it's dumb but Excel handles csv way better if you 'import' it vs just opening it. I use excel to quickly preview csv files, but never to edit them unless I'm OK only ever using it in Excel afterwards.
  - 2y ago

Night_Thastus 2y ago

I just wish Excel was a little less bad about copy-pasting CSVs as well. Every single time, without fail, it dumps them into a single column. Every single time I use "text to columns" it has insane defaults where it's fixed-width instead of delimited by, you know, commas. So I change that and finally it's fixed. Then I go do it somewhere else and have to set it up all over again. Drives me nuts. How the default behavior isn't to just put them in the way you'd expect is mind-boggling.

nly 2y ago

Search and replace + text to columns after the fact works fine.

jaza 2y ago

This. I got burnt by the encoding and other issues with CSV in Excel back in the day, I've only used LibreOffice Calc (on Linux) for viewing / editing CSVs for many years now, it's almost always a trouble-free experience. Fortunately I don't deal much with CSVs that Excel-wielding non-devs also need to open these days - I assume that, for most folks, that's the source of most of their CSV woes.