Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates

by takiwatanga • 4 years ago

191 138 4 years ago

Semaphor• 4 years ago

At the time (506 comments): https://news.ycombinator.com/item?id=24070385

mdb312• 4 years ago

PSA: if you use Excel as intended, you would just go File/New/Blank Worksheet, switch to the Data tab, then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue.

But yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Excel, MSFT and The World in general) on social media is much, much more fun...

tidenly• 4 years ago

In Japan everyone passes around CSVs in shift-jis still for exactly this reason - the default opening action of Excel with CSVs.

Its easy to say its people being dumb, but at this point I really wish excel just wasn't so confident in itself and actually asked you during the default open operation what you want to do.

netsharc• 4 years ago

It uses Windows-1252 in Europe...

fomine32• 4 years ago

Actually UTF-8 WITH BOM csv file also can be read/written like that since Excel 2016, but of course average users aren't knowledgeable as like programmers.

imtringued2• 4 years ago

then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue.

Something people do not understand is that type information in CSV files is conveyed out of band. The application that is reading the CSV file must know the datatype which effectively turns each CSV file into an application specific format.

hobofan• 4 years ago

which effectively turns each CSV file into an application specific format

Thanks for writing that! I don't think I've seen the reason for my (partial) dislike of CSV put into words that clearly before.

hoseja• 4 years ago

The easy action should be safe by default.

mdb312• 4 years ago

Yes, but without inventing a time machine to go back to 1985 or so (at which time the safe option did not exist, so better invent temporal code backporting as well), that's not a viable option.

Any change to Excel's 'open a worksheet' logic would break so many workflows it's just not funny anymore. I'm not kidding if I say I suspect it would significantly impact several countries' GDP for a while.

Even the (sometimes comically inadequate) heuristics that Excel uses to auto-determine field types can't be updated, for very similar reasons. Backwards-compatibility is... interesting...

Ekaros• 4 years ago

Easy action should be what majority expects.

I wonder what is the ratio of people dealing with dates versus genes is... Probably very substantial on favour to those who deal with dates. So things just to work for them is likely better option.

jmchuster2• 4 years ago

Similarly, if your last name is "Null", sometimes it's just easier to just change your last name.

hamasho• 4 years ago

For reference [0].

It should be tough to have such a name. The most important systems like tax/insurance/airline booking are often the most unkind systems. If you're not familiar with computers, it's almost impossible to imagine the potential cause of problems is your name.

[0] https://www.bbc.com/future/article/20160325-the-names-that-b... > These unlucky people have names that break computers

jmchuster2• 4 years ago

I had a PM who would get deleted out of the company directory every 6 months. She resolved the issue by getting married.

jdrc• 4 years ago

To Nan

hpcjoe2• 4 years ago

This has got a "Bobby Tables"[1] vibe to it. While I personally do not like the concept of adjusting data to fit a tool, as others have pointed out, Excel, for better or worse, is ubiquitous. To the obvious detriment of genomics. And likely others.

Any tool that seeks to replace it would need to be as easy to use, as flexible. And preferably fewer bugs.

The problem is that the paradigm for spreadsheets works well for many projects, even if the implementations are crap.

[1] https://www.explainxkcd.com/wiki/images/5/5f/exploits_of_a_m...

hoosieree2• 4 years ago

Don't change it to "None", though. That's just a curse for your grandchildren to deal with when Python eventually becomes the new old legacy system.

"Nil" is probably safe, because the lisp curse will actually work in your favor.

fomine32• 4 years ago

I wonder would Norway change their country code to avoid confusing "No"

imtringued2• 4 years ago

Even then, a scientist might fix their data but export it as a CSV file without saving the formatting.

Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.

CSV is such a bad format because it's not even a standard, there is RFC4180 but most people have never heard of it. CSV is complicated enough that anyone who thinks they can implement it will get it wrong on their first attempt but simple enough that people believe they can implement it on their first attempt.

rini173• 4 years ago

Opening CSVs in Excel almost never worked for me. Good thing there's LibreOffice Calc with spiffy import dialog with all the csv options/variants where you can preview the result.

joenathanone3• 4 years ago

