6 ms·

Off topic rant I need to get off my chest. Yesterday I troubleshooted a bizarre problem. New versions of Excel don't necessarily understand the simplest CSV fil

Off topic rant I need to get off my chest. Yesterday I troubleshooted a bizarre problem. New versions of Excel don't necessarily understand the simplest CSV files. Reason: They forgot that the C in CSV stands for comma, and instead expect whatever character happens to be set as the 'delimiter' character in the Windows regional preferences. Here in New Zealand, it seems that character is semicolon instead of comma. For some reason. I think Excel was finished circa 2000 and now they focus mainly on making it worse.

TAForObvReasons 9y ago
If you know the file is going to be used in Excel, you can force a delimiter with the `sep` directive. The first characters of the file should be sep=,<newline>
- sillysaurus3 9y ago
  Bleh. That's so Microsoft. It took roughly ten seconds to find huge problems with this approach: https://stackoverflow.com/questions/20395699/sep-statement-breaks-utf8-bom-in-csv-file-which-is-generated-by-xsl https://stackoverflow.com/questions/20395699/sep-statement-b...
  - TAForObvReasons 9y ago
    CSV is a mess. A while back we switched to exporting XLSX using the xlsx module https://www.npmjs.com/package/xlsx https://www.npmjs.com/package/xlsx
    - deleted 9y ago
      [deleted]
    - ng12 9y ago
      How can CSV be a mess? The implementation is in the name. Excel is a mess.
      - TAForObvReasons 9y ago
        The mess is in the "V". RFC4180 leaves value interpretation to the application. It doesn't tell you, for example, when a string like 12345 should be interpreted as a number. So we end up sending numbers and dates and other meaningful values into the ether, hoping that the end application understands what you meant to convey. XLSX has a cell type for IEEE754 doubles, so there is no such ambiguity. It also has a special date type!
        paulddraper 9y ago
        Protocol buffers don't tell you what the data types are either in the actual message. It's a trade-off, but I prefer to communicate format out of band, not in every single message.
        traviscj 9y ago
        My take: include a schema when you can and there's a justification and it's practical. IMO even JSON is a (very limited, "name only, no type") type of schema. If it becomes impractical for something you're doing, stop and switch to something less redundant.
        ng12 9y ago
        That's still not a failing of CSV. If knowing whether a V is an IEEE754 double is critical to your application you shouldn't be using CSV. You wouldn't say nails are a mess because sometimes you need screws.
        TAForObvReasons 9y ago
        Knowing whether a value is a number or text or date is critical to myriads of application, and CSV specs give no guidance on how to interpret the actual values as numbers or text or dates. You don't have control over both endpoints in most cases, especially when you accept files from clients. Furthermore, they mix presentation and values (is `1.23%` a string or a number? What is the number?) XLSX separates the value from presentation and gives every value a clear type. If a value is a number, there's a concrete numeric value that is stored separately from the number format. That way there is no guesswork involved, you can figure out exactly what the value is with zero magic.
    - kauegimenes 9y ago
      You can use jsonexport with rowDelimiter: '\t' for a xls compatible output.
wolco 9y ago
I'm curious why New Zealand would have a semi-colon as the default. Is there a historical or cultural reason?
- billforsternz 9y ago
  Nothing comes to mind. I think someone might have had a moment of unnecessary inspired creativity. The main point is, using this setting is a mistake for this context. Let CSV be CSV.
- bitmedley 9y ago
  I checked and New Zealand actually uses a comma as the separator, not a semi-colon. You can verify this by going to Control Panel -> Region -> Change to: English (New Zealand) -> Additional settings... -> List separator A semi-colon is generally used as the default list separator when the region/locale uses a comma as the decimal separator for numbers. For example Dutch (Netherlands) uses a comma for the decimal separator (ex. 3,14) whereas in English (US) we use a decimal point (ex 3.14). If comma were used as the default list separator in such a region then all floating point numbers would need to be quoted (ex. "3,14") which would make the size of the CSV file larger and also make the file less human-readable
  - billforsternz 9y ago
    Interesting. Certainly it was only a subset of customers who were complaining, and in the office it was a subset of Excel installations which could reproduce the problem. There's no doubt what the problem was and how to fix it. There is a doubt about exactly which Excel and Windows combinations are problematic, and how prevalent that combination is amongst New Zealand installations.
