[HN Gopher] Human genes renamed to stop Microsoft Excel from mis...
___________________________________________________________________
Human genes renamed to stop Microsoft Excel from misreading them as
dates (2020)
Author : takiwatanga
Score : 180 points
Date : 2022-04-11 09:05 UTC (13 hours ago)
(HTM) web link (www.theverge.com)
(TXT) w3m dump (www.theverge.com)
| hoosieree wrote:
| "4 base pairs ought to be enough for anybody" - Bill Gates
| keithalewis wrote:
| Suggested title: "Human genes renamed to stop people who don't
| know how to use Microsoft Excel from misreading them as dates."
| mcv wrote:
| 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 not use Excel for something like
| that.
|
| Excel misuse is sadly rampant and one of my main frustrations at
| my previous project. Excel is popular for this sort of misuse
| because when you open it, it presents you with an empty table and
| you can immediately start typing. This invites tabular data. But
| Excel inevitably fucks up because it lacks proper data types,
| data validation, or foreign keys, and loves making assumptions
| about what you mean. This makes it a terrible and harmful choice
| for any sort of serious data. I even proposed a new project for
| an Excel-like frontend backed by a database exactly for these
| sort of situations. Because I do get what attracts people to
| Excel for this sort of thing. And most people don't realise what
| a terrible choice it is.
| thesuitonym wrote:
| It's a real shame, because Excel is great for taking quick
| notes when you have data, but you're not yet sure how you'll
| want to organize it, or what you'll want to do with it long
| term. You start by just jotting down numbers. But then you have
| everything there an an easy to digest form. You can create
| pivot tables to sort the data. Graphing is easy. The data gets
| stuck there because it's so easy to use and it's already there.
| listenallyall wrote:
| > you should be able to tell it whether a certain column is a
| date or not
|
| You can, very easily
| [deleted]
| jasode wrote:
| _> 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.
| zozbot234 wrote:
| > 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.
|
| What's missing from a GUI like MS Access or Libreoffice Base?
| In your example, you can input your comments/annotations
| (even something like "highlight these rows as having
| problems") in a separate table without touching the original
| data, then use a database query/view to look at both
| seamlessly. It's only a bit more involved than raw editing on
| a spreadsheet, and it inherently avoids accidental data
| loss/corruption.
| pixl97 wrote:
| >MS Access also isn't really an option since
|
| Actually I would change that from your answer to
|
| "Access didn't come with the cheapest version of Office"
| Aloha wrote:
| I wish there were modern record oriented databases and
| processing tools - I've used some of the tools on zOS and
| that whole mindset of "even the system tools are record
| aware" so you can use the equivalent of basic UNIX tools to
| do reporting and data analysis, is pretty powerful.
|
| We could build something that is both as powerful as excel,
| and easier to use, yet designed for average users to
| manipulate very large data sets - we just have to chose to do
| that.
|
| Python has footguns in it, PHP has a confusing standard
| library, perl is complex, and bash is missing some of the
| data processing primitives needed.
| charlieyu1 wrote:
| You are not supposed to have a GUI view when you have 100K+
| entries. Excel misguides people to think that database
| management is easy. The reality is it is not possible to have
| a GUI view of middle-large databases with millions of entries
| because it is computationally impossible
| shadowgovt wrote:
| It's not computationally impossible; it's a hard problem
| that nobody has solved because they haven't found the niche
| to monetize it.
|
| A user only has a tiny screen relative to the size of the
| backing data. The hard part is efficiently fetching the
| data to populate that screen. But it's not unsolvable; just
| tricky. Approaches like building realtime indexes
| speculatively based on likely next user query could help.
|
| Google Sheets actually works a bit like this already, as
| it's backed by an online datastore.
| andi999 wrote:
| Why shd that be computationally impossible?
| JacobThreeThree wrote:
| >You are not supposed to have a GUI view when you have
| 100K+ entries.
|
| Why not?
| KronisLV wrote:
| Would also like to know, seems like a perfect use case
| for the Flyweight design pattern - just get the total
| count of records for when needed (e.g. size of scrollbar)
| and show the stuff that should be on screen at any given
| moment, loading more in dynamically. Throw in allowing
| doing operations on ranges that are off screen and you
| should be good.
| screye wrote:
| One thing I've learnt over the years, is that users do not
| care about the inherent complexity of your problem.
|
| Like water, they will always choose the path of least
| resistance. It is why people would rather copy-paste
| documents than learn git, despite versioning being
| inherently complex. It is why people complain about
| android, but only use 1st party preinstalled apps or
| freemium ad-infested crap.
|
| Excel works and it is easy. It does not matter how hard the
| underlying problem is. I hate having to use excel too, but
| I have found myself periodically relying on it when
| timelines get too narrow and having a ready-made
| interactive dashboard is convenient.
|
| That being said, I can't imagine using it for a use-case
| where the rows-of-interest are greater than a few dozen.
| Aloha wrote:
| a few thousand or more is practical, it depends on what
| the data is, if the data is inherently non-relational (a
| master parts list, a user list, a bunch of logged data
| points) then all is well, it breaks down based on how big
| the data set is.. and _what_ the dataset is.
| chasd00 wrote:
| 100k+ is pretty manageable in the Excel GUI. When you get
| above 1M then it starts getting dicey. I have a junior dev
| on my team that is very good at python. I only consider him
| "jr" in the language/tech he's assigned to work on daily,
| in python i consider him pretty solid.
|
| When we need data work done on csv's larger than I can ask
| an Analyst to do in Excel I give it to him to write some
| python against. Finding and exploiting his python skills
| has won the guy a couple bumps in base pay.
| Aloha wrote:
| Very much agreed - and the upper limit is a little more
| fuzzy, and has more to do with file size, record
| complexity (how many columns), and what you want to do
| with the data.
|
| At some point the overall pokeyness of excel when dealing
| with large datasets overcomes the inertia of "everyone
| already knows it" and "we'd need a environment to spin up
| something more complex".
| JustAQ wrote:
| What'd be wrong with using something like DBeaver,
| SqliteStudio, or PG-Admin? Or buying a tool like DataGrip?
| Microsoft Access even allows you to create your own UI forms
| for a database.
|
| You don't need to write your own GUI for databases. Loads
| exists.
| jasode wrote:
| _> What'd be wrong with using something like DBeaver,
| SqliteStudio, or PG-Admin? Or buying a tool like DataGrip?
| Microsoft Access _
|
| 1) Those utilities are not typically included in the
| workstation image of laptops/desktops unlike MS Excel which
| is already part of Office 365. Millions are already
| familiar with the GUI of Excel.
|
| 2) The datagrid viewers in those tools are not powerful and
| feature-rich like Excel. They are often missing features
| that are taken for granted by Excel users:
|
| - formatting any arbitrary row or column with bold/italics
| and change the font color or background cell color.
|
| - pivot the data via drag & drop UI (instead of manually
| writing a SQL cross-tab query)
|
| - hide rows or collapse rows into outlines
|
| - cut rows 37 to 52 and paste them above row 5. That type
| of behavior is not easy in generic database viewers because
| most tables -- by typical design of RDBMS table row ids --
| do not consider the visible spatial ordering of rows the
| way the end user wants to see them on the screen unless one
| adds an extra column to the table such as
| "gui_view_order_id". Excel has user specified row ordering
| as default out-of-the-box behavior.
|
| - ... tons of other GUI features like formulas, spell
| check, etc
| zozbot234 wrote:
| Add an extra table mapping id to gui_order, then just
| write an easy query/report (or edit the one you already
| have) to give you a view that's ordered by gui_order
| first. Not that hard, you just need to make the semantics
| of what you're doing explicit. This is the inherent
| tradeoff between raw editing on a spreadsheet (quick and
| dirty, very error prone) and working on something that
| has real structure to it, reflecting some underlying
| semantics.
| dotancohen wrote:
| Please, keep adding to the "tons of other GUI features"
| list. I'm just started on a Python/Qt/Sqlite project for
| spreadsheet-like functionality backed by a strongly-typed
| database. The things that you mention are perfect
| features. I'd love to know more.
| kergonath wrote:
| That's awesome! Where can we subscribe for any
| announcement? ;)
|
| To me, a critical one is easy plots. Checking how an
| arbitrary column changes as a function of an arbitrary
| other column by adding a scatter plot in less than 5
| seconds is fantastic.
|
| Formulae are also very useful. Adding numerical
| derivatives or integrals by just putting a formula in a
| new column is very useful as well. The point is not to
| have publication-quality, highly accurate numbers, but
| just quick and dirty operations to see if it warrants
| further investigation.
|
| I am happy to discuss my use cases if you are interested
| (it might be going a bit out of topic for this thread).
| mort96 wrote:
| I'm curious, how do you achieve a strongly-typed database
| with sqlite?
|
| sqlite's lack of verifying that your data fits in your
| defined schema is by far my biggest problem with sqlite.
| ElectricalUnion wrote:
| check (typeof(column) = 'integer')
| simonw wrote:
| SQLite fixed that a few releases ago with STRICT tables:
| https://www.sqlite.org/stricttables.html
| dotancohen wrote:
| SQLite has a powerful data-validation function for each
| column, look for CHECK Constraints:
| https://www.sqlite.org/lang_createtable.html
|
| But the real nice thing is that SQLite will allow
| spreadsheet-like behaviour by allowing one to enter an
| invalid data type. I could check that in Python or I can
| store it and warn "Invalid type blah blah blah". This
| will make life easier for those coming from a
| spreadsheet, or importing data. As the program matures, I
| can reevaluate what to do with invalid data as a default
| and what options to give the user.
|
| Additionally, because SQLite allows arbitrary data in any
| column, adding support for e.g. formulae is greatly
| simplified.
| magicalhippo wrote:
| Formulas is a big one for me. As a programmer I often use
| Excel to generate insert/update SQL statements for
| example. So I'd need formulas with not just numerical but
| also string manipulation functions.
|
| Also filtering is essential. So nice to be able to see
| all the values in the filter dropdown, easy to quickly
| spot weird values.
| ElectricalUnion wrote:
| > Also filtering is essential. So nice to be able to see
| all the values in the filter dropdown, easy to quickly
| spot weird values.
|
| This is not "filtering", this is faceted search.
|
| I am also a big fan of faceted search, and I found the
| occasional writing of Simon Willison's on the topic very
| informative.
|
| https://simonwillison.net/2018/Oct/4/datasette-
| ideas/#Facet_...
| dotancohen wrote:
| How does this sound? Formulae are supported, but the cell
| (not column) must be set as the Formulae datatype. The
| first formula that I am supporting is SUM, as it is easy
| to code in SQL. What are the other most common formulae
| that you use? I'll only implement the integer / float
| formulae at first, but I'll add string manipulation
| later. > Also filtering is essential.
|
| Filtering, of could. I've already got a hidden column for
| all rows isDisplay. > So nice to be able
| to see all the values in the filter dropdown, easy to
| quickly spot weird values.
|
| Perhaps instead of filtering, you'd like to see outliers
| or the range of values?
| magicalhippo wrote:
| > Formulae are supported, but the cell (not column) must
| be set as the Formulae datatype
|
| As long as it's not tedious to set a few thousand that
| sounds great.
|
| > What are the other most common formulae that you use?
|
| I mostly use SUM, COUNT and IF (if-then), along with
| functions to check if a string value is a valid number.
| Also string formatting of numbers and dates (for
| concatenating with text).
|
| I've also used lookups, ie find the row matching this and
| extract the value from the given column in that row.
| Though I'm not super happy with the way Excel does that,
| surely some room for improvement.
|
| > Perhaps instead of filtering, you'd like to see
| outliers or the range of values?
|
| In addition. Sometimes I just want to view all the values
| matching X, other times I want to quickly see any
| outliers. Definitely ranges, especially for numbers (just
| larger than 0 for example, or between 5 and 10).
|
| Sounds like a very interesting project, if you got a link
| I'd be interested in tracking progress. If not, I'd be
| happy if you did a Show HN when you're ready :)
| pelasaco wrote:
| We are talking about scientists, which are for sure able
| and used to work through different tools during their
| career. And we are in 2022. We have good tools that with
| investment could be a much better fit. Said that, I don't
| know what one cannot do with i.e RStudio to use Excel for
| science. In a second moment, python, pandas and notebook
| are pretty accessible too..
| kergonath wrote:
| > We are talking about scientists, which are for sure
| able and used to work through different tools during
| their career. And we are in 2022.
|
| Scientists are not superhuman. Just like anybody, they'll
| jump through a lot of hoops if they think the results
| justify it, but they are sometimes quite resistant to
| change for the sake of change, and sometimes even to
| change itself.
|
| One can be a great chemist or know all there is to know
| about how purple long-tailed fruit flies from Siberia and
| have no clue about how computers work. Or be very
| proficient in a given piece of complex software to
| process NMR spectra and barely able to operate Outlook.
| But these people all make do with Excel.
|
| > In a second moment, python, pandas and notebook are
| pretty accessible too
|
| It's much heavier, the IDEs are much more complex than
| Excel, and quite a lot of people on Earth are not natural
| programmers. Startup time, learning curve, steps to get a
| useful graph to check a trend, etc. All friction adds up.
| I've seen it countless times: when you show them the
| results of a complex workflow, they are excited. They
| start getting distracted when you talk about
| architecture, and they're lost when you go into things
| like pandas and scipy. Then they nod politely, keep doing
| their stuff in Excel, and call you when they need a bit
| of wizardry for a paper.
|
| In short, they are regular users, even if the software
| they use can be highly specific. Ease of use and lack of
| friction are paramount.
| kergonath wrote:
| In an alternate universe we have nice specialised GUI front
| ends on things like netCDF or SQLite that enable what you
| say. Or hell, just a spreadsheet-like view over a real
| database (but with actual names as columns). All the GUIs I
| have seen are really not up to the task for typical
| scientific data. They are clunky, heavy, slow, and
| difficult to use for visualisation. We are not writing new
| front ends for every type of data, and nobody seems to have
| written a decent one that could be universal.
|
| Myself (not genomics, but Excel is also some kind of
| universal medium here as well), I store my data in SQLite
| files (extracts and summaries anyway; complete datasets
| take several terabytes), which makes retrieving complex
| information a breeze. But it needs to be documented and you
| need to be comfortable with the command line and do any
| kind of visualisation as a supplementary step. I know of a
| couple of colleagues doing the same, but we don't use quite
| the same format, so data exchange is problematic. I use
| this setup mostly because I need it to work on remote HPC
| clusters in addition to a bunch of local workstations, and
| Excel is out of question there.
| smcl wrote:
| That solves one small part of the problem - viewing and
| editing the data in a UI. But can those tools address the
| problem raised by jasode?
|
| > "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!"
|
| This is a pretty common use-case, and neatly demonstrates a
| few of the reasons Excel is so popular. Sharing a self-
| contained DB with a colleague that they can view + edit
| with software they likely already have, modifying the
| schema easily on the fly, highlighting some rows. And
| that's not to mention the programmability - from having a
| simple "=SUM(...)" cell, to hacking some VBA or the newly
| introduced Lambda (https://www.microsoft.com/en-
| us/research/blog/lambda-the-ult...)
|
| I wouldn't personally build anything important with Excel
| as a sort of DB, but I understand why some people would
| want to
| gompertz wrote:
| Going to piggyback off this comment to ask - does anyone
| know a lightweight GUI tool to edit/modify SharePoint
| Lists? Only way I ever found was through the browser which
| is painful on large data.
| mcv wrote:
| > The problem is that databases do not include a GUI for data
| viewing.
|
| Exactly. It baffles me that such a tool still doesn't exist
| (though elsewhere someone claimed that MS Access is like
| this; I'm not familiar with it).
|
| Keeping this in the cloud, with a web-based Excel-like
| interface, where you can share it with anyone you choose, but
| keep a single source of truth, I think that would be
| incredibly useful and solve this Excel-misuse issue.
| rrrrrrrrrrrryan wrote:
| Access is literally this, and it never caught on.
|
| The are a ton of benefits to enforcing data integrity like
| data types, foreign keys, etc., but it also adds a ton of
| friction. Users encounter tons of frustrating errors while
| simply copying and pasting things, because certain values
| aren't allowed in certain columns.
|
| I think you'd need human-readable datatypes displayed
| beneath each column name, adjustable by just clicking it
| and changing it in the drop-down, and massive flexibility
| out of the box. You shouldn't throw hard errors - just
| visually mark the invalid values red or pink or whatever,
| and let the user fix then before writing them to the
| database.
| ChadNauseam wrote:
| Forgive my ignorance, but isn't that what Airtable is?
| alex1431999 wrote:
| There is actually a tool that does exactly what you
| describe. It's called baserow :) https://baserow.io/
|
| It's open source and hosted in the cloud. It has an excel
| like interface that you can share and work on with others.
| ableal wrote:
| Funnily enough, I think one the original IBM PC killer apps
| was exactly that - dBase III
| https://en.wikipedia.org/wiki/DBase
|
| MS Access was the usurper of dBase's crown. And then Excel
| took over ...
| jaclaz wrote:
| I would mention also (although it didn't have much
| success in the end) the Borland database, Paradox for
| Windows:
|
| https://en.wikipedia.org/wiki/Paradox_(database)
|
| I am not at all a programmer, but I remember that at
| least in early version(s) MS Access (circa 1994, Windows
| 3.1 times) was well behind Paradox in usability.
| alex1431999 wrote:
| There is actually a tool like that, it's called Baserow :)
|
| It does exactly what you mentioned. It's a cloud hosted,
| database tool with an excel like interface and
| collaboration features.
|
| It is also open source and can be self hosted, but you can
| use the oficial website to use it directly without having
| to use it yourself :)
| Aloha wrote:
| A modern web-based cloud-hosted access implementation would
| be a killer app for web-2.0.
| dotancohen wrote:
| > The problem is that databases do not include a GUI for data
| viewing.
|
| Let's say that I have PyCharm open right now, I'm importing
| Qt and Sqlite. How would you like your GUI to function?
| Seriously, write for me a detailed spec and a detailed
| workflow, and I'll get to work on it already. My Gmail
| username is the same as my HN username if you'd prefer to
| collaborate offline.
|
| This invite goes for anybody else who [ab]uses Excel even
| though they are versed in SQL.
| jraph wrote:
| SQLiteBrowser is written in Qt and is a GUI for SQLite.
| That's exactly what came in mind when I read parent's post.
|
| https://sqlitebrowser.org/
| dotancohen wrote:
| That was my first thought, too. But I think that there is
| demand for a tool that feels like a spreadsheet, backed
| by SQLite.
| gompertz wrote:
| I think the big point missing in all the comments that
| suggest linking to the db file and distributing is that it's
| read-only. Perhaps this is the intent too, but often Excel
| books are shared for other people's input /additions/
| corrections to data. When it's linked to a backend database
| that's no longer trivial. Whereas just using base rows/
| columns it's immediately read & write access.
| mcv wrote:
| But this is also a problem with using Excel for this. Let
| two other people edit it, and now you've got 3 different
| versions of the data. Which one is correct?
|
| Having a single database and giving two other people access
| to it keeps the data centralised and keeps a single source
| of truth.
| Closi wrote:
| > 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 alternative is to load the sqlite DB into Excel via
| PowerQuery and then share the file, which will maintain type
| safety of all columns through the excel data model.
|
| This provides much better GUI data manipulation too, as you
| can define relationships between the data in the model e.t.c.
|
| The problem is I would guess less than 1% of Excel users
| actually understand this functionality, but it is absolutely
| core to doing proper analysis in excel (not saying you don't
| use it - you probably do - but lots of users don't!).
| hn_version_0023 wrote:
| > The problem is I would guess less than 1% of Excel users
| actually understand this functionality
|
| Do you have any recommendations for getting started with
| this?
| hn_version_0023 wrote:
| To answer my own question: looks like all you need is
| Excel 2016 or better.
|
| https://powerquery.microsoft.com/en-us/excel/
| cupofpython wrote:
| I do this all the time. Excel is a fantastic data GUI for
| ad-hoc reporting.
|
| My typical quick turn-around process is: type SQL in text
| editor, test sql in database, create a view, connect to the
| view from excel, use native excel features to display whats
| needed.
|
| Usually I create a summary page as well which uses sum-ifs
| and such on the query result for the high level detail
| rather than go through the SQL process for it separately
| keithalewis wrote:
| If you know Excel and C++ then you might like
| https://github.com/xlladdins/xll. It is something I wrote to
| embed C++ in Excel.
| civilized wrote:
| Monday: "Excel is terrible, use my new solution, it'll blow
| your socks off!"
|
| Tuesday: New solution is released to much user enthusiasm
|
| Thursday: Users don't get it and are already back in Excel
| prepend wrote:
| 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.
| tanbog10 wrote:
| "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?
| prepend wrote:
| I consider myself pretty trained and still use Excel for
| "eyeballing" datasets of up to a million records.
|
| There's many times where I don't want to do anything more
| than open, sort, filter, and never see the file again. And
| I'd like something better than Excel, but haven't found it.
|
| Maybe 70% the time, vi or BBEdit or shell commands work but
| otherwise excel.
|
| For anything of significance, I use Python notebooks or
| dedicated data environment.
|
| If there was a better tool, I think people would train. But
| there's not, that I'm aware of, so Excel sticks around.
| overthemoon wrote:
| I'm continually shocked at how technically incompetent
| academics can be. Otherwise brilliant people with the most
| batshit technical workflows I've ever seen.
| prepend wrote:
| People are very inventive to get their job done.
|
| I've done some travel to Africa and am amazed at the
| ingenuity/batshit workflows that exist to get work done
| and live life.
| dotancohen wrote:
| Like everybody else, they take the path of least
| resistance. Of course, when that path is Excel they're in
| for a lot of resistance later (like needing to rename
| genes), but at first it feels smooth.
| pelasaco wrote:
| classical situation where the tool defines the workflow and not
| the other way around..
| max599 wrote:
| > you should be able to tell it whether a certain column is a
| date or not,
|
| Not only is this possible, but the tool to do it is located in
| best location (large menu, center of the screen on the first
| ribbon tab). It take literally 2 clicks to do it once your
| cells or columns are selected. The only problem with this tool
| is that you have to use before copying your data and it can be
| frustrating if you forget to do it. If you want to import a
| file instead of copy-pasting the data, it's only like 1 or 2
| extra clicks to set the data type for a column during the
| import.
|
| And btw, the CSV format was intentionally designed to NOT have
| type information imbedded in the file itself. The application
| that is reading the CSV file must know the datatype for each
| column. For a versatile tool like excel, they is no perfect way
| to implement it and there will always be a fraction of users of
| have to override the choices made by by the software. For
| advanced users who use it everyday, you learn very quickly if
| the type of data you are normally working with will require you
| to force it or if excel will understand it correctly.
|
| It looks to me like almost all of the anti-Excel comments on HN
| (including yours) are made by people who never or extremely
| rarely uses it and don't know what it can or can't do. It's
| typical that most if not all of the "missing features" listed
| by people on NH have been part of excel for at least a decades.
| Symbiote wrote:
| > Excel-like frontend backed by a database
|
| Microsoft Excel itself can connect to databases (MS SQL,
| PostgreSQL, Oracle, etc). I think you need to have set up the
| database table(s) elsewhere.
|
| Microsoft Access provides a GUI to any database (including
| PostgreSQL etc), and (IIRC) the ability to create new tables.
| It allows editing in a table-like way (rows are locked during
| editing, if the database supports this), or in a form-like way.
| Queries can be made in a text/SQL-like way, with a GUI, or in a
| form-like way. For all this, it supports data types (number,
| date, lookup-from-another-table etc).
|
| At my previous job, the research scientists had several tools
| built in Access. It was a very fast way to develop a UI, and
| the IT industry is less efficient now this is no longer
| commonly known or understood.
|
| I think Access is Microsoft's best software. It's a very
| powerful tool, but was also very accessible. You can drag-and-
| drop to create multi-table/view queries without understanding
| SQL, then switch the mode and see the SQL. Once you have the
| query, you can drag-and-drop to create a form (to edit the
| data) or a report (to format each row as a page to print out
| etc).
| wil421 wrote:
| I love it when excel decides a bunch of digits is a date or
| decides to remove leading 0000s from a very important number that
| needs 0000s. Or maybe it wants to make a bunch of digits into
| scientific notation. I would prefer if excel left my cells alone
| or just asked what I wanted to do.
| washadjeffmad wrote:
| Does anyone here use Excel in any capacity on production data in
| the biosciences? If so, did you go to college for the
| biosciences, or are you supplying work using a tool you learned
| under another discipline?
|
| I'm always fascinated to learn how Excel makes its way into
| unusual places.
| lofatdairy wrote:
| Not in biotech, but have worked with people who use a
| tremendous amount of excel, especially in biology. A lot of the
| people still using Excel either came before bioinformatics
| libraries got really really good, had to basically teach
| themselves how to use a computer to deal with bio data, worked
| in a lab that had protocols in place for data that used Excel,
| and/or haven't gotten around to teaching themselves R. You have
| to keep in mind that Excel is extremely good at being a visual
| representation of data. R, Python (only really adopted in
| recent years), and Matlab (which I don't see too often in bio)
| are all excellent for manipulating the same data as Excel, but
| if you wanted to quickly scroll through your RNA sequencing
| counts, Excel provides a fast, (almost) out-of-the box way to
| get a "big picture" view of your data (I haven't met anyone
| doing bioinformatics in SAS or STATA, but I know people who
| have used those software in academia for other fields).
|
| Even if no one is directly manipulating the data in Excel, if
| you don't import the data correctly, or forget to _not_ save
| the data, you'll end up fucking it with Excel's auto-
| formatting. These subtleties lead to things as mentioned in the
| article, but aren't things that the ordinary person learns
| except through mistakes. Nobody is born knowing what tools to
| use or how to use them, but Excel is one of the first pieces of
| software that deal with data for a lot of people, and as such
| one of the first things they turn to when faced with a new
| problem.
| mdb31 wrote:
| 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 Excel, MSFT
| and The World in general) on social media is much, much more
| fun...
| hoseja wrote:
| The easy action should be safe by default.
| mdb31 wrote:
| 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...
| Linosaurus wrote:
| > Any change to Excel's 'open a worksheet' logic would
| break so many workflows it's just not funny anymore.
|
| My preferred work flow would be to open the csv, change
| some formatting, then reinterpret the original data.
|
| It would require keeping two sets of data for each cell,
| but that seems to already be the case since F2-enter on
| each cell would accomplish exactly that. Last I tried.
| Ekaros wrote:
| 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.
| tidenly wrote:
| In Japan everyone passes around CSVs in shift-jis still for
| exactly this reason - the default opening action of Excel with
| CSVs.
|
| Its easy to say its people being dumb, but at this point I
| really wish excel just wasn't so confident in itself and
| actually asked you during the default open operation what you
| want to do.
| netsharc wrote:
| It uses Windows-1252 in Europe...
| imtringued wrote:
| >then use the 'From Text/CSV' wizard to specify the data types
| for each column in your data source, neatly bypassing this
| entire issue.
|
| Something people do not understand is that type information in
| CSV files is conveyed out of band. The application that is
| reading the CSV file must know the datatype which effectively
| turns each CSV file into an application specific format.
| hobofan wrote:
| > which effectively turns each CSV file into an application
| specific format
|
| Thanks for writing that! I don't think I've seen the reason
| for my (partial) dislike of CSV put into words that clearly
| before.
| loufe wrote:
| Good initiative, but there are hundreds of used languages, I hope
| they put a bit more effort into it than just testing the English
| dates cases. I caught one bad example, for any French speakers,
| in the article itself:
|
| and MARS tweaked to MARS1.
| joshvm wrote:
| Plenty of gene names are partial/full backronyms that don't
| really bear any relation to their function. For example the R
| in MARCH stands for "RING Finger", RING means "Really
| Interesting New Gene" [0]; finger here is some substructure
| that I guess looks superficially like a finger on the protein.
| That's an astronomy-worthy acronym if ever there was one. You
| could probably avoid a lot of this if the naming conventions
| avoided using protected words, but then they're a lot less
| catchy and easy to remember. Also I don't think Excel has a
| list, but it might be useful to make one.
|
| [0] https://pubmed.ncbi.nlm.nih.gov/24928685/
| Semaphor wrote:
| At the time (506 comments):
| https://news.ycombinator.com/item?id=24070385
| adamors wrote:
| pelasaco wrote:
| Looks more like the scientists are choosing the wrong tool to do
| their job. Sure you can use excel, to get a sense of the data,
| but for god sake, there are better tools for that - or even
| contribute to an existing open source one to do what you need.
| I'm quite sure scientists could get some grants to hire actual
| software engineers to come up with a better solution, based on
| the open source options existing there.
| hpcjoe wrote:
| > I'm quite sure scientists could get some grants to hire
| actual software engineers to come up with a better solution
|
| Uh ... no. Grants rarely ever support SWE outside of a core
| application. No granting agency would support writing a new
| open source tool that effectively replicates what is available
| in market today.
|
| You are right in that genomic scientists chose poorly here.
| Excel isn't the right tool for the job, but there are very few
| options that could work, and the others required some assembly
| ... which they couldn't get money to fund. And the other
| potential solutions were not ubiquitous.
|
| For them, renaming genes is the easier solution than switching
| workflows to new (to be assembled) tooling. Remember,
| scientists are people too ... they will opt to take paths of
| lower resistance even when they are suboptimal.
| [deleted]
| adhesive_wombat wrote:
| > Be conservative in what you send, be liberal in what you
| accept.
|
| This is why I think that's wrong: if you allow people to be
| sloppy with how they do things, they'll do it, and then make it
| part of their workflow, product, religion or whatever, and now
| everyone is stuck with it.
|
| Be absolutely explicit with what you accept and refuse to deal
| with crap. Then you will only ever have to maintain a simple
| validator and the code that deals with good data, rather than
| having to have an incredibly hairly validator that leaks into
| your logic at every level, followed by cementing your bugs into
| everyone's implementations.
| cjsawyer wrote:
| Then your competitor accepts messy data and you go out of
| business.
| adhesive_wombat wrote:
| Then file a bunch of bugs on their product relating to messy
| data in various pathological states and tie up their
| engineering for months. Game on!
| rngcntr wrote:
| Who said Bill Gates wants to manipulate our genes? ;)
| nulbyte wrote:
| > 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 run into this
| problem all the time with those pesky 16-digit account numbers.
|
| But, while Microsoft might do well to make changes to address
| their poor implementation, someone somewhere will implement the
| next best thing and screw it up, too.
| nicoburns wrote:
| 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
| zokier wrote:
| Parquet is not plain text (but that is an oxymoron anyways),
| but otherwise afaik it is pretty solid data interchange
| format supported by a number of tools already. It doesn't
| have native formatting system, but it does allow arbitrary
| key-value metadata on columns so surely something could be
| cooked up with that.
| cyxxon wrote:
| 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.
| nicoburns wrote:
| I'd quite like these files to be openable in simple
| viewer/editor software that might not support formulas. I
| think once you support formulas you open pandora's box with
| regard to which formulas are supported.
| olau wrote:
| UTF-8 CSV with a few HTML tags for formatting?
|
| You could call it CHU.
|
| Now you just need to make Excel accept it.
| nicoburns wrote:
| I'd like it to have support for things like "this is a
| <format> cell", where format is something like text,
| numeric, date, currency. I'm not sure how you'd specify
| that in HTML.
| ElectricalUnion wrote:
| > text, numeric, date
|
| I think it's already defined?
|
| <input type="text">, <input type="number">, <input
| type="date">
|
| > currency
|
| You probably need to use the microdata format
| https://schema.org/Offer
| mcv wrote:
| 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.
| criddell wrote:
| Sounds like MS Access.
| jmchuster wrote:
| Similarly, if your last name is "Null", sometimes it's just
| easier to just change your last name.
| hamasho wrote:
| For reference [0].
|
| It should be tough to have such a name. The most important
| systems like tax/insurance/airline booking are often the most
| unkind systems. If you're not familiar with computers, it's
| almost impossible to imagine the potential cause of problems is
| your name.
|
| [0] https://www.bbc.com/future/article/20160325-the-names-
| that-b... > These unlucky people have names that break
| computers
| jmchuster wrote:
| I had a PM who would get deleted out of the company directory
| every 6 months. She resolved the issue by getting married.
| hpcjoe wrote:
| This has got a "Bobby Tables"[1] vibe to it. While I personally
| do not like the concept of adjusting data to fit a tool, as
| others have pointed out, Excel, for better or worse, is
| ubiquitous. To the obvious detriment of genomics. And likely
| others.
|
| Any tool that seeks to replace it would need to be as easy to
| use, as flexible. And preferably fewer bugs.
|
| The problem is that the paradigm for spreadsheets works well
| for many projects, even if the implementations are crap.
|
| [1]
| https://www.explainxkcd.com/wiki/images/5/5f/exploits_of_a_m...
| jdrc wrote:
| To Nan
| hoosieree wrote:
| Don't change it to "None", though. That's just a curse for your
| grandchildren to deal with when Python eventually becomes the
| new old legacy system.
|
| "Nil" is probably safe, because the lisp curse will actually
| work in your favor.
| imtringued wrote:
| >Even then, a scientist might fix their data but export it as a
| CSV file without saving the formatting.
|
| Is this supposed to be a joke? Since when does excel support CSV
| files? Yes you can import and export CSV files but that is just
| there to check a box. That feature doesn't actually work. Just
| import and export .xlsx files in your applications directly.
|
| CSV is such a bad format because it's not even a standard, there
| is RFC4180 but most people have never heard of it. CSV is
| complicated enough that anyone who thinks they can implement it
| will get it wrong on their first attempt but simple enough that
| people believe they can implement it on their first attempt.
| rini17 wrote:
| Opening CSVs in Excel almost never worked for me. Good thing
| there's LibreOffice Calc with spiffy import dialog with all the
| csv options/variants where you can preview the result.
| joenathanone wrote:
| Excel has exactly the same functionality.
| rini17 wrote:
| Good, since when? Last used it 10ish years ago.
| joenathanone wrote:
| Here is the feature in Excel 2007
| https://youtu.be/ML0VDMpWllM around 2:45, could have also
| been there in earlier versions too.
| humanistbot wrote:
| I love how you gave advice in your parent post as if you
| knew what Excel's current features are, then admitted you
| haven't used it in 10 years.
| max599 wrote:
| at least 20 years, probably more.
| prepend wrote:
| I think the import wizard was added in office 2000.
| dwringer wrote:
| It was there, the problem is that by default if you open
| files the way most people do (double-clicking or
| dragging) then Excel decides you must not want to be
| bothered by the options so it fills them in for you.
| "Helpfully" not even using the most basic inference to
| assign types in a way that doesn't destroy the data.
| jasode wrote:
| _> Since when does excel support CSV files? Yes you can import
| and export CSV files but that is just there to check a box.
| That feature doesn't actually work. Just import and export
| .xlsx files in your applications directly._
|
| You're being hyperbolic and your .xlsx advice doesn't apply to
| _other systems we don 't control_ that only offer .csv files.
|
| Examples... my credit card website and Ebay only offer csv
| downloads of data. I use MS Excel to import those csv files all
| the time and it works well enough.
|
| Yes, I'm aware of potential data-conversion flaws with
| importing csv files. (My previous comment:
| https://news.ycombinator.com/item?id=25017116 )
|
| All those caveats with csv are irrelevant when the system that
| has the data people want only offers csv. Using Excel's feature
| to import csv -- and being aware of the format dangers -- is
| more practical than manually retyping all the data from
| scratch.
| AviationAtom wrote:
| Isn't this solved by putting a single quote, or something of the
| sort, in front of the data?
| teh_klev wrote:
| Yes, but if you're opening a CSV file, some folks don't
| "import" the CSV which would give you a chance to specify the
| data types for each column:
|
| https://news.ycombinator.com/item?id=30986868
| chipsa wrote:
| Part of the problem, highlighted in the article: Users don't set
| the data type of the column off of "General". So it tries to
| figure out the data type on entry. Setting the column to use a
| specific type ("Text"), keeps it from trying to use heuristics to
| figure out that something is a date.
|
| Something more recent is the introduction into Excel of Power
| Query, which lets you import a CSV and apply arbitrary
| transformations (such as applying a type), before it hits the
| workbook, so if you need to pull in a CSV, you can do so, and it
| will always be imported the same way.
| [deleted]
| burntoutfire wrote:
| Reality adapting to software (instead of the other way around)
| seems to be more and more common. Just today, my local library
| sent out email informing users that, while they agree that they
| shouldn't charge late fees for non-work days, the software
| company they use doesn't want to introduce such feature into the
| software, so they have no way but to charge for those days.
| max599 wrote:
| Excel allows you to force a specific type during file import or
| directly on the worksheet with literally two clicks once the
| region is selected. You can also create templates if you want
| all your new documents to use a specific type.
|
| The scientists are not adapting to the software, they are
| adapting to the incompetence of people in their research groups
| who refuse to learn how to use their main work tool and/or
| don't want to do the extra 2 clicks it takes to select "text".
___________________________________________________________________
(page generated 2022-04-11 23:02 UTC)