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

111–120 of 143 posts

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

#111

Post by kergonath »

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.

In an alternate universe we have nice specialised GUI front ends on things like netCDF or SQLite that enable what you say. Or hell, just a spreadsheet-like view over a real database (but with actual names as columns). All the GUIs I have seen are really not up to the task for typical scientific data. They are clunky, heavy, slow, and difficult to use for visualisation. We are not writing new front ends for every type of data, and nobody seems to have written a decent one that could be universal.

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.

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

#112

Post by [deleted] »

listenallyall wrote: post #93
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…

> you should be able to tell it whether a certain column is a date or not You can, very easily

[deleted]

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

#113

Post by kergonath »

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.

That’s awesome! Where can we subscribe for any announcement? ;)

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

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

#114

Post by kergonath »

pelasaco wrote: post #74
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…

We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. We have good tools that with investment could be a much better fit. Said that, I don't know what one cannot do with i.e RStudio to use Excel for science. In a second moment, python, pandas and notebook are pretty accessible too..

> We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022.

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.

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

#115

Post by max599 »

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…

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

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

#116

Post by humanistbot »

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.

I love how you gave advice in your parent post as if you knew what Excel's current features are, then admitted you haven't used it in 10 years.

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

#117

Post by ElectricalUnion »

magicalhippo wrote: post #69
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.

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.

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

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

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

#118

Post by [deleted] »

chipsa wrote: post #53

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

[deleted]

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

#119

Post by max599 »

burntoutfire wrote: post #97

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.

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

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

#120

Post by simonw »

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 fixed that a few releases ago with STRICT tables: https://www.sqlite.org/stricttables.html
Post reply on HN