timvdalen 9y ago
Same in Holland. The 'fun' thing is that Excel for OS X does not do this, it uses commas. We used to always just generate CSV files with semicolons since most of our clients were using Dutch Excel on Windows. As some of them moved to OS X, we've mostly been guessing what format to use.
amptorn 9y ago
> I think Excel was finished circa 2000 and now they focus mainly on Not breaking established behaviour?
rplnt 9y ago
Do you directly open the file? It used to ask during import.
strictfp 9y ago
It also behaves differently if you open the csv from inside excel rather than if you doubleclick the file in explorer. And we haven't even gotten to file encodings yet.
someSven 9y ago
Just for the case, anyone doesn't know. Be carefull with opening CSVs in Excel or just don't do it. https://www.owasp.org/index.php/CSV_Excel_Macro_Injection https://www.owasp.org/index.php/CSV_Excel_Macro_Injection
- bruth 9y ago
  Yep. Just don't do it. https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
PetahNZ 9y ago
Lets not forget when you have "ID" as the first header (ie. col 1 row 1), it assumes the CSV file is SLYK format.
ignoramous 9y ago
There was an interesting article abt CSV parsing a few years ago, here's the HN discussion https://news.ycombinator.com/item?id=7796268 https://news.ycombinator.com/item?id=7796268 To quote the top most comment (by user, slg): "CSV are a headache. Like the article says, RFC4180 doesn't necessarily represent the real world. However sometimes you just have to reject things that aren't spec. Not too long ago I was struggling with one of these CSV issues and received some good advice from Hans Passant [1] on a Stack Overflow question pertaining to my problem (emphasis mine): 'It is pretty important that you don't try to fix it. That will make you responsible for bad data for a long time. Reject the file for being improperly formatted. If they hassle you about it then point out that it is not RFC-4180 compatible. There's another programmer somewhere that can easily fix this.' It makes perfect sense in hindsight. If you accept a malformed CSV file, people will expect you to accept any malformed data that has a CSV extension. You are taking on a lot of extra responsibility to cover for the lack of work by another programmer. Odds are they can make a change to fix the problem that takes a fraction of the time it would take you work around it. You just have to raise the issue. I realize that rejecting bad files isn't really possible in every circumstance. But I have a feeling it is an option more times than you might initially think."
electroly 9y ago
The secret to using CSV in Excel is to use Data > From Text instead of opening the CSV file directly. This opens the import wizard which allows you to, among other things, change the separator character. It's still not perfect, but it's much better than opening the CSV file directly.

TAForObvReasons 9y ago

If you know the file is going to be used in Excel, you can force a delimiter with the `sep` directive. The first characters of the file should be sep=,<newline>

sillysaurus3 9y ago
Bleh. That's so Microsoft. It took roughly ten seconds to find huge problems with this approach: https://stackoverflow.com/questions/20395699/sep-statement-breaks-utf8-bom-in-csv-file-which-is-generated-by-xsl https://stackoverflow.com/questions/20395699/sep-statement-b...
- TAForObvReasons 9y ago
  CSV is a mess. A while back we switched to exporting XLSX using the xlsx module https://www.npmjs.com/package/xlsx https://www.npmjs.com/package/xlsx
  - deleted 9y ago
    [deleted]
  - ng12 9y ago
    How can CSV be a mess? The implementation is in the name. Excel is a mess.
    - TAForObvReasons 9y ago
      The mess is in the "V". RFC4180 leaves value interpretation to the application. It doesn't tell you, for example, when a string like 12345 should be interpreted as a number. So we end up sending numbers and dates and other meaningful values into the ether, hoping that the end application understands what you meant to convey. XLSX has a cell type for IEEE754 doubles, so there is no such ambiguity. It also has a special date type!
      - paulddraper 9y ago
        Protocol buffers don't tell you what the data types are either in the actual message. It's a trade-off, but I prefer to communicate format out of band, not in every single message.
        traviscj 9y ago
        My take: include a schema when you can and there's a justification and it's practical. IMO even JSON is a (very limited, "name only, no type") type of schema. If it becomes impractical for something you're doing, stop and switch to something less redundant.
      - ng12 9y ago
        That's still not a failing of CSV. If knowing whether a V is an IEEE754 double is critical to your application you shouldn't be using CSV. You wouldn't say nails are a mess because sometimes you need screws.
        TAForObvReasons 9y ago
        Knowing whether a value is a number or text or date is critical to myriads of application, and CSV specs give no guidance on how to interpret the actual values as numbers or text or dates. You don't have control over both endpoints in most cases, especially when you accept files from clients. Furthermore, they mix presentation and values (is `1.23%` a string or a number? What is the number?) XLSX separates the value from presentation and gives every value a clear type. If a value is a number, there's a concrete numeric value that is stored separately from the number format. That way there is no guesswork involved, you can figure out exactly what the value is with zero magic.
  - kauegimenes 9y ago
    You can use jsonexport with rowDelimiter: '\t' for a xls compatible output.