Excel has exactly the same functionality.

jasode3• 4 years ago

Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.

You're being hyperbolic and your .xlsx advice doesn't apply to other systems we don't control that only offer .csv files.

Examples... my credit card website and Ebay only offer csv downloads of data. I use MS Excel to import those csv files all the time and it works well enough.

Yes, I'm aware of potential data-conversion flaws with importing csv files. (My previous comment: https://news.ycombinator.com/item?id=25017116 )

All those caveats with csv are irrelevant when the system that has the data people want only offers csv. Using Excel's feature to import csv -- and being aware of the format dangers -- is more practical than manually retyping all the data from scratch.

nulbyte• 4 years ago

Microsoft Excel may be fleeting, but human genes will be around for as long as we are. It’s best to give them names that work.

This is the crux of the issue. Yes, Excel could do better with support for CSV. (OpenOffice and LibreOffice habdle this better, for example.) But no, genetics is not some niche use-case for this better support. It's just one example of many. I do analytics of another sort for a bank, and we run into this problem all the time with those pesky 16-digit account numbers.

But, while Microsoft might do well to make changes to address their poor implementation, someone somewhere will implement the next best thing and screw it up, too.

nicoburns4• 4 years ago

I really wish there was a better interchange format for tabular data. Criteria:

- Plain text file

- Supports formatting, cell types, etc

- Does not support full spreadsheet features like formulas

olau• 4 years ago

UTF-8 CSV with a few HTML tags for formatting?

You could call it CHU.

Now you just need to make Excel accept it.

mcv4• 4 years ago

I've been thinking about a project exactly to replace this sort of Excel misuse. The frontend should be about as Excel-like as possible, where you can just easily type (or import) as many rows and columns you like, but without the formulas and other real spreadsheet calculations. Instead, make it easy to add data types, validation for those types, foreign keys, etc, and store the whole thing in a database.

cyxxon• 4 years ago

I could even imagine some formula support, but only strongly typed, i.e. SUM only on numerical cells. For a small overview this would still be fine.

zokier• 4 years ago

Parquet is not plain text (but that is an oxymoron anyways), but otherwise afaik it is pretty solid data interchange format supported by a number of tools already. It doesn't have native formatting system, but it does allow arbitrary key-value metadata on columns so surely something could be cooked up with that.

mcv4• 4 years ago

The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. And of course it should be better at data validation; you should be able to tell it whether a certain column is a date or not, and Excel shouldn't be jumping to stupid conclusions about it, but it does, and that means you need to be careful and not use Excel for something like that.

Excel misuse is sadly rampant and one of my main frustrations at my previous project. Excel is popular for this sort of misuse because when you open it, it presents you with an empty table and you can immediately start typing. This invites tabular data. But Excel inevitably fucks up because it lacks proper data types, data validation, or foreign keys, and loves making assumptions about what you mean. This makes it a terrible and harmful choice for any sort of serious data. I even proposed a new project for an Excel-like frontend backed by a database exactly for these sort of situations. Because I do get what attracts people to Excel for this sort of thing. And most people don't realise what a terrible choice it is.

prepend4• 4 years ago

There’s not really a better tool that I’ve found. Excel is ubiquitous and pretty easy to use.

And there aren’t good “data browsers” that have as low a learning curve.

I’ve been especially looking for a JSON browser/editor since excel doesn’t do that well and I’m unsuccessful trying to explain how to use basic text editors to people who can’t do basic functions like open files that aren’t associated with a program.

tanbog10• 4 years ago

"And there aren’t good “data browsers” that have as low a learning curve."

If someone is trained to the point of working on genetic data at this level, should they not also have been trained to a reasonable level in domain appropriate software and tools?

jasode3• 4 years ago

The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns.

I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing.

And even though I also know programming tools like C++ Qt and C# WinFroms to slap in front of databases, starting with a blank Excel grid is faster and easier than wiring up a datagrid UI control to a database and compiling an app.

If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.

The scenario of "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!" -- is not easy in other tools that are not spreadsheets.

People (including programmers skilled in databases) constantly "misuse" Excel because it's the most practical way to get work done compared to the friction of alternative tools.

