3 ms·

i recently got burned because at some point in the last few years Excel decided it would be better if it attempted to use localized thousands and decimal separa

i recently got burned because at some point in the last few years Excel decided it would be better if it attempted to use localized thousands and decimal separators. item,cost,currency "Socks",1.234,USD Represents socks costing about a dollar in countries that use '.' as a decimal (most) and about a thousand dollars in countries that use a ','. In locales using a decimal period '.' double clicking will open the CSV just fine. In locales using a ',' it comes in as text but the default settings for text to columns use localized number parsing but still let you choose a now ambiguous comma as a column separator. I'm 99% sure it didn't used to do this as we've been making CSVs for the same clients in the same regions (Netherlands) for years and only more recently have they had this trouble. Edit: I've used Excel for at least 20yrs. I thought i new how to use it and how to search it's docs. This is the only article I've found online that mentions how to handle this as a end user. Near the bottom under "Converting International Number Formats" where it of course assumes you have decimal comma and need a decimal period. https://www.howtogeek.com/407217/how-to-use-text-to-columns-like-an-excel-pro/ https://www.howtogeek.com/407217/how-to-use-text-to-columns-... I did also find a 20 minute youtube eventually

ndespres 6y ago
I have long suspected that Excel's treatment of CSV files was intentionally antagonistic. For example, opening a CSV file will start an importer function where you must specify how fields are delimited within the file, and "comma" is not automatically selected, ever, despite "comma-separated" being literally named in the file extension. Surely the folks programming it are not that short-sighted. It's just a punishment for not using their default format.
- rightbyte 6y ago
  Well I would give Excel some cred for acctually following local number conventions. It the beginning there was no internet and interoperability issue. Usually it is the us-en way or the highway.
- kalleboo 6y ago
  It also has really broken handling of text encodings. I've taken to importing CSV files using Apple Numbers, and saving as Excel (i use Numbers primarily, Excel is just when I need to be interoperable with someone else)
dfox 6y ago
Excel's notion of what is "CSV" and syntax of its formula language is locale dependent for as long as I care about that. Notably, for locales that use ',' as decimal point excel uses semicolons as separators in both its "C"SV format and formulas. What changed in last few version is that excel started to distinguish whether you are "opening" an CSV file (eg. by double clicking it) or "importing" it and the former tends to lead to various kinds of random breakage in presence of the aforementioned (mis-)feature.
zokier 6y ago
Modern excel is better at this because it has this "powerquery" thing integrated to csv import which allows bit more control how data is imported. The classic csv import is really bad though. It is bit hidden feature, you have to click "transform data" button when importing the file Here is sort of some docs on it https://support.microsoft.com/en-gb/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba https://support.microsoft.com/en-gb/office/import-or-export-... (section "Import a text file by connecting to it (Power Query)")
- amichal 6y ago
  I've always wanted to investigate if there is a current way to do this on a URL without resorting to scripting but have never really gotten the energy up to do it. Being able to send users to https://www.example.com/foo/your_data.csv https://www.example.com/foo/your_data.csv and have that send the right headers for caching, refresh and authentication so they can use Excel as a mini BI front end without screwing around with ODBC or similar etc would be a nice way to answer the "integration" question for clients that are too small to have a IT department to help them with database connections, firewalls etc.
  - texasbigdata 6y ago
    I believe this exists. Under data, “get data”, then the url/http feature. You can also manually recursively limit a list of urls but by that point you’re privacy going beyond what the tool should be used for. https://youtu.be/cLWCUgCYl5Y https://youtu.be/cLWCUgCYl5Y
  - cosmie 6y ago
    Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a decade ago, and more recently tons of other products[1]. The Web[2] connector is what you're after. It can consume a variety of formats, including csv, xml, and json. It supports HTTP Basic Auth pretty well, but if you want to put the data source behind oauth it can get tricky (it technically supports it but depends on how novel the implementation is). Credentials sit with the (Excel) client, so if the file gets shared with a new user it'll prompt them for authentication details when they first attempt to refresh it. It'd be pretty easy to set up a template/turnkey workbook you can just hand over to new clients with everything all set up and all they have to do is enter their credentials when prompted. Only caveat is Excel for Mac - it only gained Power Query about a year ago, and porting it over is a very involved[3] process including an entire .NET Core rewrite and factoring out Windows dependencies from legacy cruft. The Web connector is one of the many that haven't made it over to the Mac yet, so any clients using a Mac would still need an alternative. [1] https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query#where-to-use-power-query https://docs.microsoft.com/en-us/power-query/power-query-wha... [2] https://docs.microsoft.com/en-us/power-query/connectors/web https://docs.microsoft.com/en-us/power-query/connectors/web [3] https://devblogs.microsoft.com/dotnet/using-net-core-to-provide-power-query-for-excel-on-mac/ https://devblogs.microsoft.com/dotnet/using-net-core-to-prov...