wolco 9y ago

I'm curious why New Zealand would have a semi-colon as the default. Is there a historical or cultural reason?

billforsternz 9y ago
Nothing comes to mind. I think someone might have had a moment of unnecessary inspired creativity. The main point is, using this setting is a mistake for this context. Let CSV be CSV.
bitmedley 9y ago
I checked and New Zealand actually uses a comma as the separator, not a semi-colon. You can verify this by going to Control Panel -> Region -> Change to: English (New Zealand) -> Additional settings... -> List separator A semi-colon is generally used as the default list separator when the region/locale uses a comma as the decimal separator for numbers. For example Dutch (Netherlands) uses a comma for the decimal separator (ex. 3,14) whereas in English (US) we use a decimal point (ex 3.14). If comma were used as the default list separator in such a region then all floating point numbers would need to be quoted (ex. "3,14") which would make the size of the CSV file larger and also make the file less human-readable
- billforsternz 9y ago
  Interesting. Certainly it was only a subset of customers who were complaining, and in the office it was a subset of Excel installations which could reproduce the problem. There's no doubt what the problem was and how to fix it. There is a doubt about exactly which Excel and Windows combinations are problematic, and how prevalent that combination is amongst New Zealand installations.

timvdalen 9y ago

Same in Holland. The 'fun' thing is that Excel for OS X does not do this, it uses commas. We used to always just generate CSV files with semicolons since most of our clients were using Dutch Excel on Windows. As some of them moved to OS X, we've mostly been guessing what format to use.

amptorn 9y ago

> I think Excel was finished circa 2000 and now they focus mainly on Not breaking established behaviour?

rplnt 9y ago

Do you directly open the file? It used to ask during import.

strictfp 9y ago

It also behaves differently if you open the csv from inside excel rather than if you doubleclick the file in explorer. And we haven't even gotten to file encodings yet.

someSven 9y ago

Just for the case, anyone doesn't know. Be carefull with opening CSVs in Excel or just don't do it. https://www.owasp.org/index.php/CSV_Excel_Macro_Injection https://www.owasp.org/index.php/CSV_Excel_Macro_Injection

bruth 9y ago
Yep. Just don't do it. https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...

PetahNZ 9y ago

Lets not forget when you have "ID" as the first header (ie. col 1 row 1), it assumes the CSV file is SLYK format.

ignoramous 9y ago

There was an interesting article abt CSV parsing a few years ago, here's the HN discussion https://news.ycombinator.com/item?id=7796268 https://news.ycombinator.com/item?id=7796268 To quote the top most comment (by user, slg): "CSV are a headache. Like the article says, RFC4180 doesn't necessarily represent the real world. However sometimes you just have to reject things that aren't spec. Not too long ago I was struggling with one of these CSV issues and received some good advice from Hans Passant [1] on a Stack Overflow question pertaining to my problem (emphasis mine): 'It is pretty important that you don't try to fix it. That will make you responsible for bad data for a long time. Reject the file for being improperly formatted. If they hassle you about it then point out that it is not RFC-4180 compatible. There's another programmer somewhere that can easily fix this.' It makes perfect sense in hindsight. If you accept a malformed CSV file, people will expect you to accept any malformed data that has a CSV extension. You are taking on a lot of extra responsibility to cover for the lack of work by another programmer. Odds are they can make a change to fix the problem that takes a fraction of the time it would take you work around it. You just have to raise the issue. I realize that rejecting bad files isn't really possible in every circumstance. But I have a feeling it is an option more times than you might initially think."

electroly 9y ago

The secret to using CSV in Excel is to use Data > From Text instead of opening the CSV file directly. This opens the import wizard which allows you to, among other things, change the separator character. It's still not perfect, but it's much better than opening the CSV file directly.