JustAQ• 4 years ago

What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access even allows you to create your own UI forms for a database.

You don't need to write your own GUI for databases. Loads exists.

dotancohen10• 4 years ago

  > The problem is that databases do not include a GUI for data viewing.

Let's say that I have PyCharm open right now, I'm importing Qt and Sqlite. How would you like your GUI to function? Seriously, write for me a detailed spec and a detailed workflow, and I'll get to work on it already. My Gmail username is the same as my HN username if you'd prefer to collaborate offline.

This invite goes for anybody else who [ab]uses Excel even though they are versed in SQL.

Closi2• 4 years ago

If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.

The alternative is to load the sqlite DB into Excel via PowerQuery and then share the file, which will maintain type safety of all columns through the excel data model.

This provides much better GUI data manipulation too, as you can define relationships between the data in the model e.t.c.

The problem is I would guess less than 1% of Excel users actually understand this functionality, but it is absolutely core to doing proper analysis in excel (not saying you don't use it - you probably do - but lots of users don't!).

zozbot2342• 4 years ago

An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.

What's missing from a GUI like MS Access or Libreoffice Base? In your example, you can input your comments/annotations (even something like "highlight these rows as having problems") in a separate table without touching the original data, then use a database query/view to look at both seamlessly. It's only a bit more involved than raw editing on a spreadsheet, and it inherently avoids accidental data loss/corruption.

charlieyu12• 4 years ago

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible

gompertz2• 4 years ago

I think the big point missing in all the comments that suggest linking to the db file and distributing is that it's read-only. Perhaps this is the intent too, but often Excel books are shared for other people's input /additions/ corrections to data. When it's linked to a backend database that's no longer trivial. Whereas just using base rows/ columns it's immediately read & write access.

mcv4• 4 years ago

The problem is that databases do not include a GUI for data viewing.

Exactly. It baffles me that such a tool still doesn't exist (though elsewhere someone claimed that MS Access is like this; I'm not familiar with it).

Keeping this in the cloud, with a web-based Excel-like interface, where you can share it with anyone you choose, but keep a single source of truth, I think that would be incredibly useful and solve this Excel-misuse issue.

Aloha4• 4 years ago

I wish there were modern record oriented databases and processing tools - I've used some of the tools on zOS and that whole mindset of "even the system tools are record aware" so you can use the equivalent of basic UNIX tools to do reporting and data analysis, is pretty powerful.

We could build something that is both as powerful as excel, and easier to use, yet designed for average users to manipulate very large data sets - we just have to chose to do that.

Python has footguns in it, PHP has a confusing standard library, perl is complex, and bash is missing some of the data processing primitives needed.

keithalewis2• 4 years ago

If you know Excel and C++ then you might like https://github.com/xlladdins/xll. It is something I wrote to embed C++ in Excel.

pixl97• 4 years ago

MS Access also isn't really an option since

Actually I would change that from your answer to

"Access didn't come with the cheapest version of Office"

Symbiote• 4 years ago

Excel-like frontend backed by a database

Microsoft Excel itself can connect to databases (MS SQL, PostgreSQL, Oracle, etc). I think you need to have set up the database table(s) elsewhere.

Microsoft Access provides a GUI to any database (including PostgreSQL etc), and (IIRC) the ability to create new tables. It allows editing in a table-like way (rows are locked during editing, if the database supports this), or in a form-like way. Queries can be made in a text/SQL-like way, with a GUI, or in a form-like way. For all this, it supports data types (number, date, lookup-from-another-table etc).

At my previous job, the research scientists had several tools built in Access. It was a very fast way to develop a UI, and the IT industry is less efficient now this is no longer commonly known or understood.

I think Access is Microsoft's best software. It's a very powerful tool, but was also very accessible. You can drag-and-drop to create multi-table/view queries without understanding SQL, then switch the mode and see the SQL. Once you have the query, you can drag-and-drop to create a form (to edit the data) or a report (to format each row as a page to print out etc).

civilized• 4 years ago

Monday: "Excel is terrible, use my new solution, it'll blow your socks off!"

