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!).
Do you have any recommendations for getting started with this?
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.
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
You don't need to write your own GUI for databases. Loads exists.
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
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_...
But yeah great feature, and thanks for the reference.
> Also filtering is essential.
> So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
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 :)
> As long as it's not tedious to set a few thousand that sounds great.
> SUM, COUNT and IF (if-then)
Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:
I'm old school, so ctrl+c, shift+end (maybe tweak selection area with cursors if Excel is being dumb) and ctrl+v.
The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.
That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.
It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.
In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.
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).
https://github.com/dotancohen/structuresheet
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
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.
Python / Qt / SQLite
I don't see any part that gives a strongly-typed properties.
Do I miss something?
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.
> "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
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.
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.
MS Access was the usurper of dBase's crown. And then Excel took over ...
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.
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.
It's open source and hosted in the cloud. It has an excel like interface that you can share and work on with others.
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 :)
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.
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.
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".
Why not?
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.
Having a single database and giving two other people access to it keeps the data centralised and keeps a single source of truth.
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.
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.
> The problem is that databases do not include a GUI for data viewing.
This invite goes for anybody else who [ab]uses Excel even though they are versed in SQL.
Actually I would change that from your answer to
"Access didn't come with the cheapest version of Office"
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.