4 ms·

Some things in Excel have not improved since 2003, that should be fixed before toying with "AI" . For example, when importing a CSV: - Excel cannot guess the e

by bambax 3y ago

Some things in Excel have not improved since 2003, that should be fixed before toying with "AI" . For example, when importing a CSV: - Excel cannot guess the encoding of the file, and relies on the user selecting it from a list that has maybe a hundred values (?!); the most common encoding, UTF-8, is neither at the top or at the bottom of that list, but somewhere near the end, and is called "65001 : Unicode (UTF-8)" (the preceding value is "65000 : Unicode (UTF-7)"). There is little chance non-technical users will get this right the first time, or any time thereafter, and the result is files that are circulated with garbled encoding and wrong values. - Excel cannot guess the separators either! (How hard can it be?) That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel. Yes, it's a minor thing, but it should be so easy to fix; instead of that, recent versions of Office have brought incredibly annoying animations that take 2-3 screens to disable.

dagw 3y ago
Excel cannot guess the separators either! (How hard can it be?) What is the separator in this .csv file ;) 1,2;3,4;5,6 But on a more serious note, I completely agree. There are so many small quality of life things that can and should be fixed in Excel, and it's baffling that they haven't.
- M4v3R 3y ago
  Sure, there are some edge cases where guessing the separator would be hard, but in 99% of cases you can correctly identify the separator pretty easily and in case you can't you just don't pre-fill that field in the import dialog.
  - PTOB 3y ago
    Psst, the joke is that it's a "comma separated variable" file.
    - cxr 3y ago
      The V in CSV doesn't stand for "variable". And that wasn't the joke.
- sroussey 3y ago
  If the file ends in CSV then the comma is the separator (the C is CSV).
  - dagw 3y ago
    Someone should tell the Excel developers, because they obviously didn't get that message. They use the .csv extension even when exporting files with ; separator.
tobr 3y ago
> That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel. Both of these things are consistent with trying to keep users in Excel. Make it easy to accidentally open Excel, but don’t make it too convenient to use open data formats when you have a proprietary one.
- tomjakubowski 3y ago
  XLSX is openly documented and free to use without royalty, it's not proprietary
  - resoluteteeth 3y ago
    The OOXML format is heavily oriented around internal details of Office in ways that makes it pretty inconvenient to work with even if it's "open". Also IIRC some more esoteric parts are just binary blobs that aren't actually documented and the specifications just say to do what Office does.
- bambax 3y ago
  Maybe... but I wouldn't be so quick as to assume bad intent ("never attribute to malice", etc.) What's the deal with "copy" for example? Why does the source of the copy have to be highlighted, why is it so fragile, why does it disappear from the clipboard when one presses the Escape key? This has been the case since I think the very first version of Excel, and never changed. I don't know of any other program that works that way. It didn't make sense then (I think), and it doesn't make sense now.
kubik369 3y ago
I have an even better one for you — if the language/region of your system is different, Excel parses the file differently. My example, Slovak region, numbers are officially written with a comma — 1,3 meaning a decimal "one point three". When you open a file that has the numbers from a different region (so 1.3), it completely falls apart.
- towawy 3y ago
  Workaround for anyone struggling with this: Use the Import feature from the Files menu instead of the usual Open dialog for csv files. You can change decimal separators in the import wizard.
- andersa 3y ago
  This is one of those things that make absolutely no sense. The "."/"," difference should only apply to displaying numbers to users like any other localization related change. Why on earth is it writing numbers differently in a standard format meant to be read only by computers? Such a colossal fuckup making csv files always broken for half of Excel users.
wombatpm 3y ago
How about a diff tool to track changes in excel DATA, FORMULAS, and MACROS. Do you know how many business are run with handcrafted excel sheets that are not in source control? Which is the correct budget model? Budget.xlsx Budgetv2.xlsx Budget.final.xlsx Budget.v2.final.final.xlsx Budget.final.final.submitted.revised.xlsx
- cxr 3y ago
  Those .xlsx files aren't just blobs. They're ZIP files. If you want version control for Office documents, it's as easy as unzipping them and checking in the results.
- MikusR 3y ago
  https://support.microsoft.com/en-us/office/show-changes-that-were-made-in-a-workbook-978ceea7-bbf6-4337-bca7-22e7cc9892e8 https://support.microsoft.com/en-us/office/show-changes-that... and https://support.microsoft.com/en-us/office/compare-workbooks-using-spreadsheet-inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42?ui=en-us&rs=en-us&ad=us https://support.microsoft.com/en-us/office/compare-workbooks...
