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

Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

theverge.com

Post reply on HN
Print view View original

131–140 of 169 posts

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#131

Post by __s »

lloydatkinson wrote: post #46
smeej wrote: post #44

The article has now been edited to note that Excel now offers a toggle to turn this feature off. How long did the entire genetics community struggle with this problem before deciding to rename a bunch of genes, making their own data harder to search, without just...asking Microsoft to add a new toggle for their use case?

Has anyone ever had any luck asking a large organisation, such as Microsoft, for anything good or meaningful changes? Without a huge controversy or media outcry?

I've seen it happen internally over years of sweat & tears

A few large customers can move things on specific products if they're able to escalate their support ticket into talking to the right developer

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#132

Post by acdha »

Amezarak wrote: post #119
acdha wrote: post #101

Earlier quoted context omitted.

Only if the option was implemented as a user preference which isn’t stored in the file. The right way to do this would be to add the flag as a file header, and give people a UI control to set it by default. That way you’d always see spreadsheets as the person who gave it to you did, and you would create new ones with your preferred behavior.

I don't understand what's wrong with the existing solution of: Right Click > Format Cells > Text. Set the data type of the column and Excel will not fiddle with it. Let it stay implicit and Excel will to guess. This change is saved in the file. The article only slightly touches on this: converting to CSV and losing all the Excel formatting and then opening it in Excel again.

The problem with that is that you have to remember to do it before Excel loses data.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#133

Post by beAbU »

dmz73 wrote: post #18

Or they could set the format of the column to text instead of keeping it as general and everything would just work. If these "scientists" are having this problem then they haven't spent any time at all learning how to use the tool they rely on. Can we trust anything these people produce if this is the level of competence they have with Excel?

This is a very unfair and harsh comment.

In my current work, we deal with our user's national identity numbers quite frequently. This number is a 13 digit numerical number, that starts with your date of birth. So someone born on March 13 1989 will have a number start with 890913. People born in the aughts have "00" "01" "02" etc at the start of their ID number.

We need to frequently generate excel and csv reports that contain these numbers, and we need to ingest CSVs from other vendors that contain these numbers.

The /moment/ excel touches a CSV with these numbers in, it'll assume that column is a number, it'll strip out the preceding zeros, and it'll format the number in scientific notation. If you change the column's data type to text afterwards, then it's too late - the damage has been done and you've worst case lost data, best case you have a text column full of scientific notation numbers. You can't just open up the CSV, you need to import it, and very explicitly tell Excel how to handle this column, otherwise you mess things up.

Now, anywhere in the chain of people and other vendors sending and receiving these files, anyone who double clicks on that file and it opens up in excel and does not notice this very destructive action messes up our processes and causes unknown amounts of delays. It's the bane of my existence. This exact problem also crops up with phone numbers, where in many countries the number starts with a 0, or if it's an international number, a "+". Excel thinks the "+" makes the field a formula.

All of this because Excel is making assumptions and trying to "help", in the same way a 4 year old helps in the kitchen.

For this reason I find it incredibly frustrating to work with CSVs, because there is no "native" way for me to open the file and interact with the data in a native and intuitive way without running the risk of data being lost or edited without me noticing. I've resorted to importing the files into a local DB instance and using SQL to interact with the data, especially if the files are large.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#134

Post by sandworm101 »

shaan7 wrote: post #86
ulrikrasmussen wrote: post #4

I think implicit conversions is the other billion dollar mistake in computing. It just shows up as a subtle source of mistakes in so many places. JavaScript, MySQL, YAML, Excel, PHP, just to make a few off the top of my head, have all caused countless bugs and security exploits due to implicit conversations of data which can be interpreted in multiple ways, and where the system happened to pick the wrong one.

Call it cynicism but I think humans just prefer things to be implicit. I've been annoyed by how people do not want to be specific, but then rant about how someone did not understand exactly what they wanted. A certain CEO says that we won't need programming languages because you will be able to program AI systems with natural language. Well, the problem with natural language is not that we do not have the tech, it is…

>> it is that natural language is too imprecise.

If only there was a trade, a group of educated professionals with specific training in the use of language to describe a desired outcome. We could call them "language engineers" and regulated them via state associations meant to protect the public from charlatans.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#136

Post by SAI_Peregrinus »

DonsDiscountGas wrote: post #117
makeitdouble wrote: post #90

Earlier quoted context omitted.

As usual, I think it's a lot more nuanced than that. An option was added last year [0] the way you propose it, but until then users already had the option to mark the columns as text. The same way we have to deal with any data where auto detection might be problematic: explicitely set a type, and potentially a format. But of course that's bothersome, sometime people forget to do it, sometimes they don't even know how…

> but until then users already had the option to mark the columns as text This only works on Excel files though, which save a type for columns/cells. With CSV files, Excel would just auto-format them. I guess you could save everything as xls/xlsx, but I shouldn't have to use a vendors file format because they're threatening to corrupt my data if I don't.

That's just CSV being unusable shit. There's always a schema for the types of the fields, but there's no way to communicate that schema, so CSV is inherently ambiguous. If your data includes fancy things like numbers you should avoid CSV.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#137

Post by [deleted] »

cqqxo4zV46cp wrote: post #59
knighthack wrote: post #24

Earlier quoted context omitted.

Sorry, can't understand, and that excuse is unacceptable. It's totally a skill issue, and a failure of it, and a laziness of these 'scientists'. You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as w…

Coming up next: people should still use C because writing insecure code is a “skill issue”. Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?

[deleted]

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#138

Post by l72 »

Waterluvian wrote: post #83
ulrikrasmussen wrote: post #4

I think implicit conversions is the other billion dollar mistake in computing. It just shows up as a subtle source of mistakes in so many places. JavaScript, MySQL, YAML, Excel, PHP, just to make a few off the top of my head, have all caused countless bugs and security exploits due to implicit conversations of data which can be interpreted in multiple ways, and where the system happened to pick the wrong one.

It’s so awful in JavaScript that they made new comparison operators that don’t do it. And now it’s pretty much universal that you set == and != to be linting errors and just never use them. They’re good for professional and hobby code golf though.

Except for when you to `var != null` to make sure var isn't null OR undefined since those are two different things in javascript and undefined will implicitly be converted to null here.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#139

Post by OJFord »

KwisatzHaderack wrote: post #6

So let me get this straight: Scientists would rather rename genes, than to simply use a tool other than MS Excel?

They could also just format that column explicitly, it's not really an issue of how much friction there is so much as that there is friction, it seems to me.

Re: Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)

#140

Post by WorldMaker »

SAI_Peregrinus wrote: post #136
DonsDiscountGas wrote: post #117

Earlier quoted context omitted.

> but until then users already had the option to mark the columns as text This only works on Excel files though, which save a type for columns/cells. With CSV files, Excel would just auto-format them. I guess you could save everything as xls/xlsx, but I shouldn't have to use a vendors file format because they're threatening to corrupt my data if I don't.

That's just CSV being unusable shit. There's always a schema for the types of the fields, but there's no way to communicate that schema, so CSV is inherently ambiguous. If your data includes fancy things like numbers you should avoid CSV.

It's still a shame that Excel's JSON support is so good, but no business user trusts it because "you can't double click the files" and CSV remains the worst best thing you can double click.

I don't want Excel to take over the file association for .json but I do sometimes wish Excel would just invent a file extension like .xljson that loads JSON so that at least you could trivially rename a JSON file to .xljson and get working double-click for your business users but a file format (with optional schema support even) that's a more modern and saner de facto standard than CSV.

I think that one dumb trick would improve everyone's productivity a great deal.

Post reply on HN