News for hackers. Stuff that matters.
Re:Scientists rename genes to stop Microsoft Excel from misr... by dmz73 on Tuesday March 26, 2024 @05:07AM (#39825650)
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?
Re:Scientists rename genes to stop Microsoft Excel from misr... by JimmyAustin on Tuesday March 26, 2024 @05:17AM (#39825703)
A lot of analysis is done using CSVs being pushed in and out of Excel. Doing so strips the formatting. Please understand the workflows being crying “skill issue”.
Re:Scientists rename genes to stop Microsoft Excel from misr... by mike_hearn on Tuesday March 26, 2024 @05:59AM (#39825954)
You can write CSVs in such a way that forces Excel to infer specific data types:

"=""Data Here"""

will always be treated as a string. This is also supported by Sheets, apparently.

Re:Scientists rename genes to stop Microsoft Excel from misr... by _visgean on Tuesday March 26, 2024 @08:23AM (#39826961)
ok but will that work other tools that work with those csvs? I imagine that they export / import from excel to csv for a reason.
Re:Scientists rename genes to stop Microsoft Excel from misr... by mcintyre1994 on Tuesday March 26, 2024 @07:49AM (#39826690)
TIL, neat! Does Excel automatically export like that if you format the columns as strings?
Re:Scientists rename genes to stop Microsoft Excel from misr... by bayindirh on Tuesday March 26, 2024 @05:23AM (#39825739)
Yes, but you can create a small macro to the change the formatting and assign that to your table. Your CSV should import deterministically. That's not impossible to do.

I saw a complete analysis engine written as an Excel file, which accepts and exports CSVs cleanly. It can be done.

Re:Scientists rename genes to stop Microsoft Excel from misr... by tssva on Tuesday March 26, 2024 @07:06AM (#39826371)
You don’t even need to write a macro. Excel’s Import Text Wizard will allow you to assign data type to each column at the time of import.
Re:Scientists rename genes to stop Microsoft Excel from misr... by LikesPwsh on Tuesday March 26, 2024 @08:06AM (#39826822)
Same for Power Query and Text to Columns.
Re:Scientists rename genes to stop Microsoft Excel from misr... by n4r9 on Tuesday March 26, 2024 @05:40AM (#39825834)
"It can be done" is a far cry from "it's reasonable to expect scientists to do this".
Re:Scientists rename genes to stop Microsoft Excel from misr... by bayindirh on Tuesday March 26, 2024 @05:43AM (#39825855)
As a person who does research and support researchers, I can't see the gap, sorry.

I understand some people don't know it's possible, and some don't care, but for any competent researcher, it's expected them to master the tools they use. This is esp. true for career researchers.

Re:Scientists rename genes to stop Microsoft Excel from misr... by n4r9 on Tuesday March 26, 2024 @06:00AM (#39825961)
I'm not disputing that a competent computer user can do it. From the perspective of "this is what I would do if I was a scientist", you're totally correct.

But when you're writing guidelines for an entire field - as the article describes HGNC doing - you're catering to all researchers in that field: good, bad, and ugly. Plus technicians, editors, admins and anyone else that might handle the files. Given how hidden and unintuitive Excel's behaviour is here, I think what they're doing makes sense.

Re:Scientists rename genes to stop Microsoft Excel from misr... by djtango on Tuesday March 26, 2024 @05:59AM (#39825953)
I agree.

As a researcher you may have to learn how to carefully dig up skulls, raise rats, handle lasers, remember not to accidentally syringe yourself with viruses etc.

Getting cut by Excel seems like part of the job and at least is hopefully less life threatening than possibly blowing yourself up or giving yourself silicosis.

That said the problem with computers is that they're pervasive, they're a moving target and often it's a case of the blind leading the blind when it comes to research. And probably more and more research groups need dedicated computer technician resources who can centralize the required computer knowledge of keeping a research group running.

Re:Scientists rename genes to stop Microsoft Excel from misr... by knighthack on Tuesday March 26, 2024 @05:21AM (#39825728)
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 well. Excel is not at fault here; the 'scientists' are.

Re:Scientists rename genes to stop Microsoft Excel from misr... by eigenket on Tuesday March 26, 2024 @06:38AM (#39826187)
Haha, you think scientists have control over what software their employer buys for them to work on.

There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.

Re:Scientists rename genes to stop Microsoft Excel from misr... by cqqxo4zV46cp on Tuesday March 26, 2024 @06:48AM (#39826265)
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?

Re:Scientists rename genes to stop Microsoft Excel from misr... by bayindirh on Tuesday March 26, 2024 @05:20AM (#39825725)
That's too much work. Nobody have time for doing or learning that.

Sounds like a joke, but it isn't. I know scientists who think exactly like that.

Re:Scientists rename genes to stop Microsoft Excel from misr... by rrr_oh_man on Tuesday March 26, 2024 @08:32AM (#39827043)
It’s probably the same scientists who answer an email 2 years later
Re:Scientists rename genes to stop Microsoft Excel from misr... by cqqxo4zV46cp on Tuesday March 26, 2024 @06:47AM (#39826253)
It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. This really just comes across as unproductive snark.
Re:Scientists rename genes to stop Microsoft Excel from misr... by chrisjj on Tuesday March 26, 2024 @06:54AM (#39826302)
> It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people.

I'd say more unreasonable is for you to mischaracterise this comment as you have done.

Re:Scientists rename genes to stop Microsoft Excel from misr... by barryrandall on Tuesday March 26, 2024 @09:06AM (#39827333)
C/C++ developers could just use the tooling available to them to mitigate memory management issues, but they don't. Why should we trust anything programmers produce if this is the level of competence they have with their chosen tools?
Re:Scientists rename genes to stop Microsoft Excel from misr... by beAbU on Tuesday March 26, 2024 @10:56AM (#39828720)
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 misr... by vegetable on Tuesday March 26, 2024 @02:18PM (#39831063)
You should never double click to open a csv file in excel. You should ALWAYS import the file into excel. Set your types during import. Many of the complaints about excel's csv handling in this thread are user errors.
Re:Scientists rename genes to stop Microsoft Excel from misr... by Loranubi on Wednesday March 27, 2024 @10:30AM (#39839654)
Not in this case. Excel makes it extra hard for you to do correctly. They encourage the wrong way.
Re:Scientists rename genes to stop Microsoft Excel from misr... by sparkie on Tuesday March 26, 2024 @02:12PM (#39830986)
You don't even need to do that. Just stick a single ' before any number/date and it will be treated as text.

The problems are when importing/exporting though. Even quoted numbers will lose the quote when exported to say, CSV. You need to explicitly save as quoted, and import quoted fields as text. The real issue is that these settings are not the default.

In LibreOffice (Save/Open CSV): https://imgur.com/a/ved7wgA