voisin 3y ago
How about using AI to understand dates? My bank’s CSV output isn’t recognized by Excel and needs to be manipulated so it will follow a date format.
gigel82 3y ago
I've opened thousands of CSV files directly in Excel and never had one not open correctly (unless the file itself was not valid CSV, especially with things not quote-enclosed properly). It's likely I've mostly had to deal with ASCII / Latin alphabet, so don't know how well it handles unicode.
downrightmike 3y ago
I just double click the csv and it opens in excel correctly.
Tagbert 3y ago
Excel has some annoying defaults when importing data, too. My company tracks transactions with an ID that is a numeric string that Excel assumes is an integer but longer than it supports so it truncates the value and displays it in scientific notation. Original value: -7223371999747962216 Truncated value: -7223371999747960000 Displayed value: -7.22337E+18 It would be fine if it just treated it as text but by discarding digits it makes the values useless. You can’t open a CSV file directly. You have to manually import the data and specify specific columns as text. Every. Single. Time. It cannot be automated. I’ve been sent so many excel files with truncated data like that because most people don’t even know about the problem, let alone know how to work around it.
- bambax 3y ago
  It may be automatable if you enclose the values in double quotes and specify that double quotes mark text (which is the default I think).
  - Tagbert 3y ago
    Sure, if you have control over the source, but that is not always the case. CSV is very loosely specified and there are large variations in implementations.
resoluteteeth 3y ago
It seems like they don't want to change anything that affects existing behavior, so they're mostly adding new features that are orthogonal to existing features. Unfortunately this means that they're not really fixing most of the annoyances that were already present. "Excel Labs" appears to just be an add-on using the normal API which is a lot easier than making change to Excel itself. They've also added stuff like new functions for formulas and a new type of comments that are more like word comments (while renaming the old comments to "note") but it's pretty clear that they have chosen to focus on things that they can tack on without having to change existing functionality (presumably either so they won't break anything and/or because it's simpler easier to add this kind of stuff rather than getting too deep into the existing codebase)
- mcbishop 3y ago
  I mostly agree with you. But I assume dynamic-array formulas (released in 2018) required deep calculation-engine changes. (For me, this feature is a game-changer.)
erichocean 3y ago
> Yes, it's a minor thing I think you may slightly misunderstand the functionality, it's an updateable link to some other file (which is, frankly, great). I can import a CSV file I generate with code ONCE, build up a huge calculation from it (probably in other sheets), and then the next day, regenerate the CSV, update the link WITHIN EXCEL, and boom, all of my computation is redone. Very underutilized feature, but incredibly useful when using Excel not as a CSV viewer (if you want that, you can buy that elsewhere) but as a critical part of running a business. Excel exists to make it almost free to write accounting-like software. It does NOT exist to view CSV files.
prepend 3y ago
I open csv files in excel every day without going through these dialogs. Aside from excel doing stupid stuff like misinterpreting columns as dates every once in a while, it’s good enough to eyeball stuff.

by bambax 3y ago

dagw 3y ago

Excel cannot guess the separators either! (How hard can it be?) What is the separator in this .csv file ;) 1,2;3,4;5,6 But on a more serious note, I completely agree. There are so many small quality of life things that can and should be fixed in Excel, and it's baffling that they haven't.

M4v3R 3y ago
Sure, there are some edge cases where guessing the separator would be hard, but in 99% of cases you can correctly identify the separator pretty easily and in case you can't you just don't pre-fill that field in the import dialog.
- PTOB 3y ago
  Psst, the joke is that it's a "comma separated variable" file.
  - cxr 3y ago
    The V in CSV doesn't stand for "variable". And that wasn't the joke.
sroussey 3y ago
If the file ends in CSV then the comma is the separator (the C is CSV).
- dagw 3y ago
  Someone should tell the Excel developers, because they obviously didn't get that message. They use the .csv extension even when exporting files with ; separator.

tobr 3y ago

> That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel. Both of these things are consistent with trying to keep users in Excel. Make it easy to accidentally open Excel, but don’t make it too convenient to use open data formats when you have a proprietary one.

tomjakubowski 3y ago
XLSX is openly documented and free to use without royalty, it's not proprietary
- resoluteteeth 3y ago
  The OOXML format is heavily oriented around internal details of Office in ways that makes it pretty inconvenient to work with even if it's "open". Also IIRC some more esoteric parts are just binary blobs that aren't actually documented and the specifications just say to do what Office does.
bambax 3y ago
Maybe... but I wouldn't be so quick as to assume bad intent ("never attribute to malice", etc.) What's the deal with "copy" for example? Why does the source of the copy have to be highlighted, why is it so fragile, why does it disappear from the clipboard when one presses the Escape key? This has been the case since I think the very first version of Excel, and never changed. I don't know of any other program that works that way. It didn't make sense then (I think), and it doesn't make sense now.

