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

21–30 of 143 posts

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

#21

Post by olau »

nicoburns wrote: post #18
nulbyte wrote: post #16

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

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.

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

#22

Post by mcv »

nicoburns wrote: post #18
nulbyte wrote: post #16

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

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

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

#23

Post by prepend »

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…

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.

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

#24

Post by mdb31 »

hoseja wrote: post #15
mdb31 wrote: post #4

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

The easy action should be safe by default.

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

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

#25

Post by criddell »

mcv wrote: post #22
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'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.

Sounds like MS Access.

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

#26

Post by Ekaros »

hoseja wrote: post #15
mdb31 wrote: post #4

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

The easy action should be safe by default.

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.

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

#28

Post by tanbog10 »

prepend wrote: post #23
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…

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?

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

#29

Post by cyxxon »

nicoburns wrote: post #18
nulbyte wrote: post #16

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

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.

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

#30

Post by jasode »

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

Post reply on HN