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

31–40 of 143 posts

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

#31

Post by hamasho »

jmchuster wrote: post #7

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

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

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

#32

Post by overthemoon »

tanbog10 wrote: post #28
prepend wrote: post #23

Earlier quoted context omitted.

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.

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

I'm continually shocked at how technically incompetent academics can be. Otherwise brilliant people with the most batshit technical workflows I've ever seen.

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

#33

Post by nicoburns »

olau wrote: post #21
nicoburns wrote: post #18

Earlier quoted context omitted.

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

UTF-8 CSV with a few HTML tags for formatting? You could call it CHU. Now you just need to make Excel accept it.

I'd like it to have support for things like "this is a cell", where format is something like text, numeric, date, currency. I'm not sure how you'd specify that in HTML.

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

#34

Post by nicoburns »

cyxxon wrote: post #29
nicoburns wrote: post #18

Earlier quoted context omitted.

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

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.

I'd quite like these files to be openable in simple viewer/editor software that might not support formulas. I think once you support formulas you open pandora's box with regard to which formulas are supported.

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

#35

Post by JustAQ »

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…

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.

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

#37

Post by smcl »

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.

That solves one small part of the problem - viewing and editing the data in a UI. But can those tools address the problem raised by jasode?

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

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

#38

Post by dotancohen »

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

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

#39

Post by Symbiote »

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…

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

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

#40

Post by Closi »

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…

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

Post reply on HN