Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

theverge.com

Post reply on HN
Print view View original

61–70 of 143 posts

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

#61

Post by dotancohen »

mort96 wrote: post #49
dotancohen wrote: post #47

Earlier quoted context omitted.

Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.

I'm curious, how do you achieve a strongly-typed database with sqlite? sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.

SQLite has a powerful data-validation function for each column, look for CHECK Constraints: https://www.sqlite.org/lang_createtable.html

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.

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

#62

Post by gompertz »

JustAQ wrote: post #35
jasode wrote: post #30

Earlier quoted context omitted.

>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 k…

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.

Going to piggyback off this comment to ask - does anyone know a lightweight GUI tool to edit/modify SharePoint Lists? Only way I ever found was through the browser which is painful on large data.

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

#63

Post by gompertz »

jasode wrote: post #30
mcv wrote: post #20

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 n…

>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 k…

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.

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

#64

Post by washadjeffmad »

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.

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

#65

Post by mcv »

gompertz wrote: post #63
jasode wrote: post #30

Earlier quoted context omitted.

>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 k…

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.

But this is also a problem with using Excel for this. Let two other people edit it, and now you've got 3 different versions of the data. Which one is correct?

Having a single database and giving two other people access to it keeps the data centralised and keeps a single source of truth.

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

#66

Post by JacobThreeThree »

charlieyu1 wrote: post #50
jasode wrote: post #30

Earlier quoted context omitted.

>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 k…

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

>You are not supposed to have a GUI view when you have 100K+ entries.

Why not?

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

#67

Post by mcv »

jasode wrote: post #30
mcv wrote: post #20

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 n…

>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 k…

> 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.

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

#68

Post by dwringer »

rini17 wrote: post #17
joenathanone wrote: post #14

Earlier quoted context omitted.

Excel has exactly the same functionality.

Good, since when? Last used it 10ish years ago.

It was there, the problem is that by default if you open files the way most people do (double-clicking or dragging) then Excel decides you must not want to be bothered by the options so it fills them in for you. "Helpfully" not even using the most basic inference to assign types in a way that doesn't destroy the data.

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

#69

Post by magicalhippo »

dotancohen wrote: post #47
jasode wrote: post #41

Earlier quoted context omitted.

>What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access 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 o…

Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.

Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions.

Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.

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

#70

Post by joshvm »

loufe wrote: post #46

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.

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/

Post reply on HN