5 ms·

I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file wit

I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file with Unicode in it - regardless of whatever ancient version and OS they're using - is to use tab separators and encode as UTF-16LE with a leading BOM.

wantoncl 7y ago
It isn't just Unicode, Excel has been like this for every version I can remember, and I've used v5.0, possibly earlier versions (1992/93). Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.
- jimnotgym 7y ago
  For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
  - turbinerneiter 7y ago
    Maybe the most common spreadsheet software should get it's act together and support this simple standard?
    - mhd 7y ago
      Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance. If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
      - rusk 7y ago
        Its not just non-Anglo .. semicolon is often used because data often contains commas, and it’s just easier to swop your delimiter than encapsulate your fields properly ... even then it’s just a simple matter to use the alternative delimeter, or convert by a simple search and replace (once youve added encapuslation to the relevant fields of course). Though CSV directly implies commas I find it helpful to consider it a synonym for “delimited text”. The specifics of the delimiter don’t bother me so much so long as the format is consistent.
        mhd 7y ago
        Not saying it's not useful, just that this arose from them not using a hard-coded comma, but referencing some default separator, that happens to be the semi-colon in some locales. I might be wrong and this could have an older precedent. Now, whether it would've been better to stick with the comma and just properly quote all the values for locales where the comma is a decimal separator and thus often used in spreadsheet columns is a moot point, the damage has been done. Personally I just call it "character separated values" anyway. I quite like tab-separated values. Makes it okay to read quite often and I think it's the default output of Postgres' COPY command.
    - jimnotgym 7y ago
      But wouldn't it help the non technical user more if Magento, Shopify etc supported the much more widely used standard of xlsx? Then people don't have to find out what UTF-8 even is. Library support for reading Excel docs is pretty good now, isn't it? I wish Excel did support csv better, but we are in the .001% of Excel users who even know that it doesn't do a good job.
      - tialaramex 7y ago
        XLSX isn't actually a standard in a meaningful sense. So while support for whatever parts are standard is "pretty good" how does that help your users when some of their sheets don't work correctly? Who wants to explain what's wrong and that nobody can fix it but er... maybe better luck next time? XLSX (Office Open XML) is full of safety valves that let Microsoft's existing products continue doing whatever poorly documented or undocumented stuff they were doing previously. Microsoft didn't want to have to go back to features which worked in Office already and either rip them out or re-implement them, and it didn't have documentation for those features that anybody else would be able to implement‡. So Office Open XML just says in those cases well here's a blob of data and good luck unless you're Microsoft Office. This is tolerable for exporting to Excel. I can emit compliant Office Open XML that gets my numbers into XL reliably. So that's nice. But when importing from Excel you're fighting that impedance mismatch. Rather than explain to users "Something about your document is incompatible and I swear it's Microsoft's fault" it's just better to say "Use CSV". ‡ e.g. suppose there's a line in Excel which defines a function FOO() by calling into some particular Windows DLL. Well that's not a useful thing to standardise. So do you call the relevant MS department and ask them to paste all their documentation for that DLL into your "spreadsheet" standard? No. You write "Implementation defined" and it becomes a black box.
      - modo_mario 7y ago
        XLSX is not remotely the same as CSV. CSV is way more widely used than you think under the hood of stuff and is a muuuuuch more slimmed down format. It's litterally just values separated by a delimiter. XLSX also isn't a standard. It'll keep changing. It's one of those reasons why Microsoft tried to shoehorn their own trash Open document format that would fit them and let them keep backwards compatibility with stoneage versions of their office suite. https://www.computerweekly.com/news/2240225262/Microsoft-attacks-UK-government-decision-to-adopt-ODF-for-document-formats https://www.computerweekly.com/news/2240225262/Microsoft-att... It's like saying everyone should have adapted to internet explorer rather than following proper web standards. Even if XSLX was some kind of standard it's a bad idea because at the end of the day MS shouldn't be trusted with those.
tim-- 7y ago
I wrote a Stack Overflow answer like this. Only Mac Excel had this issue, if I recall correctly. https://stackoverflow.com/a/16766198 https://stackoverflow.com/a/16766198 To be fair, the latest version of Excel has improved handling of CSV files immensely.

