7 ms·

When researchers stop using Excel as their main "database", this problem might be solved.

Tomte 10y ago
What's the alternative? 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.
- Sacho 10y ago
  > I don't see them formulating a correct SQL query. 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-excel/stop-auto-correction-of-number-into-a-date/9968c54a-221b-4b18-a3d1-cfd3d312a8a6?auth=1 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.
  - Tomte 10y ago
    > prevent the most egregious cases of auto-formatting gone wrong 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.
    - Sacho 10y ago
      Not to a person who specifically does not want that feature - and since Excel does not provide a way to turn it off or customize it, my idea was that an extension might be able to. Of course, I've never written an Excel extension(macro?) so I have no idea.
      - Tomte 10y ago
        Of course you can turn it off. By formatting the cells as the correct type.
        Sacho 10y ago
        That's a ridiculous argument and you should know it. Forcing users to do manual work every time instead of having an option to disable or configure a feature is just a UX fail. Doubly so because I would bet those formatted excel files don't survive transition, and the data is actually transmitted in CSV or whatever, so you'd have to reformat the data over and over every time you open to edit it, and hope that someone along the way doesn't make a mistake. This a problem software is meant to solve, not create.. 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.
- viraptor 10y ago
  > It would have to be a custom-tailored system that knows about nomenclature in the field. 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.
DiabloD3 10y ago
The one thing SQL can't do that Excel can: a calculation cell, as in, one that starts with =. 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 =.
- Someone 10y ago
  Many databases support virtual columns (https://en.m.wikipedia.org/wiki/Virtual_column https://en.m.wikipedia.org/wiki/Virtual_column) 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.

Tomte 10y ago

What's the alternative? 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.

Sacho 10y ago
> I don't see them formulating a correct SQL query. 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-excel/stop-auto-correction-of-number-into-a-date/9968c54a-221b-4b18-a3d1-cfd3d312a8a6?auth=1 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.
- Tomte 10y ago
  > prevent the most egregious cases of auto-formatting gone wrong 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.
  - Sacho 10y ago
    Not to a person who specifically does not want that feature - and since Excel does not provide a way to turn it off or customize it, my idea was that an extension might be able to. Of course, I've never written an Excel extension(macro?) so I have no idea.
    - Tomte 10y ago
      Of course you can turn it off. By formatting the cells as the correct type.
      - Sacho 10y ago
        That's a ridiculous argument and you should know it. Forcing users to do manual work every time instead of having an option to disable or configure a feature is just a UX fail. Doubly so because I would bet those formatted excel files don't survive transition, and the data is actually transmitted in CSV or whatever, so you'd have to reformat the data over and over every time you open to edit it, and hope that someone along the way doesn't make a mistake. This a problem software is meant to solve, not create.. 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.
viraptor 10y ago
> It would have to be a custom-tailored system that knows about nomenclature in the field. 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.

DiabloD3 10y ago

The one thing SQL can't do that Excel can: a calculation cell, as in, one that starts with =. 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 =.

Someone 10y ago
Many databases support virtual columns (https://en.m.wikipedia.org/wiki/Virtual_column https://en.m.wikipedia.org/wiki/Virtual_column) 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.