kubik369 3y ago

I have an even better one for you — if the language/region of your system is different, Excel parses the file differently. My example, Slovak region, numbers are officially written with a comma — 1,3 meaning a decimal "one point three". When you open a file that has the numbers from a different region (so 1.3), it completely falls apart.

towawy 3y ago
Workaround for anyone struggling with this: Use the Import feature from the Files menu instead of the usual Open dialog for csv files. You can change decimal separators in the import wizard.
andersa 3y ago
This is one of those things that make absolutely no sense. The "."/"," difference should only apply to displaying numbers to users like any other localization related change. Why on earth is it writing numbers differently in a standard format meant to be read only by computers? Such a colossal fuckup making csv files always broken for half of Excel users.

wombatpm 3y ago

How about a diff tool to track changes in excel DATA, FORMULAS, and MACROS. Do you know how many business are run with handcrafted excel sheets that are not in source control? Which is the correct budget model? Budget.xlsx Budgetv2.xlsx Budget.final.xlsx Budget.v2.final.final.xlsx Budget.final.final.submitted.revised.xlsx

cxr 3y ago
Those .xlsx files aren't just blobs. They're ZIP files. If you want version control for Office documents, it's as easy as unzipping them and checking in the results.
MikusR 3y ago
https://support.microsoft.com/en-us/office/show-changes-that-were-made-in-a-workbook-978ceea7-bbf6-4337-bca7-22e7cc9892e8 https://support.microsoft.com/en-us/office/show-changes-that... and https://support.microsoft.com/en-us/office/compare-workbooks-using-spreadsheet-inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42?ui=en-us&rs=en-us&ad=us https://support.microsoft.com/en-us/office/compare-workbooks...

voisin 3y ago

How about using AI to understand dates? My bank’s CSV output isn’t recognized by Excel and needs to be manipulated so it will follow a date format.

gigel82 3y ago

I've opened thousands of CSV files directly in Excel and never had one not open correctly (unless the file itself was not valid CSV, especially with things not quote-enclosed properly). It's likely I've mostly had to deal with ASCII / Latin alphabet, so don't know how well it handles unicode.

downrightmike 3y ago

I just double click the csv and it opens in excel correctly.

Tagbert 3y ago

Excel has some annoying defaults when importing data, too. My company tracks transactions with an ID that is a numeric string that Excel assumes is an integer but longer than it supports so it truncates the value and displays it in scientific notation. Original value: -7223371999747962216 Truncated value: -7223371999747960000 Displayed value: -7.22337E+18 It would be fine if it just treated it as text but by discarding digits it makes the values useless. You can’t open a CSV file directly. You have to manually import the data and specify specific columns as text. Every. Single. Time. It cannot be automated. I’ve been sent so many excel files with truncated data like that because most people don’t even know about the problem, let alone know how to work around it.

bambax 3y ago
It may be automatable if you enclose the values in double quotes and specify that double quotes mark text (which is the default I think).
- Tagbert 3y ago
  Sure, if you have control over the source, but that is not always the case. CSV is very loosely specified and there are large variations in implementations.

resoluteteeth 3y ago

It seems like they don't want to change anything that affects existing behavior, so they're mostly adding new features that are orthogonal to existing features. Unfortunately this means that they're not really fixing most of the annoyances that were already present. "Excel Labs" appears to just be an add-on using the normal API which is a lot easier than making change to Excel itself. They've also added stuff like new functions for formulas and a new type of comments that are more like word comments (while renaming the old comments to "note") but it's pretty clear that they have chosen to focus on things that they can tack on without having to change existing functionality (presumably either so they won't break anything and/or because it's simpler easier to add this kind of stuff rather than getting too deep into the existing codebase)

mcbishop 3y ago
I mostly agree with you. But I assume dynamic-array formulas (released in 2018) required deep calculation-engine changes. (For me, this feature is a game-changer.)

erichocean 3y ago

> Yes, it's a minor thing I think you may slightly misunderstand the functionality, it's an updateable link to some other file (which is, frankly, great). I can import a CSV file I generate with code ONCE, build up a huge calculation from it (probably in other sheets), and then the next day, regenerate the CSV, update the link WITHIN EXCEL, and boom, all of my computation is redone. Very underutilized feature, but incredibly useful when using Excel not as a CSV viewer (if you want that, you can buy that elsewhere) but as a critical part of running a business. Excel exists to make it almost free to write accounting-like software. It does NOT exist to view CSV files.

prepend 3y ago

I open csv files in excel every day without going through these dialogs. Aside from excel doing stupid stuff like misinterpreting columns as dates every once in a while, it’s good enough to eyeball stuff.