ndespres 6y ago

I have long suspected that Excel's treatment of CSV files was intentionally antagonistic. For example, opening a CSV file will start an importer function where you must specify how fields are delimited within the file, and "comma" is not automatically selected, ever, despite "comma-separated" being literally named in the file extension. Surely the folks programming it are not that short-sighted. It's just a punishment for not using their default format.

rightbyte 6y ago
Well I would give Excel some cred for acctually following local number conventions. It the beginning there was no internet and interoperability issue. Usually it is the us-en way or the highway.
kalleboo 6y ago
It also has really broken handling of text encodings. I've taken to importing CSV files using Apple Numbers, and saving as Excel (i use Numbers primarily, Excel is just when I need to be interoperable with someone else)

dfox 6y ago

Excel's notion of what is "CSV" and syntax of its formula language is locale dependent for as long as I care about that. Notably, for locales that use ',' as decimal point excel uses semicolons as separators in both its "C"SV format and formulas. What changed in last few version is that excel started to distinguish whether you are "opening" an CSV file (eg. by double clicking it) or "importing" it and the former tends to lead to various kinds of random breakage in presence of the aforementioned (mis-)feature.

zokier 6y ago

Modern excel is better at this because it has this "powerquery" thing integrated to csv import which allows bit more control how data is imported. The classic csv import is really bad though. It is bit hidden feature, you have to click "transform data" button when importing the file Here is sort of some docs on it https://support.microsoft.com/en-gb/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba https://support.microsoft.com/en-gb/office/import-or-export-... (section "Import a text file by connecting to it (Power Query)")

amichal 6y ago
I've always wanted to investigate if there is a current way to do this on a URL without resorting to scripting but have never really gotten the energy up to do it. Being able to send users to https://www.example.com/foo/your_data.csv https://www.example.com/foo/your_data.csv and have that send the right headers for caching, refresh and authentication so they can use Excel as a mini BI front end without screwing around with ODBC or similar etc would be a nice way to answer the "integration" question for clients that are too small to have a IT department to help them with database connections, firewalls etc.
- texasbigdata 6y ago
  I believe this exists. Under data, “get data”, then the url/http feature. You can also manually recursively limit a list of urls but by that point you’re privacy going beyond what the tool should be used for. https://youtu.be/cLWCUgCYl5Y https://youtu.be/cLWCUgCYl5Y
- cosmie 6y ago
  Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a decade ago, and more recently tons of other products[1]. The Web[2] connector is what you're after. It can consume a variety of formats, including csv, xml, and json. It supports HTTP Basic Auth pretty well, but if you want to put the data source behind oauth it can get tricky (it technically supports it but depends on how novel the implementation is). Credentials sit with the (Excel) client, so if the file gets shared with a new user it'll prompt them for authentication details when they first attempt to refresh it. It'd be pretty easy to set up a template/turnkey workbook you can just hand over to new clients with everything all set up and all they have to do is enter their credentials when prompted. Only caveat is Excel for Mac - it only gained Power Query about a year ago, and porting it over is a very involved[3] process including an entire .NET Core rewrite and factoring out Windows dependencies from legacy cruft. The Web connector is one of the many that haven't made it over to the Mac yet, so any clients using a Mac would still need an alternative. [1] https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query#where-to-use-power-query https://docs.microsoft.com/en-us/power-query/power-query-wha... [2] https://docs.microsoft.com/en-us/power-query/connectors/web https://docs.microsoft.com/en-us/power-query/connectors/web [3] https://devblogs.microsoft.com/dotnet/using-net-core-to-provide-power-query-for-excel-on-mac/ https://devblogs.microsoft.com/dotnet/using-net-core-to-prov...