Tuesday: New solution is released to much user enthusiasm

Thursday: Users don't get it and are already back in Excel

pelasaco4• 4 years ago

classical situation where the tool defines the workflow and not the other way around..

thesuitonym• 4 years ago

It's a real shame, because Excel is great for taking quick notes when you have data, but you're not yet sure how you'll want to organize it, or what you'll want to do with it long term. You start by just jotting down numbers. But then you have everything there an an easy to digest form. You can create pivot tables to sort the data. Graphing is easy. The data gets stuck there because it's so easy to use and it's already there.

listenallyall• 4 years ago

you should be able to tell it whether a certain column is a date or not

You can, very easily

max5993• 4 years ago

you should be able to tell it whether a certain column is a date or not,

Not only is this possible, but the tool to do it is located in best location (large menu, center of the screen on the first ribbon tab). It take literally 2 clicks to do it once your cells or columns are selected. The only problem with this tool is that you have to use before copying your data and it can be frustrating if you forget to do it. If you want to import a file instead of copy-pasting the data, it's only like 1 or 2 extra clicks to set the data type for a column during the import.

And btw, the CSV format was intentionally designed to NOT have type information imbedded in the file itself. The application that is reading the CSV file must know the datatype for each column. For a versatile tool like excel, they is no perfect way to implement it and there will always be a fraction of users of have to override the choices made by by the software. For advanced users who use it everyday, you learn very quickly if the type of data you are normally working with will require you to force it or if excel will understand it correctly.

It looks to me like almost all of the anti-Excel comments on HN (including yours) are made by people who never or extremely rarely uses it and don't know what it can or can't do. It's typical that most if not all of the "missing features" listed by people on NH have been part of excel for at least a decades.

wil421• 4 years ago

I love it when excel decides a bunch of digits is a date or decides to remove leading 0000s from a very important number that needs 0000s. Or maybe it wants to make a bunch of digits into scientific notation. I would prefer if excel left my cells alone or just asked what I wanted to do.

loufe• 4 years ago

Good initiative, but there are hundreds of used languages, I hope they put a bit more effort into it than just testing the English dates cases. I caught one bad example, for any French speakers, in the article itself:

and MARS tweaked to MARS1.

joshvm• 4 years ago

Plenty of gene names are partial/full backronyms that don't really bear any relation to their function. For example the R in MARCH stands for "RING Finger", RING means "Really Interesting New Gene" [0]; finger here is some substructure that I guess looks superficially like a finger on the protein. That's an astronomy-worthy acronym if ever there was one. You could probably avoid a lot of this if the naming conventions avoided using protected words, but then they're a lot less catchy and easy to remember. Also I don't think Excel has a list, but it might be useful to make one.

[0] https://pubmed.ncbi.nlm.nih.gov/24928685/

chipsa• 4 years ago

Part of the problem, highlighted in the article: Users don't set the data type of the column off of "General". So it tries to figure out the data type on entry. Setting the column to use a specific type ("Text"), keeps it from trying to use heuristics to figure out that something is a date.

Something more recent is the introduction into Excel of Power Query, which lets you import a CSV and apply arbitrary transformations (such as applying a type), before it hits the workbook, so if you need to pull in a CSV, you can do so, and it will always be imported the same way.

washadjeffmad• 4 years ago

Does anyone here use Excel in any capacity on production data in the biosciences? If so, did you go to college for the biosciences, or are you supplying work using a tool you learned under another discipline?

I'm always fascinated to learn how Excel makes its way into unusual places.

lofatdairy• 4 years ago

