Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
theverge.com
theverge.com
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.
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.
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?
I’ve done some travel to Africa and am amazed at the ingenuity/batshit workflows that exist to get work done and live life.
There’s many times where I don’t want to do anything more than open, sort, filter, and never see the file again. And I’d like something better than Excel, but haven’t found it.
Maybe 70% the time, vi or BBEdit or shell commands work but otherwise excel.
For anything of significance, I use Python notebooks or dedicated data environment.
If there was a better tool, I think people would train. But there’s not, that I’m aware of, so Excel sticks around.
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.
You don't need to write your own GUI for databases. Loads exists.
> "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!"
This is a pretty common use-case, and neatly demonstrates a few of the reasons Excel is so popular. Sharing a self-contained DB with a colleague that they can view + edit with software they likely already have, modifying the schema easily on the fly, highlighting some rows. And that's not to mention the programmability - from having a simple "=SUM(...)" cell, to hacking some VBA or the newly introduced Lambda (https://www.microsoft.com/en-us/research/blog/lambda-the-ult...)
I wouldn't personally build anything important with Excel as a sort of DB, but I understand why some people would want to
1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel.
2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users:
- formatting any arbitrary row or column with bold/italics and change the font color or background cell color.
- pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query)
- hide rows or collapse rows into outlines
- cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior.
- ... tons of other GUI features like formulas, spell check, etc
sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user.
Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.
Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
> Also filtering is essential.
> So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
As long as it's not tedious to set a few thousand that sounds great.
> What are the other most common formulae that you use?
I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text).
I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement.
> Perhaps instead of filtering, you'd like to see outliers or the range of values?
In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10).
Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
> As long as it's not tedious to set a few thousand that sounds great.
> SUM, COUNT and IF (if-then)
Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:
I'm old school, so ctrl+c, shift+end (maybe tweak selection area with cursors if Excel is being dumb) and ctrl+v.
The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.
That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.
It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.
In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.
This is not "filtering", this is faceted search.
I am also a big fan of faceted search, and I found the occasional writing of Simon Willison’s on the topic very informative.
https://simonwillison.net/2018/Oct/4/datasette-ideas/#Facet_...
But yeah great feature, and thanks for the reference.
To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic.
Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation.
I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
https://github.com/dotancohen/structuresheet
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
Python / Qt / SQLite
I don't see any part that gives a strongly-typed properties.
Do I miss something?
Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself.
One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel.
> In a second moment, python, pandas and notebook are pretty accessible too
It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper.
In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.
Myself (not genomics, but Excel is also some kind of universal medium here as well), I store my data in SQLite files (extracts and summaries anyway; complete datasets take several terabytes), which makes retrieving complex information a breeze. But it needs to be documented and you need to be comfortable with the command line and do any kind of visualisation as a supplementary step. I know of a couple of colleagues doing the same, but we don’t use quite the same format, so data exchange is problematic. I use this setup mostly because I need it to work on remote HPC clusters in addition to a bunch of local workstations, and Excel is out of question there.
> The problem is that databases do not include a GUI for data viewing.
This invite goes for anybody else who [ab]uses Excel even though they are versed in SQL.
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!).
Do you have any recommendations for getting started with this?
The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table.
PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
My typical quick turn-around process is: type SQL in text editor, test sql in database, create a view, connect to the view from excel, use native excel features to display whats needed.
Usually I create a summary page as well which uses sum-ifs and such on the query result for the high level detail rather than go through the SQL process for it separately
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.
Why not?
Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap.
Excel works and it is easy. It does not matter how hard the underlying problem is. I hate having to use excel too, but I have found myself periodically relying on it when timelines get too narrow and having a ready-made interactive dashboard is convenient.
That being said, I can't imagine using it for a use-case where the rows-of-interest are greater than a few dozen.
When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.
At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".
A user only has a tiny screen relative to the size of the backing data. The hard part is efficiently fetching the data to populate that screen. But it's not unsolvable; just tricky. Approaches like building realtime indexes speculatively based on likely next user query could help.
Google Sheets actually works a bit like this already, as it's backed by an online datastore.
Having a single database and giving two other people access to it keeps the data centralised and keeps a single source of truth.
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.
MS Access was the usurper of dBase's crown. And then Excel took over ...
https://en.wikipedia.org/wiki/Paradox_(database)
I am not at all a programmer, but I remember that at least in early version(s) MS Access (circa 1994, Windows 3.1 times) was well behind Paradox in usability.
The are a ton of benefits to enforcing data integrity like data types, foreign keys, etc., but it also adds a ton of friction. Users encounter tons of frustrating errors while simply copying and pasting things, because certain values aren't allowed in certain columns.
I think you'd need human-readable datatypes displayed beneath each column name, adjustable by just clicking it and changing it in the drop-down, and massive flexibility out of the box. You shouldn't throw hard errors - just visually mark the invalid values red or pink or whatever, and let the user fix then before writing them to the database.
It's open source and hosted in the cloud. It has an excel like interface that you can share and work on with others.
It does exactly what you mentioned. It's a cloud hosted, database tool with an excel like interface and collaboration features.
It is also open source and can be self hosted, but you can use the oficial website to use it directly without having to use it yourself :)
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.
Actually I would change that from your answer to
"Access didn't come with the cheapest version of Office"
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).
Tuesday: New solution is released to much user enthusiasm
Thursday: Users don't get it and are already back in Excel
You can, very easily
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.
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...
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.
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.
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.
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...
My preferred work flow would be to open the csv, change some formatting, then reinterpret the original data.
It would require keeping two sets of data for each cell, but that seems to already be the case since F2-enter on each cell would accomplish exactly that. Last I tried.
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.
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.
- Plain text file
- Supports formatting, cell types, etc
- Does not support full spreadsheet features like formulas
You could call it CHU.
Now you just need to make Excel accept it.
I think it's already defined?
<input type="text">, <input type="number">, <input type="date">
> currency
You probably need to use the microdata format https://schema.org/Offer
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
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...
"Nil" is probably safe, because the lisp curse will actually work in your favor.
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.
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.
and MARS tweaked to MARS1.
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.
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".
I'm always fascinated to learn how Excel makes its way into unusual places.
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.
Or anything with autocorrect and autoformat.
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.
It's the second button on the data tab, you are still spreading misinformation.
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.