Sure, the vast biotech community outnumbers those few people who work in some other jobs (or use Excel for private stuff) and enter dates or floating point numbers.
Excel is trying to limit the effect of sloppiness on the part of laypeople. They usually don't set cell formatting to date or number. Excel really does the right thing there.
And if you're a scientist, presumably with a respectable degree and professional responsibilities, learn to use your tool correctly.
Nice how the correct ways to use Excel are listed as cumbersome workarounds.
1. Out of a thousand people entering "SEPT2", how many mean "September 2nd"?
2. How many of them use that shortcut intentionally?
3. How many people mean some gene?
My answers are:
1. almost all of them
2. A respectable part of those in 1.
3. A dozen?
And far more than a dozen people mean the gene, come on.
And even if you want to type september second, trying to guess a year to attach is not the best idea.
Pfft, I bet you only say that because you have a low h-index.
Even counting citations is better, although if admins would actually read the papers, that would be better-er. Even NIH intramural people talk about impact factor like it means something. Pretty fucked considering that the publishers of glamour rags have vested interests that are very nearly the opposite of "careful scholarship".
Yes, I can probably code something entirely in SQL to do it, but it will not be portable across SQL implementations; and yes, I can code that as a variable in my program... but neither of them seem to be as fluent as the way Excel does it.
I wouldn't use Excel for prod, but it comes in handy for a lot of small dumb shit purely because of =.
If portability is really important, I think your best bet would be a view that adds the calculated columns.
Unfortunately, that's still not as easy as using Excel, by a long stretch.
Remember, these are people who don't understand data types, or if they do they are to lazy to declare them.
I don't see them formulating a correct SQL query. Or use any kind of programming language that has strict typing.
It would have to be a custom-tailored system that knows about nomenclature in the field. Sounds not very efficient.
Well, neither can programmers, since SQL injection is still the most common security vulnerability in software, so I agree, SQL is probably a bad tool for the job :)
> It would have to be a custom-tailored system that knows about nomenclature in the field. Sounds not very efficient.
Is Excel a custom-tailored system that knows about nomenclature in the field? The article seems to explicitly argue against that.
I don't think it's a failure to understand data types. It's a mismatch between what you expect the software to do, and what it does by default. Unfortunately, Microsoft has steadfastly refused to allow any way to change the auto-formatting options(check out some people really pissed off for being treated like children here - http://answers.microsoft.com/en-us/office/forum/office_2007-...).
There's two useful prongs of attack here - one is to somehow force Excel to conform to the expectations of researchers - perhaps an extension that works to prevent the most egregious cases of auto-formatting gone wrong? The alternative would be what you suggest - creating and marketing a custom solution, the problem there is that you'd need either buy-in from a significant number of researchers to spread it, or you'd need to replicate a lot of Excel features to make the transition smooth for others.
The problem isn't with SQL, it's with the idiotic idea of gluing SQL queries from strings. See also: template languages for web pages, i.e. gluing strings to build what is really a tree.
--
I think the hate against Excel is unfounded. Sure, it's not the perfectly suited software for the domain - in the same way like a dedicated fruit slicer is better than a knife at slicing fruits, a dedicated fruit peeler is better at peeling them, etc. but one knife can do all those jobs pretty well on its own, while also being able to do countless other things. Excel is a very versatile and powerful tool, and this power comes from its flexibility. The solutions often proposed, like a "proper" database-backed system, often involves a fixed workflow and having to call in IT support every time something doesn't fit that workflow (which is always).
So IMO - yes for people dealing with data using Excel. Yes for them learning a programming language, SQL and a generic database system. But strong no for forcing them to use domain-specific dedicated "tools" that impose a particular workflow on them.
Sure, but converting "DEC1" to "December, 1st" is not egregiously wrong, it's a valuable feature and in most of the cases the expected thing.
You could argue that all you can do is mitigation since CSV files don't offer much ability to influence how Excel will load them, and all you would need is one improperly-configured Excel along the pipeline to break the data. However, this is a significant mitigation - Excel apps would be configured once, and you would deal with a situation 1% of the time, and the solution would be trivial(just configure it!). Instead now you're dealing with the problem every time, and the solution(just mark the cells!) takes a lot more effort.
I don't think Microsoft necessarily has incentive to add this configuration(the science community as a whole is probably a tiny blip on its radar), but this is why we create modular and extensible software - so others can tweak it to their liking.
Putting an option in to disable something that only a miniscule number of users would ever want is not sensible.
Note bene: I'm only talking about this DEC1-like conversions. I agree that there are lots of conversions that are annoying.
And is available for free - excel came with university / corporate license to all desktops for no extra cost (in most places relevant for this discussion). And popular + easily available - you need to convince IT to allow it on the network / preinstall it on the provided systems.
https://bmcbioinformatics.biomedcentral.com/articles/10.1186...
Bonus: the number of errors is positively correlated with Impact Factor (a tool used by statistically illiterate administrative types to judge the quality of research).