Not in biotech, but have worked with people who use a tremendous amount of excel, especially in biology. A lot of the people still using Excel either came before bioinformatics libraries got really really good, had to basically teach themselves how to use a computer to deal with bio data, worked in a lab that had protocols in place for data that used Excel, and/or haven't gotten around to teaching themselves R. You have to keep in mind that Excel is extremely good at being a visual representation of data. R, Python (only really adopted in recent years), and Matlab (which I don't see too often in bio) are all excellent for manipulating the same data as Excel, but if you wanted to quickly scroll through your RNA sequencing counts, Excel provides a fast, (almost) out-of-the box way to get a "big picture" view of your data (I haven't met anyone doing bioinformatics in SAS or STATA, but I know people who have used those software in academia for other fields).

Even if no one is directly manipulating the data in Excel, if you don't import the data correctly, or forget to _not_ save the data, you'll end up fucking it with Excel's auto-formatting. These subtleties lead to things as mentioned in the article, but aren't things that the ordinary person learns except through mistakes. Nobody is born knowing what tools to use or how to use them, but Excel is one of the first pieces of software that deal with data for a lot of people, and as such one of the first things they turn to when faced with a new problem.

pelasaco4• 4 years ago

Looks more like the scientists are choosing the wrong tool to do their job. Sure you can use excel, to get a sense of the data, but for god sake, there are better tools for that - or even contribute to an existing open source one to do what you need. I'm quite sure scientists could get some grants to hire actual software engineers to come up with a better solution, based on the open source options existing there.

hpcjoe2• 4 years ago

I'm quite sure scientists could get some grants to hire actual software engineers to come up with a better solution

Uh ... no. Grants rarely ever support SWE outside of a core application. No granting agency would support writing a new open source tool that effectively replicates what is available in market today.

You are right in that genomic scientists chose poorly here. Excel isn't the right tool for the job, but there are very few options that could work, and the others required some assembly ... which they couldn't get money to fund. And the other potential solutions were not ubiquitous.

For them, renaming genes is the easier solution than switching workflows to new (to be assembled) tooling. Remember, scientists are people too ... they will opt to take paths of lower resistance even when they are suboptimal.

pelasaco4• 4 years ago

The error is to assume that we have a tool available to do it, aka Excel. If they assume that there is no tool available, then a tool have to be found/developed.

AviationAtom• 4 years ago

Isn't this solved by putting a single quote, or something of the sort, in front of the data?

teh_klev• 4 years ago

Yes, but if you're opening a CSV file, some folks don't "import" the CSV which would give you a chance to specify the data types for each column:

https://news.ycombinator.com/item?id=30986868

rngcntr• 4 years ago

Who said Bill Gates wants to manipulate our genes? ;)

adhesive_wombat2• 4 years ago

Be conservative in what you send, be liberal in what you accept.

This is why I think that's wrong: if you allow people to be sloppy with how they do things, they'll do it, and then make it part of their workflow, product, religion or whatever, and now everyone is stuck with it.

Be absolutely explicit with what you accept and refuse to deal with crap. Then you will only ever have to maintain a simple validator and the code that deals with good data, rather than having to have an incredibly hairly validator that leaks into your logic at every level, followed by cementing your bugs into everyone's implementations.

cjsawyer• 4 years ago

Then your competitor accepts messy data and you go out of business.

adhesive_wombat2• 4 years ago

Then file a bunch of bugs on their product relating to messy data in various pathological states and tie up their engineering for months. Game on!

burntoutfire• 4 years ago

Reality adapting to software (instead of the other way around) seems to be more and more common. Just today, my local library sent out email informing users that, while they agree that they shouldn't charge late fees for non-work days, the software company they use doesn't want to introduce such feature into the software, so they have no way but to charge for those days.

max5993• 4 years ago

Excel allows you to force a specific type during file import or directly on the worksheet with literally two clicks once the region is selected. You can also create templates if you want all your new documents to use a specific type.

The scientists are not adapting to the software, they are adapting to the incompetence of people in their research groups who refuse to learn how to use their main work tool and/or don't want to do the extra 2 clicks it takes to select "text".

hoosieree2• 4 years ago

"4 base pairs ought to be enough for anybody" - Bill Gates

keithalewis2• 4 years ago

Suggested title: "Human genes renamed to stop people who don't know how to use Microsoft Excel from misreading them as dates."

Shadonototra• 4 years ago

Modern stupidity at its finest, a point of no return was achieved

Schroedingersat• 4 years ago

Just don't use excel.

Or anything with autocorrect and autoformat.

nikonyrh• 4 years ago

I work in the retail sector, GTINs / EANs in the exponential form aren't ideal but sadly fairly common.

Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

Related Stories