9 ms·

>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 row

by jasode 5y ago

>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.

JustAQ 5y ago
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.
- smcl 5y ago
  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-ultimatae-excel-worksheet-function/ 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
- jasode 5y ago
  >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 5y ago
    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 5y ago
    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.
    - mort96 5y ago
      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.
- gompertz 5y ago
  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.
- kergonath 5y ago
  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.
dotancohen 5y ago
> 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 5y ago
  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/ https://sqlitebrowser.org/
  - dotancohen 5y ago
    That was my first thought, too. But I think that there is demand for a tool that feels like a spreadsheet, backed by SQLite.
Closi 5y ago
> 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 5y ago
  > 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 5y ago
    To answer my own question: looks like all you need is Excel 2016 or better. https://powerquery.microsoft.com/en-us/excel/ https://powerquery.microsoft.com/en-us/excel/
    - Closi 5y ago
      Absolutely! I would add though that you really need to follow a tutorial/book to make everything make sense in it. I read a book by Rob Collie - but any well reviewed book on DAX should do it. The book “Supercharge Excel - When you learn DAX…” looks like it covers similar information but more up to date. The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table. PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
zozbot234 5y ago
> 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.
charlieyu1 5y ago
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
- andi999 5y ago
  Why shd that be computationally impossible?
- JacobThreeThree 5y ago
  >You are not supposed to have a GUI view when you have 100K+ entries. Why not?
  - KronisLV 5y ago
    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 5y ago
  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 5y ago
gompertz 5y ago
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 5y ago
  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.
mcv 5y ago
> 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.
- alex1431999 5y ago
  There is actually a tool that does exactly what you describe. It's called baserow :) https://baserow.io/ 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 5y ago
  Funnily enough, I think one the original IBM PC killer apps was exactly that - dBase III https://en.wikipedia.org/wiki/DBase https://en.wikipedia.org/wiki/DBase MS Access was the usurper of dBase's crown. And then Excel took over ...
  - jaclaz 5y ago
    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) 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.
- Aloha 5y ago
  A modern web-based cloud-hosted access implementation would be a killer app for web-2.0.
- ChadNauseam 5y ago
  Forgive my ignorance, but isn't that what Airtable is?
- 5y ago
Aloha 5y ago
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.
keithalewis 5y ago
If you know Excel and C++ then you might like https://github.com/xlladdins/xll https://github.com/xlladdins/xll. It is something I wrote to embed C++ in Excel.
pixl97 5y ago
>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"

by jasode 5y ago

JustAQ 5y ago

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.

smcl 5y ago
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-ultimatae-excel-worksheet-function/ 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
jasode 5y ago
>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 5y ago
  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 5y ago
  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.
  - mort96 5y ago
    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.
gompertz 5y ago
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.
kergonath 5y ago
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.

dotancohen 5y ago

> 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 5y ago
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/ https://sqlitebrowser.org/
- dotancohen 5y ago
  That was my first thought, too. But I think that there is demand for a tool that feels like a spreadsheet, backed by SQLite.

Closi 5y ago

> 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 5y ago
> 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 5y ago
  To answer my own question: looks like all you need is Excel 2016 or better. https://powerquery.microsoft.com/en-us/excel/ https://powerquery.microsoft.com/en-us/excel/
  - Closi 5y ago
    Absolutely! I would add though that you really need to follow a tutorial/book to make everything make sense in it. I read a book by Rob Collie - but any well reviewed book on DAX should do it. The book “Supercharge Excel - When you learn DAX…” looks like it covers similar information but more up to date. The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table. PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.

zozbot234 5y ago

> 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.

charlieyu1 5y ago

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

andi999 5y ago
Why shd that be computationally impossible?
JacobThreeThree 5y ago
>You are not supposed to have a GUI view when you have 100K+ entries. Why not?
- KronisLV 5y ago
  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 5y ago
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 5y ago

gompertz 5y ago

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 5y ago
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.

mcv 5y ago

> 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.

alex1431999 5y ago
There is actually a tool that does exactly what you describe. It's called baserow :) https://baserow.io/ 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 5y ago
Funnily enough, I think one the original IBM PC killer apps was exactly that - dBase III https://en.wikipedia.org/wiki/DBase https://en.wikipedia.org/wiki/DBase MS Access was the usurper of dBase's crown. And then Excel took over ...
- jaclaz 5y ago
  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) 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.
Aloha 5y ago
A modern web-based cloud-hosted access implementation would be a killer app for web-2.0.
ChadNauseam 5y ago
Forgive my ignorance, but isn't that what Airtable is?
5y ago

Aloha 5y ago

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.

keithalewis 5y ago

If you know Excel and C++ then you might like https://github.com/xlladdins/xll https://github.com/xlladdins/xll. It is something I wrote to embed C++ in Excel.

pixl97 5y ago

>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"