Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)
theverge.com
theverge.com
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.
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.
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.
I am not usually sarcastic, but can't help it, sorry. Also, I do think this is a very much a possibility.
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.
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
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
I went searching for it, found it interesting and so did others.
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.
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.
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.
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.
More discussion then: https://news.ycombinator.com/item?id=24070385
I misread this as "... the typesetting concept.".
I immediately feared someone adding LaTeX support to Excel cells.
But now I'm thinking "hmmmm..."
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.
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.
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.