[HN Gopher] A Relational Spreadsheet
___________________________________________________________________
A Relational Spreadsheet
Author : dustingetz
Score : 97 points
Date : 2023-02-26 15:21 UTC (7 hours ago)
(HTM) web link (kevinlynagh.com)
(TXT) w3m dump (kevinlynagh.com)
| inglor wrote:
| Excel has get and transform that makes this easy + an odbc
| provider (you can query excel as an sql database).
|
| A reminder for everyone excel web is actually entirely free and
| you can create stuff with https://excel.new
|
| We spent a lot of time making it faster and rewrote a bunch of it
| ^^
|
| (I'm just an engineer, I don't speak for my corporate overlords)
| KRAKRISMOTT wrote:
| How's Excel web's feature parity?
| capableweb wrote:
| excel.new has a "weird" TLD, no mention of Microsoft in the
| domain and asks me to auth with my Microsoft account before I
| can even see the site. If I came across this in the wild, I'd
| for sure think this is some phishing or generally bad website.
| switch007 wrote:
| Agreed. That's bad even for Microsoft.
|
| What is wrong with excel.office.com with a version toggle?
| arjvik wrote:
| excel.new is a shortcut to creating a new spreadsheet, not
| a brand new version of Excel
| cldellow wrote:
| Those are all valid points, of course, and you can navigate
| to excel.office.com if you don't trust the OP (although it
| will also immediately redirect you to an auth window)
|
| The .new TLD is pretty nifty. sheets.new gets you the same
| thing as excel.new, but for Google.
| nhatcher wrote:
| Hi inglor! As someone who has been writing spreadsheets engines
| for the last 10+ years I am a big fan of how Excel has taken
| the lead in innovation in the last couple of years with dynamic
| formulas and the LAMBDA. So congratulations on your part of it!
| BTW, I must have been interesting to find an algorithm that can
| evaluate the new dynamic formulas. I would like to see one day
| more public information on spreadsheet evaluation algorithms.
| As the people from sourcetable[1] put it:
|
| > Spreadsheets are one of the hardest things you can build --
| right up there with compilers.
|
| I think I would like to see a "democratization" of the
| technology and find books, and lectures about it as popular as
| we find books and lectures about compilers.
|
| As for the OP I find their take interesting, of course you can
| accomplish that with more established tools but it was a
| refreshing read and an interesting, if not inspiring, notation.
|
| PS: Is it possible that there is a regression when computing
| sheets with lots of open ranges (like =SUM(D:D))
|
| [1]: https://www.sourcetable.com/jobs
| gigel82 wrote:
| That link took me through 6 redirects, at least 2 rendered
| flashes of various colors in the browser.
|
| I love Excel, BTW :) just wanted to let you know in case you
| want to make it even faster (and easier to onboard presumably
| based on the domain).
| plaidfuji wrote:
| Bigquery is so deeply integrated with Sheets these days that you
| can establish live links from specified tabs to BQ (applying a
| SQL schema in transit), and vice versa, such that you can build a
| SQL layer into your spreadsheet "backend". Bigquery is so cheap
| on data of that size that you'll never realize significant cost,
| and provides a great query builder GUI on top.
| chroteus wrote:
| Just throwing it around, but there is Apache Calcite, a DBMS
| without the Database part. It's an SQL parsing, logical &
| physical plan building framework. It's pretty much a DBMS without
| any database of its own. You can program up an adapter for
| "whatever you want" pretty much and query "whatever you want"
| with SQL.
| gregwebs wrote:
| I am loving using coda.io for small data. It's easy to create
| tables that reference each other. Tables are part of a document
| and you can write text and make them a little bit interactive
| with slider controls, etc.
| Bloating wrote:
| Grist?
| pcthrowaway wrote:
| Yeah I was going to say the same. For the unfamiliar it's an
| open-source collaborative spreadsheet program that can be self-
| hosted and shared within your org. The sheets themselves are
| actually sqlite tables, which brings some constraints you might
| not get with traditional spreadsheet software.
|
| I believe it also allows you to run python code for data
| processing and jobs
| notpushkin wrote:
| The UI is kinda nice. I'd love something like that for plain old
| SQLite.
| anigbrowl wrote:
| [Laughs in pandas]
|
| I don't want to be dismissive, this is nice work and it's clean
| and lightweight. But it might be good to look at existing
| solutions in this area - pandas was developed within the
| financial industry to solve _exactly_ this sort of issue. If you
| need more topological flexibility there is xarray, and if you
| need spreadsheet type immediacy it 's worth looking into Mito.
|
| Rustaceans should look into pola.rs: https://github.com/pola-
| rs/polars
| chaps wrote:
| [Sobs in hours spent in pandas documentation]
|
| Or, just use a relational database.
|
| Alternatively, airtable and similar are basically relational
| databases that have some really neat features that let you
| create relationships by just copying/pasting data, or importing
| CSVs. They're limited in a lot of ways, but it solves a certain
| set of problems that can't be solved with code or excel.
| anigbrowl wrote:
| I went through that as well; Wes McKinney is super-smart, but
| not the best teacher, and unfortunately his style of example
| has become a norm ion Stack Overflow etc.
| import pandas as pd df =
| pd.whaaargarrrrbllllll[(['what']['the']['fuck'),
| is.this['shit'], I, mean, seriously] (outputs)
| df[.astype('int64').fillna('spork')
| df.groupby[['uppers']['downers']['all
| arounders']].join(inner, child, trauma, (yes && no))
| df['confused'].very(simple['example']) # the thing being
| explained
|
| I'm exaggerating, but not by much. Most examples in
| documentation or McKinney's tutorial work is presented as a
| complete small program in a REPL, and while that does make it
| easy to follow along by imitation, learning pandas feels like
| a painfully fragmented process at first. Also, tehre's a
| widespread assumption among pandas experts that people coming
| to pandas are already familiar with SQL, even more than
| Python in fact. I'm sure this reflects the initial user base
| and to bfair it's probably a true assumption for a lot of
| folk. But if you came from a more CS or scientific context
| rather than a database one, it's anotehr avoidable layer of
| confusion.
|
| I can't recommend a book unfortunately - I just worked with
| McKinney's own materials and suffered for a while until
| things started to click. Once I realized what I found
| frustrating about the tutorial materials I began to realize
| that I could read it more selectively - and also that the
| code base is in constant flux. There are often 2 or 3
| different ways to do the same thing, with different
| approaches being deprecated or promoted over time.
| curiousgal wrote:
| I work in finance and I hate pandas! Also worth pointing out
| that almost all big banks have their own functional languages
| baked into excel that allow not only relational operations and
| extend excel functions but also tapping into their inhouse
| analytics libraries written in C++.
| andylynch wrote:
| Excel's get and transform makes this dead easy I think?
| injidup wrote:
| What about MS Access. I used to use it years ago. It was amazing
| how simple it was to create and deploy an application based on
| relational data.
| dottedmag wrote:
| Very nice piece of software, unfortunately Microsoft didn't
| port it to Mac.
| password4321 wrote:
| Also using Access as a front end on top of MSSQL server.
| sorokod wrote:
| Northwind gives me shivers
| kfk wrote:
| an interesting approach, how about doing the heavy lifting in an
| api backend and pushing/pulling data to the sheet using a custom
| formula? that way you would be able to leverage the full power of
| a db backend
| somat wrote:
| I want the same, I want it so much that I have been working on
| _gasp_ gui code to do such, an activity I have found I am
| profoundly bad at.
|
| But the theory is, I love the relational database, they are a
| sort of rigorous superset of the spreadsheet, and I have replaced
| all my spreadsheets with database tables, however while it is
| very hard to beat sql for rich comprehensive data transforms and
| analysis, ad-hoc data entry is very awkward. Most gui tools are
| focused on database administration and I want one for quick
| random edits. any hints?
| rrrrrrrrrrrryan wrote:
| Microsoft Access is this. It was quite popular in its heyday,
| but as time wore on, it turned out that people eventually
| either want scalable, real, full-blown databases, or infinitely
| flexible spreadsheets. Anything built with a product in that
| grey area in between will eventually want to go one way or the
| other.
| zozbot234 wrote:
| Is SQLite a "scalable, real, full-blown database"? That's the
| scale that Access is going for. (Though it also totally works
| as a pure frontend to a "real" database connection.)
| randomdata wrote:
| SQLite can be used to help build a "scalable, real, full-
| blown database" application. JET, the SQLite-like engine
| used by Access, could theoretically be used in the same
| way.
|
| But the topic here is about GUIs which serve the middle-
| ground between the "scalable, real, full-blown database"
| applications and the lowly spreadsheet. The SQLite project
| offers nothing that is comparable to Access.
| jiggawatts wrote:
| Access had the _capability_ to use a full-blown SQL Server
| back end, while retaining 90% of the flexibility on the front
| end. For example, adding a column in Access would add a
| column in the database table!
|
| We can have our cake and eat it too.
|
| The mistake with Access was that instead of keeping a
| deathgrip on its legacy file-based roots, it ought to have
| "grown up" and become a web-native HTML5 app that uses SQL
| Server back-ends as the only option.
|
| I still think there's a huge market for something like this.
| ghaff wrote:
| We've seen the same phenomenon in word processing/desktop
| publishing. We've mostly seen people coalesce around
| Microsoft Word and its online counterpart/competitors or go
| full-on desktop publishing with InDesign. People have mostly
| moved away from lightweight desktop publishing programs for
| newsletters and the like.
| erichocean wrote:
| > _I want one for quick random edit._
|
| DBeaver fills that need for me.
| burcs wrote:
| We're building www.outerbase.com to support this use case.
| We're in the latest YC batch and are hoping to do a launch HN
| in the near future, but would love to hear your thoughts on
| what we've built if you are up for checking it out!
| hermitcrab wrote:
| Airtable provides an interesting mix of spreadsheet and
| database functionality.
| Lunrtick wrote:
| I've tried a bunch, but many of the prettier ones don't have
| Linux versions. I've been using DBeaver (https://dbeaver.io/)
| for years now though - it's occasionally a little rough around
| the edges, but overall it's really amazing!
|
| It uses jdbc drivers for database support, so it can handle
| basically anything.
| kgodey wrote:
| We are building a open source UI for Postgres DBs that should
| meet your use case: https://mathesar.org. We're doing our first
| release next week!
| chaps wrote:
| How large is the data you're working with? Airtable (expensive,
| with serious row limitations) and its open source alternatives
| (apparently great, but I've never used one) are basically what
| you're looking for. They allow you to create relationships
| pretty trivially. I largely use airtable for collaborative data
| entry work with 1-10 others, where I need to eventually throw
| the data into postgres. It works great. Though, it's hard to
| say where its limitations are until you're a bunch of hours
| into a project -- some features you'd expect to be easy can be
| extremely hard, or limited.
| hinkley wrote:
| I wonder if you drag Notebooks a bit back toward spreadsheets
| if there's a product there.
| totalhack wrote:
| Check out NocoDB for an open alternative to AirTable.
| sublinear wrote:
| > ad-hoc data entry is very awkward
|
| I understand that a spreadsheet "row" often isn't going to be a
| normalized SQL table row and all that, but isn't mapping the
| spreadsheet cell ranges into such a schema technically the only
| problem here?
|
| If you had a script that watches your spreadsheet for changes
| and can drop you into a SQL shell whenever you want, you'd have
| it both ways.
| [deleted]
| SloopJon wrote:
| If you're asking for a GUI for simple CRUD on an arbitrary
| table, I think that's pretty common. What database are you
| using, and what API are you using to access it? (Edit: and on
| what O/S?)
|
| If one of the obstacles to data entry is normalization, perhaps
| an updatable view if your database supports it.
| deepspace wrote:
| > I think that's pretty common.
|
| Not as common as you might think. It is a hard thing to
| search for, and the ones I have been able to find all suck.
|
| I wrote a PHP application a long time that allowed you to
| create a GUI for an 'arbitrary' SQL database table, using an
| XML schema to configure the appearance, validation etc. of
| the fields. Life got in the way, and I stopped development
| before it was ready for prime time. Fifteen years later, I
| went looking for something with similar functionality and was
| surprised at how few options there still were.
| masfuerte wrote:
| A bit more than 20 years ago Microsoft released a desktop
| product that automated producing a CRUD interface from an
| XML schema. It looked very nice but it was immediately
| withdrawn. I was puzzled at the time but I guess now they
| were threatened with a patent. This might explain why there
| weren't more products solving this obvious problem. Patents
| should have expired by now though.
___________________________________________________________________
(page generated 2023-02-26 23:00 UTC)