4 ms·

Excel is quite often useless with CSV, even if its contents are pure ASCII. Whether or not Excel will open a standard CSV file delimited with comma's (as per RF

Excel is quite often useless with CSV, even if its contents are pure ASCII. Whether or not Excel will open a standard CSV file delimited with comma's (as per RFC 4180) properly also depends on the locale of the computer running Excel. So if you run Excel on Windows with a Dutch locale, it will by default fail to open the CSV normally and jam all its contents in one cell, because it expects a semi-colon as delimiter instead of a comma. To open standard CSV you have to import the file instead. Someone at some point decided that the Dutch use semi-colons instead of commas to delimit tabulated data, and decided to apply this logic to CSV files as well. I would like to know the history behind such a decision! Switch your locale to English US, and it opens normally. LibreOffice just opens the CSV and asks the user to confirm the delimiter and what not regardless of locale.

pieter_mj 10y ago
Importing csv is very messy in excel. A VBA import routine will always run in the US locale. However, csv files containing dates formatted according to other locales will have their days and months switched as long as the day is not bigger than 12.
jsproc 10y ago
Yes, this is so horrible. As a workaround I sometimes put "sep=," on the first line of the spreadsheet. This forces Excel to use the comma as a separator, regardless of the locale. The downside of course is that every other tool on the planet shows the extra row, so it's not very useful.
- ptman 10y ago
  I suffer from the reverse problem. I need to ingest CSV files produced by excel in various locales. Or is there some generally available alternative to excel that would produce valid RFC CSVs?
  - Piskvorrr 10y ago
    There is no such thing as a valid CSV - even the spec is borked, and nobody follows it anyway. If you have a choice, XLSX is a lesser evil.
    - Freak_NL 10y ago
      RFC 4180¹ has been in existence since 2005 and seems perfectly straight forward. The basic syntax is two pages of very understandable text. I hold that to be the definition of valid CSV. CSV libraries tend to adhere to it (and often support additional options encountered in the wild as well); e.g., Apache Commons CSV². 1: https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 2: https://commons.apache.org/proper/commons-csv/ https://commons.apache.org/proper/commons-csv/
      - supergreg 10y ago
        Is there a way to report a bug to excel asking for proper RFC 4180 support?
        Freak_NL 10y ago
        Probably, but what will it achieve realistically? This (both what OP reports and this locale-dependent behaviour) is likely a WONTFIX type of bug, as there are workarounds (don't double-click to open normal CSV if you are in a locale where different delimiters are used, use import; don't start your CSV files with "ID"; etc.).
      - Piskvorrr 10y ago
        Seems is the correct word here. Alas, it's one of multiple attempts at standardization ("I hold it" - good thing about standards is there's so many to choose from, as this RFC acknowledges), ignores charset altogether (a flaw of CSV, not of standard), and anything touched by MSO is unlikely to fit this. So, while useful for emitting CSV, no use at all for processing.
viraptor 10y ago
Wait... which excel? 2013 definitely gives me a choice of separators when importing.
- Freak_NL 10y ago
  > when importing. Exactly. Unfortunately, double-clicking the file does not trigger that dialogue. You would be surprised how many IT-staffers simply give up at that point („Can I get the raw data?”, „Sure, here is the automatically generated CSV dump.”, „I can't open it…”).
  - jandrese 10y ago
    I can't remember the last time double clicking on a CSV file did the right thing in Excel. I always use the clunky import dialog. Even then it often requires a few tries before I get what I want. For an operation that is done literally millions of times around the world each year, Microsoft seems to pretend that it's some bizarre corner case. I know CSV is a messy format, but you can do better Microsoft. Even a week or two of work on that could make it much much better.

pieter_mj 10y ago

Importing csv is very messy in excel. A VBA import routine will always run in the US locale. However, csv files containing dates formatted according to other locales will have their days and months switched as long as the day is not bigger than 12.

jsproc 10y ago

Yes, this is so horrible. As a workaround I sometimes put "sep=," on the first line of the spreadsheet. This forces Excel to use the comma as a separator, regardless of the locale. The downside of course is that every other tool on the planet shows the extra row, so it's not very useful.

ptman 10y ago
I suffer from the reverse problem. I need to ingest CSV files produced by excel in various locales. Or is there some generally available alternative to excel that would produce valid RFC CSVs?
- Piskvorrr 10y ago
  There is no such thing as a valid CSV - even the spec is borked, and nobody follows it anyway. If you have a choice, XLSX is a lesser evil.
  - Freak_NL 10y ago
    RFC 4180¹ has been in existence since 2005 and seems perfectly straight forward. The basic syntax is two pages of very understandable text. I hold that to be the definition of valid CSV. CSV libraries tend to adhere to it (and often support additional options encountered in the wild as well); e.g., Apache Commons CSV². 1: https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 2: https://commons.apache.org/proper/commons-csv/ https://commons.apache.org/proper/commons-csv/
    - supergreg 10y ago
      Is there a way to report a bug to excel asking for proper RFC 4180 support?
      - Freak_NL 10y ago
        Probably, but what will it achieve realistically? This (both what OP reports and this locale-dependent behaviour) is likely a WONTFIX type of bug, as there are workarounds (don't double-click to open normal CSV if you are in a locale where different delimiters are used, use import; don't start your CSV files with "ID"; etc.).
    - Piskvorrr 10y ago
      Seems is the correct word here. Alas, it's one of multiple attempts at standardization ("I hold it" - good thing about standards is there's so many to choose from, as this RFC acknowledges), ignores charset altogether (a flaw of CSV, not of standard), and anything touched by MSO is unlikely to fit this. So, while useful for emitting CSV, no use at all for processing.

viraptor 10y ago

Wait... which excel? 2013 definitely gives me a choice of separators when importing.

Freak_NL 10y ago
> when importing. Exactly. Unfortunately, double-clicking the file does not trigger that dialogue. You would be surprised how many IT-staffers simply give up at that point („Can I get the raw data?”, „Sure, here is the automatically generated CSV dump.”, „I can't open it…”).
- jandrese 10y ago
  I can't remember the last time double clicking on a CSV file did the right thing in Excel. I always use the clunky import dialog. Even then it often requires a few tries before I get what I want. For an operation that is done literally millions of times around the world each year, Microsoft seems to pretend that it's some bizarre corner case. I know CSV is a messy format, but you can do better Microsoft. Even a week or two of work on that could make it much much better.