Update October 24th, 2023, 11:25AM ET: Microsoft has updated Excel on Windows and macOS, adding a toggle to turn off automatic data conversion. The original version of this article continues below.
Thank God they added a toggle and not an on-by-default fuzzy matching where "MARCHF1" would still yield "March 1st". What a pity if the genes had to be renamed again and again, playing hide-and-seek with Excel.
I am not usually sarcastic, but can't help it, sorry. Also, I do think this is a very much a possibility.
I think implicit conversions is the other billion dollar mistake in computing. It just shows up as a subtle source of mistakes in so many places. JavaScript, MySQL, YAML, Excel, PHP, just to make a few off the top of my head, have all caused countless bugs and security exploits due to implicit conversations of data which can be interpreted in multiple ways, and where the system happened to pick the wrong one.
null is really another special case of the same problem - allowing a nullable type to be implicitly coerced to a nullable one, or rather a general lack of type safety.
> Probably the general rule is simply, “you get more bugs when expectations don’t match reality”.
That's too vague to be useful. You might as well say "you get bugs when you write wrong code". Whereas framing it as a lack of type safety points towards an actionable solution.
I think this is too strong a take. The general rule, as stated, can be useful as a way to think about systems, interfaces, architecture, etc in broad strokes as a vector for expectations which might fall victim to this pitfall. Being general, it isn’t prescriptive about the solution… but then, type safety isn’t always (and isn’t the only) solution. It’s a way to think about problems where you explicitly scrutinize the expectations they promote and their corresponding realities. You might arrive at type safety as a measure to align those, but you might also consider a variety of other concerns in the same thought process. It’s only unuseful if the problem you’re applying it to is itself vague, or if you’re unprepared to do the thinking involved in understanding a more specific problem and what expectations you’re addressing in solving it.
I think these are billion dollar mistakes only because they've allowed for trillions in improvements in the first place.
People choose these system with loose conversions over and over again because it's an advantage up to a point. Of course it becomes a liability when expanded too much and used in now critical systems, but IMHO the tradeoff usually ends as a net positive. Which is why, over and over we build more of these systems, and they fare better in the market than the stricter ones.
An opt-out doesn't solve the problem, because one person in the chain of custody if an excel file who hasn't opted out would be enough to destroy the data.
Only if the option was implemented as a user preference which isn’t stored in the file. The right way to do this would be to add the flag as a file header, and give people a UI control to set it by default. That way you’d always see spreadsheets as the person who gave it to you did, and you would create new ones with your preferred behavior.
Channeling my inner QA person, this breaks in many subtle and not so subtle ways:
- people love to export as CSV. And I kinda understand them, csv looks simple enough, what could go wrong ?
- other spreadsheet softs need to recognize the same field and act the same way. Google Sheets, LibreOffice, KingSoft's one etc. Not counting old office versions that aren't updated anymore, because the owner doesn't want to move into the cloud.
- the flag needs to stay when moving sheets around, copy to another set, etc.
That could still be better than nothing. But could still lead to catastrophic loses as people get used to it working most of the time, there's no perfect solution.
It might be easier to think about as the difference between perfection and practical improvements. Right now 100% of Excel users are at risk of silent data corruption. The proposed change would allow people to reliably prevent this category of corruption for all of the documents they create and over time that’d become a majority in their field.
This would also offer the path for changing the default over time to safe by default. There will be more spreadsheets created in the future than exist now.
> Right now 100% of Excel users are at risk of silent data corruption.
They already added an option in the settings last year to deal with this specific issue. Their solution isn't perfect either, they made the tradeoff to optimize for up to date excel users.
Still the same problems. Unless you exclude people with older excel versions they can still ruin the file. And if the first person in the chain didn't make the change, who would even notice it? So we're back at requiring extreme vigilance without any assurance.
Also keep in mind that the first person doesn't necessarily use excel at all. The file might start and end as a text file or TSV.
I don't understand what's wrong with the existing solution of:
Right Click > Format Cells > Text.
Set the data type of the column and Excel will not fiddle with it. Let it stay implicit and Excel will to guess. This change is saved in the file. The article only slightly touches on this: converting to CSV and losing all the Excel formatting and then opening it in Excel again.
As usual, I think it's a lot more nuanced than that.
An option was added last year [0] the way you propose it, but until then users already had the option to mark the columns as text. The same way we have to deal with any data where auto detection might be problematic: explicitely set a type, and potentially a format.
But of course that's bothersome, sometime people forget to do it, sometimes they don't even know how to properly do it etc. And some of these people will still probably forget to set the option, and there are still edge cases where it can fail (macros are cited)
> but until then users already had the option to mark the columns as text
This only works on Excel files though, which save a type for columns/cells. With CSV files, Excel would just auto-format them. I guess you could save everything as xls/xlsx, but I shouldn't have to use a vendors file format because they're threatening to corrupt my data if I don't.
That's just CSV being unusable shit. There's always a schema for the types of the fields, but there's no way to communicate that schema, so CSV is inherently ambiguous. If your data includes fancy things like numbers you should avoid CSV.
It's still a shame that Excel's JSON support is so good, but no business user trusts it because "you can't double click the files" and CSV remains the worst best thing you can double click.
I don't want Excel to take over the file association for .json but I do sometimes wish Excel would just invent a file extension like .xljson that loads JSON so that at least you could trivially rename a JSON file to .xljson and get working double-click for your business users but a file format (with optional schema support even) that's a more modern and saner de facto standard than CSV.
I think that one dumb trick would improve everyone's productivity a great deal.
No, I'm talking about developing for those users. Right now if you want to write the simplest, dependency lightest export code that those users can double-click and open in Excel you are writing yet another CSV export. It would be nice if instead you could take an existing API that returns JSON and wrap it in a mimetype like application/json+excel, and add a Content-Disposition: attachment; filename=thisapi.xljson and get Excel double-click support without another flaky CSV file.
CSV doesn't do what you want and excel doesn't support CSV files. Why insist on a completely wrong tool and file format combination?
If the excel developers cared about CSV support they would have added more settings to the CSV import tool that let you specify everything. Every single data base tool that lets you import CSV files has worked better than Excel. It simply means Excel wasn't built to import CSV files. Tough luck.
.xlsx file extension is well supported by many open source libraries. If you insist on working with Excel then you should use the appropriate file format, which is .xlsx. I do that all the time and it just works.
when I type "age" into a spreadsheet it is text. When I type "23" into a spreadsheet I can later use it as a number without converting to another type. People want and expect this behavior. What is your preferred solution to this problem?
A) I have no clue where you got this from. It seems like a convenient assumption, but not one that's easily verified, especially when folks are introduced to the dangers of such an approach.
B) Giving people control over how the data is interpreted outside of the default interpretation seems like a no-brainer. I don't really rely on default interpretation of anything, especially in the context of spreadsheets given how eager they are to interpret data in unexpected ways, but based on this thread it seems like it's rather difficult to turn off the date conversion behavior in excel.
C) I was replying to a comment about implicit conversions, which is far larger than just the context of excel spreadsheets.
Wouldn't your approach be 100% covered by current type implementation ?
As a workflow, if every time you create a new spreadsheet you first define all the column types and constraints, you're in the world you're describing. Passing around that spreadsheet would also keep these settings as long as it's not converted and everyone's version is compatible.
That means you get the best of both worlds: people who care about strict types can be as strict as they want, other people can benefit from auto conversions.
> People choose these system with loose conversions over and over again because it's an advantage up to a point.
The world is full of antipatterns that people implement with little, no, or flawed analysis. They do not fare better in the market, they propagate the same way entropy does.
It’s so awful in JavaScript that they made new comparison operators that don’t do it. And now it’s pretty much universal that you set == and != to be linting errors and just never use them.
They’re good for professional and hobby code golf though.
Except for when you to `var != null` to make sure var isn't null OR undefined since those are two different things in javascript and undefined will implicitly be converted to null here.
Call it cynicism but I think humans just prefer things to be implicit. I've been annoyed by how people do not want to be specific, but then rant about how someone did not understand exactly what they wanted.
A certain CEO says that we won't need programming languages because you will be able to program AI systems with natural language. Well, the problem with natural language is not that we do not have the tech, it is that natural language is too imprecise. People will, again, end up being dissatisfied by the output of AI systems and blame it on the tech, while the problem just remains that as humans we kinda suck at being explicit about what we want.
The thing is, most of the time, you can actually have both. You just have to spend the time to build systems that way.
To use the example from the article: spreadsheets could use strongly-typed columns, but guess the column type automatically based on the values entered. When things go right, it'd be just as easy as today, but if things go wrong, you always have an escape hatch: with a little more work, you can manually specify the column type. Problem solved. That gives you the increased productivity you want for the happy case, without the frustration of trying to find a workaround for the corner case. This kind of approach needs more architecture work at the beginning of a project, but it's almost always feasible.
That being said, I think your frustration is warranted, although I think it's much more pervasive than tech. At its core, I think the problem is that, in your example, the CEO and the programmer are having two different conversations without really realizing it. The CEO wants a problem solved, but the programmer wants the tools to solve the problem themselves. The CEO thinks that ML should be able to solve their problem, but the programmer sees that ML as a tool has serious limitations, and won't help solve under-specification. And then they both end up talking past each other.
It seems that a lot of (at least western society) is built around the idea that when someone asks for help, what they "really" want is, metaphorically, to be given a fish for dinner, instead of learning how to fish. That's the CEO in your example; the presumption is "I want help" means "solve my problem", not "make it easy for me to solve my problem myself". It's one of my biggest frustrations, because it inherently robs you of agency and in many cases can be downright condescending -- for example, when I was working as a mechanical engineer, there were shop floor guys ignoring the tolerances I specified on my drawings because they assumed I was over-specifying them, because they lacked the context of the rest of the design, and weren't willing to sit down to hear the bigger picture. But when I had to send the parts back for re-work, they acted like it was my fault.
Anyways, this is all a really long-winded way of saying, I think this is largely a social problem, not a technological one. But I don't think it's inherent in humans; I think this is something we've learned as a part of our culture.
> Call it cynicism but I think humans just prefer things to be implicit.
I agree with the implicit part, but not with the cynicism. Most human-to-human communication has implicit details, simply because there would otherwise be too many "obvious" details that need to be communicated. Suffice to say, humans tend to misunderstand each other when different assumptions are held and things can go horribly wrong.
People like mathematicians and programmers, who are used to formal thinking, know that you have to be rigorous in your statements. We understand that details and edge cases matter. And it can be frustrating when other people don't understand that you need to look at the full picture instead of a very rough sketch.
> I've been annoyed by how people do not want to be specific, but then rant about how someone did not understand exactly what they wanted.
I'm also annoyed when this happens. But then I say: This is what you requested. I can only work with the information that you provide to me.
> I'm also annoyed when this happens. But then I say: This is what you requested. I can only work with the information that you provide to me.
I think it depends on the attitude of the person giving feedback. I generally find that digging into why this isn't what the user wanted is more illuminating. Tell me a story and I'll gain context and get more of the implicit details right going forward.
For humans it’s primarily a form of data compression. We rely on a shared understanding and ability to detect inconsistencies and ask.
This breaks down when someone starts talking outside of that shared understanding (say, on the internet) but do not realize more context is now needed.
If only there was a trade, a group of educated professionals with specific training in the use of language to describe a desired outcome. We could call them "language engineers" and regulated them via state associations meant to protect the public from charlatans.
I wouldn't call them language engineers as they don't engineer languages. Such an individual would be more accurately described as a Language-Astute Worker Yielding Exact Responses.
Lol, yes. Scientists, engineers, accountants, businessmen, etc. all use Excel. They say the modern world is built upon spreadsheets (even broken ones that show the wrong data). I forgot who said this though...
> So let me get this straight: Scientists would rather rename genes,
than to simply use a tool other than MS Excel?
It's not that they'd rather.
They understand that a trillion dollar corporation cannot supply the
most basic tool they need for work, despite trying for 40 years. They
just can't effect change. The real problem behind it all is that they
allow themselves to be bullied. Despite holding multiple PhDs they
cannot actually exercise rational choice.
The scientists work in an organisation where a suit called Kevin and a
middle aged lady in a cardigan called Mavis choose what software they
use. Kevin and Mavis are "IT experts".
Kevin and Mavis choose Microsoft because they think it is "reputable",
and they like the colours, and because HR have had "Microsoft Office"
as a mandatory hiring requirement since 1990.
Mostly though, they don't want to upset Colin who is head of IT, Colin
still thinks Windows XP was the best thing ever and goes a funny shade
of red when anyone mentions Linux (or anything else he doesn't
understand). Bless him, he's close to retirement anyway.
Meanwhile Tom the MD, whose "door is always open" invites the
scientists and engineers into a "consultation". They tell him that
Microsoft and Google offer 1990s solutions to twenty-first century
needs, that their products are unfit for purpose, unstable and
insecure too. They even produce a report of all the software they
need along with suppliers, budgets and installation guides. Colin has
gone a very worrying shade of purple.
Six months later the square root of fuck-all has happened. An
uncomfortale quiet hangs in the air. Someone nobody ever heard of
called Isabella, who actually works for "strategy and brand" in the
parent company - and is married to some Microsoft guy - over-rode the
whole thing.
Tom mumbles something about preferred suppliers and compliance. Kevin
and Mavis assure the scientists that Microsoft are going to fix
everything in the next update. Colin is restored to a smug pink.
Science will have to wait.
In mine (academic) they fired Mavis, Kevin and Colin (a week before
his retirement party) and moved the lot into "the cloud". What was
once the "campus network" vanished and became a wholly managed outpost
of Microsoft.
> a trillion dollar corporation cannot supply the most basic tool they need for work
Same - I'm still waiting for Microsoft to make me a decent set of drill bits. Can't believe a trillion dollar corporation cannot supply the most basic tool I need for work!
> Colin still thinks Windows XP was the best thing ever and goes a funny shade of red when anyone mentions Linux (or anything else he doesn't understand)
Circa 2000 I worked in a place with such a head of IT. After some effort I managed to get my work done by bringing in my own Linux laptop. But, connecting it to the network was strictly prohibited ("everyone knows that Linux is insecure"), and data had to be transferred by floppy disk to/from the rather unreliable networked Windows PC shared by me and two other scientists in the same office. This PC used to crash after each user logged out and required a reboot pretty much every time.
Perhaps there is a minority who thinks that automatic data conversion is unsound, and a majority who thinks it's 'pragmatic', and will let them move faster.
A lot of scientists are very bright people that can't invest the time to learn another tool that maybe is worse than Excel (e.g. Google Docs also does the gene-to-date swapping, try writting "SEPT2", the known gene [https://en.wikipedia.org/wiki/SEPT2] in a cell), investing time and effort in changing pipelines that work for them.
Instead, the path of least resistance is to change the name of all known genes that cause problems with Excel. Biologist are changing and adding names all the time, so it's not like this is new. E.g. https://www.uniprot.org/uniprotkb/Q04609/entry#names_and_tax... shows you a gene with 5 names.
When I had this issue, the path of least resistance was to use Libre Office Calc. Of course, that's exactly the kind of application that my company now wants to remove from all systems for compliance with something called "Cyber Essentials".
Spreadsheets are irreplaceable because they're the only no-code software development tool that's ever succeeded.
Nothing will ever replace spreadsheets, because the tech industry no longer believes in making products for power users. In modern thinking, any form of user training is an indication your product is too complicated, and any feature only used by 5% of users should be removed.
The idea of a product where users have to type in incantations like =VLOOKUP(A1,C2:D16,2,False) which they're just supposed to somehow know? A product that's essentially unusable on mobile? A product with features like 'Pivot Table' that only 1% of users ever use, and yet it's a vital core feature?
It's absurd. Only in 1970 could someone have conceived of such a thing.
Or any R or J user doing faster and secure stuff, even by using BioPerl/BioPython under a Jupyter instance. 1970's? This came from 2040, but brainwashed Excel users are still mentally locked in the 80's.
It's not just a question of individual research groups deciding to use a different tool.
Even if you're conscientious enough to handle this issue, the automatic conversion could come and cause you problems from anyone you share data with: medical teams, grad students, administrators...
Approximately 90%[1] of scientists who deal with this type of thing have in fact stopped using Excel (for this and other reasons), the problem is that the nature of this problem is such that the remaining 10% can really still screw it up for everyone. Also, next time you're having a problem with any of your tools, trying ditching that tool and picking something else. See if your productivity goes up or down.
They could also just format that column explicitly, it's not really an issue of how much friction there is so much as that there is friction, it seems to me.
As a computer scientist this sounds ridiculous. Especially that they restrict themselves to Latin letters and Arabic numbers.
I guess many people here will point out that they should use other tools. I think we should help them in any way.
They could also prefix every gene with Gn so it cannot be confused with other acronyms (e.g., GnDEC12). It not only breaks parsing by computers but also by humans who might read offensive words into it. Depending on the structure of data this prefix could even be compressed with 0 bits.
> Especially that they restrict themselves to Latin letters and Arabic numbers.
I think this is sensible actually. Certainly better than the inverse, Latin numbers and Arabic letters. I'm not sure why they put it in this way (with the ethnic reference), but they are really just saying about putting it letters and numbers that people use and would be able to spell out to each other.
I think your idea of a prefix though is great - simple.
Gene names are potentially mentioned a lot in a paper, a prefix would be seriously annoying for reading. And which other characters would you want to use here in the names, a restriction to the latin alphabet and numbers seems very reasonable to me?
We, as CS people, think computers are easy. However, for other disciplines, computers are not easy.
We can try to (and do) create tools, but it's not as simple as a table editor or a simple CRUD application. Scenarios has many peculiarities and edge cases.
A fair proportion of CS is precisely oriented around the fact that computers are not easy in ways that non-CS people don't realise, and provides methods for quantifying and trying to cope with that. I refer you to https://xkcd.com/2030/
I think the reason why first two examples can say that they are safe is that in case of failure, people die. And thus there was a regulation introduced. People generally care when they can die.
In case of software, rather rarely people die and when software is being done on something human critical (a friend of mine works on pacemakers), there a great deal of care being done.
So basically it's a wide range from DIY using a cheap wrench to person rotating a torque calibrated wrench on a rocket. Because just like the hammer, there is a wide range of use cases, some critical (e.g. pacemaker), some mundane (e.g. ecommerce).
Planes and elevators always have a risk of crashing and killing humans. And it did. Software doesn't.
I was trying to find the right words to kindly point this out as a CS guy working in this space, thank you for just cutting to the chase and being blunt.
The proposed solution is also not really a solution in that it still allows for one of the main issues: random people shouldn't be responsible for creating names for things that should have unique IDs. Which is why things like ensembl IDs exist.
These gene names are human readable strings, not concept unique identifiers. We in bioinformatics / comp bio world strongly encourage the use of ncbi or ensmbl gene IDs. In general it is bad practice to use human readable “names” of things as a primary indexing key.
Or they could set the format of the column to text instead of keeping it as general and everything would just work.
If these "scientists" are having this problem then they haven't spent any time at all learning how to use the tool they rely on.
Can we trust anything these people produce if this is the level of competence they have with Excel?
A lot of analysis is done using CSVs being pushed in and out of Excel. Doing so strips the formatting. Please understand the workflows being crying “skill issue”.
Sorry, can't understand, and that excuse is unacceptable. It's totally a skill issue, and a failure of it, and a laziness of these 'scientists'.
You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as well. Excel is not at fault here; the 'scientists' are.
Haha, you think scientists have control over what software their employer buys for them to work on.
There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.
Coming up next: people should still use C because writing insecure code is a “skill issue”.
Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?
Yes, but you can create a small macro to the change the formatting and assign that to your table. Your CSV should import deterministically. That's not impossible to do.
I saw a complete analysis engine written as an Excel file, which accepts and exports CSVs cleanly. It can be done.
As a person who does research and support researchers, I can't see the gap, sorry.
I understand some people don't know it's possible, and some don't care, but for any competent researcher, it's expected them to master the tools they use. This is esp. true for career researchers.
As a researcher you may have to learn how to carefully dig up skulls, raise rats, handle lasers, remember not to accidentally syringe yourself with viruses etc.
Getting cut by Excel seems like part of the job and at least is hopefully less life threatening than possibly blowing yourself up or giving yourself silicosis.
That said the problem with computers is that they're pervasive, they're a moving target and often it's a case of the blind leading the blind when it comes to research. And probably more and more research groups need dedicated computer technician resources who can centralize the required computer knowledge of keeping a research group running.
I'm not disputing that a competent computer user can do it. From the perspective of "this is what I would do if I was a scientist", you're totally correct.
But when you're writing guidelines for an entire field - as the article describes HGNC doing - you're catering to all researchers in that field: good, bad, and ugly. Plus technicians, editors, admins and anyone else that might handle the files. Given how hidden and unintuitive Excel's behaviour is here, I think what they're doing makes sense.
It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. This really just comes across as unproductive snark.
C/C++ developers could just use the tooling available to them to mitigate memory management issues, but they don't. Why should we trust anything programmers produce if this is the level of competence they have with their chosen tools?
In my current work, we deal with our user's national identity numbers quite frequently. This number is a 13 digit numerical number, that starts with your date of birth. So someone born on March 13 1989 will have a number start with 890913. People born in the aughts have "00" "01" "02" etc at the start of their ID number.
We need to frequently generate excel and csv reports that contain these numbers, and we need to ingest CSVs from other vendors that contain these numbers.
The /moment/ excel touches a CSV with these numbers in, it'll assume that column is a number, it'll strip out the preceding zeros, and it'll format the number in scientific notation. If you change the column's data type to text afterwards, then it's too late - the damage has been done and you've worst case lost data, best case you have a text column full of scientific notation numbers. You can't just open up the CSV, you need to import it, and very explicitly tell Excel how to handle this column, otherwise you mess things up.
Now, anywhere in the chain of people and other vendors sending and receiving these files, anyone who double clicks on that file and it opens up in excel and does not notice this very destructive action messes up our processes and causes unknown amounts of delays. It's the bane of my existence. This exact problem also crops up with phone numbers, where in many countries the number starts with a 0, or if it's an international number, a "+". Excel thinks the "+" makes the field a formula.
All of this because Excel is making assumptions and trying to "help", in the same way a 4 year old helps in the kitchen.
For this reason I find it incredibly frustrating to work with CSVs, because there is no "native" way for me to open the file and interact with the data in a native and intuitive way without running the risk of data being lost or edited without me noticing. I've resorted to importing the files into a local DB instance and using SQL to interact with the data, especially if the files are large.
You should never double click to open a csv file in excel. You should ALWAYS import the file into excel. Set your types during import. Many of the complaints about excel's csv handling in this thread are user errors.
You don't even need to do that. Just stick a single ' before any number/date and it will be treated as text.
The problems are when importing/exporting though. Even quoted numbers will lose the quote when exported to say, CSV. You need to explicitly save as quoted, and import quoted fields as text. The real issue is that these settings are not the default.
People who don't understand that excel is just a tool, which is useful but has flaws, arguing with people who don't understand that experts care about solutions not the tools, in which case it might be ok to compromise something unimportant, like the _name_ of something.
What these two sides have in common is the contempt for the actual experts for not building a tool that HN could build but wouldn't know how to use.
In this thread: guy pretends to think he's different from the rest, and is able to distinguish the "key issue" unlike everyone else - and of what purportedly doesn't matter, like the name of something.
I don't have to distinguish anything, I just have to the humility to listen to what the experts say, rather than a bunch of webdevs.
EDIT: Also, i'm not a biologist, but I see the same phenomenon in finance where a bunch of low pay webdevs nobodies refuse to make an effort to understand why Excel is popular, push their half backed solutions, and then are confused that nobody uses them, and frustrated when showed for the 10th time the feature set, that they don't really have the attention to detail to implement. Then i come on HN and realize "oh so _this_ is where these people come from".
It makes a difference because those that solve problems are well paid, whereas those than spend their time pushing their ideals of how things "should be", aren't.
At this point there must be enough people in the world relying on that behavior that you can't just change it.
Perhaps having an option somewhere could be done, but it would sit there among the hundreds of other options that so many people have no idea about. I think changing Excel's default behavior is a social challenge in itself, I'd be having a stomach ulcer for decades if I was Excel's prooduct manager.
Users enter text. That's how it always has been, because they use keyboard.
If format is set to General, Excel tries to make the best guess what is inputted and convert it to that value, so things like SUM work. That is very sensible behavior.
Your accountant doesn't want to set format and only then enter number. She has a job to do, so just do it for her.
Same thing for dates. This guessing algorithm has some edge behavior, but always happens. C# DateTime.TryParse("DEC12") will deduce date is 2012-12-01. Yay?
Do you want to keep it as text? Add ' as a first character (it's not considered a part of the string) or format cells to non-General format (e.g. Text) before entering a text.
Because it's garbage. I mean it. Try enter some text to search autofilter 100k rows on LO and Excel. In Excel, it's instant, in LO, I have to go make a coffee.
LO 10 seconds, while being frozen (command is likely sync).
Excel 2 seconds, while being responsive (command is likely async).
There is a saying that you only use 5% of Excel, but everyone uses different 5%. LO is full of stuff like that. Not to mention subtle differences from Excel that (likely) stems from Excel being having it's internal structure aligned with (barely documented) xlsx while LO is aligned with .ods.
LO is useful if you just want to make some simple stuff and you don't interact with rest of the world. If you need both, you are only asking for trouble.
The article has now been edited to note that Excel now offers a toggle to turn this feature off.
How long did the entire genetics community struggle with this problem before deciding to rename a bunch of genes, making their own data harder to search, without just...asking Microsoft to add a new toggle for their use case?
Has anyone ever had any luck asking a large organisation, such as Microsoft, for anything good or meaningful changes? Without a huge controversy or media outcry?
It has always been an option, however it's easy to miss it's happening when it happens to a really small subset of genes you are importing into an excel sheet. Most of the time people just don't pay attention, and excel does whatever is useful for most people (i.e. not biologists).
Worked in bioinformatics in the past, and I think this is just an issue with people not knowing it did happen. You have many new biologists joining the field every day, people are going to mess up if the standard excel option is conversion.
A consumer asking Microsoft for a feature? Thats like asking a mountain to build you a house. Ask all you want. The mountain is physically unable to comprehend your existance, let alone address your concerns.
I want an "always on top" feature for the default windows interface i am forced to use at work. Never going to happen. I want to remove all thise stupid references to 3d objects and VR that clutter some windows menues. Also never going to happen. A mountain doesnt have the ears needed to hear my requests.
About 20 years. And people have been begging Microsoft to add that toggle the entire time. It apparently took longer than naming the genes did. If you're having a problem, "just ask Microsoft" is about the worst possible plan to try and get it fixed. They don't GAF. And why should they? It's not like it cost them any money. Even if everybody stopped using Excel for data involving gene names they're not gonna stop using it entirely.
I'm just thinking the entire genetics community has to be gigantic, and they can't be the only ones with frustration about an auto-changing feature. Surely lots of somebodies in genetics know somebodies at Microsoft who might be arsed to surface a relatively simple toggle.
Well if the screwdriver sales guy takes your bosses bosses boss and their spouse out for a nice dinner, mysteriously leading to your org purchasing screwdrivers for everyone who is asking for a hammer to do their work then maybe the hypothetical craftsperson might start complaining.
A ridiculously large number of people have excel installations and exactly zero budget to get access to a less shit tool.
I'm really annoyed that even Open/LibreOffice copied this crap behaviour.
I try to avoid spreadsheets. I won't use them unless there's calculations to be done.
When I do use them, the first thing when I create a new sheet is to select all cells and set it to text. It's probably a bad habit, but it's the only way to avoid future frustration and mistakes.
One good solution rather than Excel is org-mode. See the original Google talk by org-mode creator [1]. At some point 30 minutes or so, he explains how to use org-mode as a spreadsheet.
Not so powerful as Excel, even though missing features might be trivially added. I haven't tried it myself but i want to really dive into it's functionality. Org-mode is really great at many things, spreadsheets might as well be one more.
Just format the range as text before pasting or importing the data (right-click -> format cells -> category = text). Text-formatted ranges don't convert input data.
The article also doesn't help saying that there is a toggle for implicit conversions. You want text, you preformat as text.
Just learn to use the damn thing before you bash it. Especially if it's your daily driver.
168 comments
[ 2.6 ms ] story [ 223 ms ] threadUpdate October 24th, 2023, 11:25AM ET: Microsoft has updated Excel on Windows and macOS, adding a toggle to turn off automatic data conversion. The original version of this article continues below.
I am not usually sarcastic, but can't help it, sorry. Also, I do think this is a very much a possibility.
Excel is the polar opposite of that.
Probably the general rule is simply, “you get more bugs when expectations don’t match reality”.
Example: 1. (null) This value can’t be null 2. (article) This value won’t be converted into a date 3. (misc) This API can be called frequently
That's too vague to be useful. You might as well say "you get bugs when you write wrong code". Whereas framing it as a lack of type safety points towards an actionable solution.
People choose these system with loose conversions over and over again because it's an advantage up to a point. Of course it becomes a liability when expanded too much and used in now critical systems, but IMHO the tradeoff usually ends as a net positive. Which is why, over and over we build more of these systems, and they fare better in the market than the stricter ones.
There doesn't have to be a tradeoff if you make it optional.
- people love to export as CSV. And I kinda understand them, csv looks simple enough, what could go wrong ?
- other spreadsheet softs need to recognize the same field and act the same way. Google Sheets, LibreOffice, KingSoft's one etc. Not counting old office versions that aren't updated anymore, because the owner doesn't want to move into the cloud.
- the flag needs to stay when moving sheets around, copy to another set, etc.
That could still be better than nothing. But could still lead to catastrophic loses as people get used to it working most of the time, there's no perfect solution.
This would also offer the path for changing the default over time to safe by default. There will be more spreadsheets created in the future than exist now.
They already added an option in the settings last year to deal with this specific issue. Their solution isn't perfect either, they made the tradeoff to optimize for up to date excel users.
Also keep in mind that the first person doesn't necessarily use excel at all. The file might start and end as a text file or TSV.
Right Click > Format Cells > Text.
Set the data type of the column and Excel will not fiddle with it. Let it stay implicit and Excel will to guess. This change is saved in the file. The article only slightly touches on this: converting to CSV and losing all the Excel formatting and then opening it in Excel again.
Just because they have something they call CSV import or export doesn't mean the feature actually works.
An option was added last year [0] the way you propose it, but until then users already had the option to mark the columns as text. The same way we have to deal with any data where auto detection might be problematic: explicitely set a type, and potentially a format.
But of course that's bothersome, sometime people forget to do it, sometimes they don't even know how to properly do it etc. And some of these people will still probably forget to set the option, and there are still edge cases where it can fail (macros are cited)
[0] https://www.theverge.com/2023/10/21/23926585/microsoft-excel...
This only works on Excel files though, which save a type for columns/cells. With CSV files, Excel would just auto-format them. I guess you could save everything as xls/xlsx, but I shouldn't have to use a vendors file format because they're threatening to corrupt my data if I don't.
I don't want Excel to take over the file association for .json but I do sometimes wish Excel would just invent a file extension like .xljson that loads JSON so that at least you could trivially rename a JSON file to .xljson and get working double-click for your business users but a file format (with optional schema support even) that's a more modern and saner de facto standard than CSV.
I think that one dumb trick would improve everyone's productivity a great deal.
If the excel developers cared about CSV support they would have added more settings to the CSV import tool that let you specify everything. Every single data base tool that lets you import CSV files has worked better than Excel. It simply means Excel wasn't built to import CSV files. Tough luck.
.xlsx file extension is well supported by many open source libraries. If you insist on working with Excel then you should use the appropriate file format, which is .xlsx. I do that all the time and it just works.
or they dont know any better.
That's a hell of a claim to try and demonstrate when its initial advantage is far from obvious to begin with.
A) I have no clue where you got this from. It seems like a convenient assumption, but not one that's easily verified, especially when folks are introduced to the dangers of such an approach.
B) Giving people control over how the data is interpreted outside of the default interpretation seems like a no-brainer. I don't really rely on default interpretation of anything, especially in the context of spreadsheets given how eager they are to interpret data in unexpected ways, but based on this thread it seems like it's rather difficult to turn off the date conversion behavior in excel.
C) I was replying to a comment about implicit conversions, which is far larger than just the context of excel spreadsheets.
As a workflow, if every time you create a new spreadsheet you first define all the column types and constraints, you're in the world you're describing. Passing around that spreadsheet would also keep these settings as long as it's not converted and everyone's version is compatible.
That means you get the best of both worlds: people who care about strict types can be as strict as they want, other people can benefit from auto conversions.
Is this some kind of joke? Ever used Excel?
The world is full of antipatterns that people implement with little, no, or flawed analysis. They do not fare better in the market, they propagate the same way entropy does.
They’re good for professional and hobby code golf though.
A certain CEO says that we won't need programming languages because you will be able to program AI systems with natural language. Well, the problem with natural language is not that we do not have the tech, it is that natural language is too imprecise. People will, again, end up being dissatisfied by the output of AI systems and blame it on the tech, while the problem just remains that as humans we kinda suck at being explicit about what we want.
To use the example from the article: spreadsheets could use strongly-typed columns, but guess the column type automatically based on the values entered. When things go right, it'd be just as easy as today, but if things go wrong, you always have an escape hatch: with a little more work, you can manually specify the column type. Problem solved. That gives you the increased productivity you want for the happy case, without the frustration of trying to find a workaround for the corner case. This kind of approach needs more architecture work at the beginning of a project, but it's almost always feasible.
That being said, I think your frustration is warranted, although I think it's much more pervasive than tech. At its core, I think the problem is that, in your example, the CEO and the programmer are having two different conversations without really realizing it. The CEO wants a problem solved, but the programmer wants the tools to solve the problem themselves. The CEO thinks that ML should be able to solve their problem, but the programmer sees that ML as a tool has serious limitations, and won't help solve under-specification. And then they both end up talking past each other.
It seems that a lot of (at least western society) is built around the idea that when someone asks for help, what they "really" want is, metaphorically, to be given a fish for dinner, instead of learning how to fish. That's the CEO in your example; the presumption is "I want help" means "solve my problem", not "make it easy for me to solve my problem myself". It's one of my biggest frustrations, because it inherently robs you of agency and in many cases can be downright condescending -- for example, when I was working as a mechanical engineer, there were shop floor guys ignoring the tolerances I specified on my drawings because they assumed I was over-specifying them, because they lacked the context of the rest of the design, and weren't willing to sit down to hear the bigger picture. But when I had to send the parts back for re-work, they acted like it was my fault.
Anyways, this is all a really long-winded way of saying, I think this is largely a social problem, not a technological one. But I don't think it's inherent in humans; I think this is something we've learned as a part of our culture.
I agree with the implicit part, but not with the cynicism. Most human-to-human communication has implicit details, simply because there would otherwise be too many "obvious" details that need to be communicated. Suffice to say, humans tend to misunderstand each other when different assumptions are held and things can go horribly wrong.
People like mathematicians and programmers, who are used to formal thinking, know that you have to be rigorous in your statements. We understand that details and edge cases matter. And it can be frustrating when other people don't understand that you need to look at the full picture instead of a very rough sketch.
> I've been annoyed by how people do not want to be specific, but then rant about how someone did not understand exactly what they wanted.
I'm also annoyed when this happens. But then I say: This is what you requested. I can only work with the information that you provide to me.
I think it depends on the attitude of the person giving feedback. I generally find that digging into why this isn't what the user wanted is more illuminating. Tell me a story and I'll gain context and get more of the implicit details right going forward.
This breaks down when someone starts talking outside of that shared understanding (say, on the internet) but do not realize more context is now needed.
If only there was a trade, a group of educated professionals with specific training in the use of language to describe a desired outcome. We could call them "language engineers" and regulated them via state associations meant to protect the public from charlatans.
It's not that they'd rather.
They understand that a trillion dollar corporation cannot supply the most basic tool they need for work, despite trying for 40 years. They just can't effect change. The real problem behind it all is that they allow themselves to be bullied. Despite holding multiple PhDs they cannot actually exercise rational choice.
The scientists work in an organisation where a suit called Kevin and a middle aged lady in a cardigan called Mavis choose what software they use. Kevin and Mavis are "IT experts".
Kevin and Mavis choose Microsoft because they think it is "reputable", and they like the colours, and because HR have had "Microsoft Office" as a mandatory hiring requirement since 1990.
Mostly though, they don't want to upset Colin who is head of IT, Colin still thinks Windows XP was the best thing ever and goes a funny shade of red when anyone mentions Linux (or anything else he doesn't understand). Bless him, he's close to retirement anyway.
Meanwhile Tom the MD, whose "door is always open" invites the scientists and engineers into a "consultation". They tell him that Microsoft and Google offer 1990s solutions to twenty-first century needs, that their products are unfit for purpose, unstable and insecure too. They even produce a report of all the software they need along with suppliers, budgets and installation guides. Colin has gone a very worrying shade of purple.
Six months later the square root of fuck-all has happened. An uncomfortale quiet hangs in the air. Someone nobody ever heard of called Isabella, who actually works for "strategy and brand" in the parent company - and is married to some Microsoft guy - over-rode the whole thing.
Tom mumbles something about preferred suppliers and compliance. Kevin and Mavis assure the scientists that Microsoft are going to fix everything in the next update. Colin is restored to a smug pink. Science will have to wait.
Big Tech is a complex cultural problem.
I’d guess that this is becoming less and less common, it pretty much sums up the places I worked at during the late 90’s but mot so much anymore.
In mine (academic) they fired Mavis, Kevin and Colin (a week before his retirement party) and moved the lot into "the cloud". What was once the "campus network" vanished and became a wholly managed outpost of Microsoft.
Same - I'm still waiting for Microsoft to make me a decent set of drill bits. Can't believe a trillion dollar corporation cannot supply the most basic tool I need for work!
The real story for me is that people are lazy and rather deal with the annoyances they already know and that are one click away than learn new tools.
Circa 2000 I worked in a place with such a head of IT. After some effort I managed to get my work done by bringing in my own Linux laptop. But, connecting it to the network was strictly prohibited ("everyone knows that Linux is insecure"), and data had to be transferred by floppy disk to/from the rather unreliable networked Windows PC shared by me and two other scientists in the same office. This PC used to crash after each user logged out and required a reboot pretty much every time.
Perhaps there is a minority who thinks that automatic data conversion is unsound, and a majority who thinks it's 'pragmatic', and will let them move faster.
Instead, the path of least resistance is to change the name of all known genes that cause problems with Excel. Biologist are changing and adding names all the time, so it's not like this is new. E.g. https://www.uniprot.org/uniprotkb/Q04609/entry#names_and_tax... shows you a gene with 5 names.
Nothing will ever replace spreadsheets, because the tech industry no longer believes in making products for power users. In modern thinking, any form of user training is an indication your product is too complicated, and any feature only used by 5% of users should be removed.
The idea of a product where users have to type in incantations like =VLOOKUP(A1,C2:D16,2,False) which they're just supposed to somehow know? A product that's essentially unusable on mobile? A product with features like 'Pivot Table' that only 1% of users ever use, and yet it's a vital core feature?
It's absurd. Only in 1970 could someone have conceived of such a thing.
Even if you're conscientious enough to handle this issue, the automatic conversion could come and cause you problems from anyone you share data with: medical teams, grad students, administrators...
[1] Source: POUMA
I guess many people here will point out that they should use other tools. I think we should help them in any way.
They could also prefix every gene with Gn so it cannot be confused with other acronyms (e.g., GnDEC12). It not only breaks parsing by computers but also by humans who might read offensive words into it. Depending on the structure of data this prefix could even be compressed with 0 bits.
I think this is sensible actually. Certainly better than the inverse, Latin numbers and Arabic letters. I'm not sure why they put it in this way (with the ethnic reference), but they are really just saying about putting it letters and numbers that people use and would be able to spell out to each other.
I think your idea of a prefix though is great - simple.
We can try to (and do) create tools, but it's not as simple as a table editor or a simple CRUD application. Scenarios has many peculiarities and edge cases.
In case of software, rather rarely people die and when software is being done on something human critical (a friend of mine works on pacemakers), there a great deal of care being done.
So basically it's a wide range from DIY using a cheap wrench to person rotating a torque calibrated wrench on a rocket. Because just like the hammer, there is a wide range of use cases, some critical (e.g. pacemaker), some mundane (e.g. ecommerce).
Planes and elevators always have a risk of crashing and killing humans. And it did. Software doesn't.
I'd say we have a better understanding of which parts are necessarily / accidentally / capriciously / deceptively hard.
And it's certainly subjective. E.g., Python's dynamism (typing, etc.) really trips me up, but others feel the same about strong static typing.
The proposed solution is also not really a solution in that it still allows for one of the main issues: random people shouldn't be responsible for creating names for things that should have unique IDs. Which is why things like ensembl IDs exist.
https://news.ycombinator.com/item?id=24070385
You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as well. Excel is not at fault here; the 'scientists' are.
There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.
Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?
I saw a complete analysis engine written as an Excel file, which accepts and exports CSVs cleanly. It can be done.
I understand some people don't know it's possible, and some don't care, but for any competent researcher, it's expected them to master the tools they use. This is esp. true for career researchers.
As a researcher you may have to learn how to carefully dig up skulls, raise rats, handle lasers, remember not to accidentally syringe yourself with viruses etc.
Getting cut by Excel seems like part of the job and at least is hopefully less life threatening than possibly blowing yourself up or giving yourself silicosis.
That said the problem with computers is that they're pervasive, they're a moving target and often it's a case of the blind leading the blind when it comes to research. And probably more and more research groups need dedicated computer technician resources who can centralize the required computer knowledge of keeping a research group running.
But when you're writing guidelines for an entire field - as the article describes HGNC doing - you're catering to all researchers in that field: good, bad, and ugly. Plus technicians, editors, admins and anyone else that might handle the files. Given how hidden and unintuitive Excel's behaviour is here, I think what they're doing makes sense.
"=""Data Here"""
will always be treated as a string. This is also supported by Sheets, apparently.
Sounds like a joke, but it isn't. I know scientists who think exactly like that.
I'd say more unreasonable is for you to mischaracterise this comment as you have done.
In my current work, we deal with our user's national identity numbers quite frequently. This number is a 13 digit numerical number, that starts with your date of birth. So someone born on March 13 1989 will have a number start with 890913. People born in the aughts have "00" "01" "02" etc at the start of their ID number.
We need to frequently generate excel and csv reports that contain these numbers, and we need to ingest CSVs from other vendors that contain these numbers.
The /moment/ excel touches a CSV with these numbers in, it'll assume that column is a number, it'll strip out the preceding zeros, and it'll format the number in scientific notation. If you change the column's data type to text afterwards, then it's too late - the damage has been done and you've worst case lost data, best case you have a text column full of scientific notation numbers. You can't just open up the CSV, you need to import it, and very explicitly tell Excel how to handle this column, otherwise you mess things up.
Now, anywhere in the chain of people and other vendors sending and receiving these files, anyone who double clicks on that file and it opens up in excel and does not notice this very destructive action messes up our processes and causes unknown amounts of delays. It's the bane of my existence. This exact problem also crops up with phone numbers, where in many countries the number starts with a 0, or if it's an international number, a "+". Excel thinks the "+" makes the field a formula.
All of this because Excel is making assumptions and trying to "help", in the same way a 4 year old helps in the kitchen.
For this reason I find it incredibly frustrating to work with CSVs, because there is no "native" way for me to open the file and interact with the data in a native and intuitive way without running the risk of data being lost or edited without me noticing. I've resorted to importing the files into a local DB instance and using SQL to interact with the data, especially if the files are large.
The problems are when importing/exporting though. Even quoted numbers will lose the quote when exported to say, CSV. You need to explicitly save as quoted, and import quoted fields as text. The real issue is that these settings are not the default.
In LibreOffice (Save/Open CSV): https://imgur.com/a/ved7wgA
People who don't understand that excel is just a tool, which is useful but has flaws, arguing with people who don't understand that experts care about solutions not the tools, in which case it might be ok to compromise something unimportant, like the _name_ of something.
What these two sides have in common is the contempt for the actual experts for not building a tool that HN could build but wouldn't know how to use.
Peak HN.
EDIT: Also, i'm not a biologist, but I see the same phenomenon in finance where a bunch of low pay webdevs nobodies refuse to make an effort to understand why Excel is popular, push their half backed solutions, and then are confused that nobody uses them, and frustrated when showed for the 10th time the feature set, that they don't really have the attention to detail to implement. Then i come on HN and realize "oh so _this_ is where these people come from".
If you want to sound reasonable, at least try to hide your contempt.
Better luck next time.
I thought they fixed that bug. Oh wait, this is Microsoft. /s
Perhaps having an option somewhere could be done, but it would sit there among the hundreds of other options that so many people have no idea about. I think changing Excel's default behavior is a social challenge in itself, I'd be having a stomach ulcer for decades if I was Excel's prooduct manager.
Maybe it depends on your use case, but why it converts almost everything to dates ? Why not to numbers ?
Excel also tries to automatically convert numbers as well.
If format is set to General, Excel tries to make the best guess what is inputted and convert it to that value, so things like SUM work. That is very sensible behavior.
Your accountant doesn't want to set format and only then enter number. She has a job to do, so just do it for her.
Same thing for dates. This guessing algorithm has some edge behavior, but always happens. C# DateTime.TryParse("DEC12") will deduce date is 2012-12-01. Yay?
Do you want to keep it as text? Add ' as a first character (it's not considered a part of the string) or format cells to non-General format (e.g. Text) before entering a text.
LO 10 seconds, while being frozen (command is likely sync). Excel 2 seconds, while being responsive (command is likely async).
There is a saying that you only use 5% of Excel, but everyone uses different 5%. LO is full of stuff like that. Not to mention subtle differences from Excel that (likely) stems from Excel being having it's internal structure aligned with (barely documented) xlsx while LO is aligned with .ods.
LO is useful if you just want to make some simple stuff and you don't interact with rest of the world. If you need both, you are only asking for trouble.
How long did the entire genetics community struggle with this problem before deciding to rename a bunch of genes, making their own data harder to search, without just...asking Microsoft to add a new toggle for their use case?
A few large customers can move things on specific products if they're able to escalate their support ticket into talking to the right developer
Worked in bioinformatics in the past, and I think this is just an issue with people not knowing it did happen. You have many new biologists joining the field every day, people are going to mess up if the standard excel option is conversion.
I want an "always on top" feature for the default windows interface i am forced to use at work. Never going to happen. I want to remove all thise stupid references to 3d objects and VR that clutter some windows menues. Also never going to happen. A mountain doesnt have the ears needed to hear my requests.
I went searching for it, found it interesting and so did others.
If excel (which was absolutely not designed for genetic research) is bad for genetic research purposes why blame microsoft.
Its like a craft man complaining that a screwdriver is a bad tool if your job is to put in nails.
A ridiculously large number of people have excel installations and exactly zero budget to get access to a less shit tool.
I try to avoid spreadsheets. I won't use them unless there's calculations to be done.
When I do use them, the first thing when I create a new sheet is to select all cells and set it to text. It's probably a bad habit, but it's the only way to avoid future frustration and mistakes.
Not a problem for someone who understands the issue, but it is a slow/confusing way to open files.
The real problem is the default behaviour of double-clicking a CSV in Explorer and destroying the contents.
Not so powerful as Excel, even though missing features might be trivially added. I haven't tried it myself but i want to really dive into it's functionality. Org-mode is really great at many things, spreadsheets might as well be one more.
[1]https://www.youtube.com/watch?v=oJTwQvgfgMM
I misread this as "... the typesetting concept.".
I immediately feared someone adding LaTeX support to Excel cells.
But now I'm thinking "hmmmm..."
More discussion then: https://news.ycombinator.com/item?id=24070385
Just format the range as text before pasting or importing the data (right-click -> format cells -> category = text). Text-formatted ranges don't convert input data.
The article also doesn't help saying that there is a toggle for implicit conversions. You want text, you preformat as text.
Just learn to use the damn thing before you bash it. Especially if it's your daily driver.