wantoncl 7y ago

It isn't just Unicode, Excel has been like this for every version I can remember, and I've used v5.0, possibly earlier versions (1992/93). Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.

jimnotgym 7y ago
For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
- turbinerneiter 7y ago
  Maybe the most common spreadsheet software should get it's act together and support this simple standard?
  - mhd 7y ago
    Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance. If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
    - rusk 7y ago
      Its not just non-Anglo .. semicolon is often used because data often contains commas, and it’s just easier to swop your delimiter than encapsulate your fields properly ... even then it’s just a simple matter to use the alternative delimeter, or convert by a simple search and replace (once youve added encapuslation to the relevant fields of course). Though CSV directly implies commas I find it helpful to consider it a synonym for “delimited text”. The specifics of the delimiter don’t bother me so much so long as the format is consistent.
      - mhd 7y ago
        Not saying it's not useful, just that this arose from them not using a hard-coded comma, but referencing some default separator, that happens to be the semi-colon in some locales. I might be wrong and this could have an older precedent. Now, whether it would've been better to stick with the comma and just properly quote all the values for locales where the comma is a decimal separator and thus often used in spreadsheet columns is a moot point, the damage has been done. Personally I just call it "character separated values" anyway. I quite like tab-separated values. Makes it okay to read quite often and I think it's the default output of Postgres' COPY command.
  - jimnotgym 7y ago
    But wouldn't it help the non technical user more if Magento, Shopify etc supported the much more widely used standard of xlsx? Then people don't have to find out what UTF-8 even is. Library support for reading Excel docs is pretty good now, isn't it? I wish Excel did support csv better, but we are in the .001% of Excel users who even know that it doesn't do a good job.
    - tialaramex 7y ago
      XLSX isn't actually a standard in a meaningful sense. So while support for whatever parts are standard is "pretty good" how does that help your users when some of their sheets don't work correctly? Who wants to explain what's wrong and that nobody can fix it but er... maybe better luck next time? XLSX (Office Open XML) is full of safety valves that let Microsoft's existing products continue doing whatever poorly documented or undocumented stuff they were doing previously. Microsoft didn't want to have to go back to features which worked in Office already and either rip them out or re-implement them, and it didn't have documentation for those features that anybody else would be able to implement‡. So Office Open XML just says in those cases well here's a blob of data and good luck unless you're Microsoft Office. This is tolerable for exporting to Excel. I can emit compliant Office Open XML that gets my numbers into XL reliably. So that's nice. But when importing from Excel you're fighting that impedance mismatch. Rather than explain to users "Something about your document is incompatible and I swear it's Microsoft's fault" it's just better to say "Use CSV". ‡ e.g. suppose there's a line in Excel which defines a function FOO() by calling into some particular Windows DLL. Well that's not a useful thing to standardise. So do you call the relevant MS department and ask them to paste all their documentation for that DLL into your "spreadsheet" standard? No. You write "Implementation defined" and it becomes a black box.
    - modo_mario 7y ago
      XLSX is not remotely the same as CSV. CSV is way more widely used than you think under the hood of stuff and is a muuuuuch more slimmed down format. It's litterally just values separated by a delimiter. XLSX also isn't a standard. It'll keep changing. It's one of those reasons why Microsoft tried to shoehorn their own trash Open document format that would fit them and let them keep backwards compatibility with stoneage versions of their office suite. https://www.computerweekly.com/news/2240225262/Microsoft-attacks-UK-government-decision-to-adopt-ODF-for-document-formats https://www.computerweekly.com/news/2240225262/Microsoft-att... It's like saying everyone should have adapted to internet explorer rather than following proper web standards. Even if XSLX was some kind of standard it's a bad idea because at the end of the day MS shouldn't be trusted with those.

tim-- 7y ago

I wrote a Stack Overflow answer like this. Only Mac Excel had this issue, if I recall correctly. https://stackoverflow.com/a/16766198 https://stackoverflow.com/a/16766198 To be fair, the latest version of Excel has improved handling of CSV files immensely.