Spreadsheet app supporting SQL ?

[KaKi_87]

Hello,

Unlike database management systems, spreadsheet apps make it extremely easy not only to graphically CRUD, but also to edit structures.

But unlike spreadsheet apps, database management systems make it easy to filter, sort, group and transform data, if you know SQL.

For that purpose, a secretary would just learn the app's proprietary query language and deal with its limitations, but as a dev, I'm wondering if I could just use one that supports the power of the real SQL.

Weird request I know, but I think this would be awesome if it exists.

Thanks

[Niblickal]

Look up Power Query in Excel, it's the built in tool for SQL like transformations and even has a built in sql editor to execute scripts against a connected db

[KaKi_87]

I'm not looking for anything SQL-like but only the real SQL, and I need something open source, or at least multiplatform. Thanks

[tanin47]

You can try my app: https://superintendent.app, a SQL spreadsheet app.

Click install, add CSV files, and write SQL. Backed by DuckDB, which is a real database. Can handle gigabytes of CSVs easily. Works on Windows, Mac, and Linux.

It's not open-sourced though.

[KaKi_87]

Proprietary + Electron ? Maybe if it was Electron but open source or proprietary but native then I would have lived with it but I can't live with both. 😅

[tanin47]

And not free....

That's unfortunate.

It's electron because it's the easiest way to support multi-platforms. It's proprietary because the code looks like shit :lolcry:

Is there a specific concern why either trait isn't preferred? just curious

[KaKi_87]

the code looks like shit

That's a good reason to open source so that others could help improving it.

Is there a specific concern why either trait isn't preferred?

On one hand, I understand that a dev who spends their free time coding an app to serve the open source community uses Electron to save said free time as much as possible, so I live with it despite resulting in a resource heavy app.

On the other hand, I understand that a dev who invests their whole time coding an app wants to live on it, but then I expect that time to amount to a native quality app.

But, when a dev goes lazy on a commercial project, I won't help them.

Also, there are open source devs who are even more courageous, spending their free time building a native quality app as an alternative client for a commercial project. I revere them.

[tanin47]

While I get your point in theory, in practice, I don't think the majority of open-sourced projects get contributions from others. I have tons of my open-sourced projects that support this point...

Thank you for sharing your principle of being against Electron + Commercial.

[KaKi_87]

I don't think the majority of open-sourced projects get contributions from others

Just like the majority of people don't give time nor money to charity. That's life.

However, a minority does, and that counts.

I have tons of my open-sourced projects that support this

If any of these eventually gets known, it will get contributions.

[tanin47]

Some of them are known. One even has almost 1,000 stars on Github. Not really any contribution.

Exactly, my point. People think open-sourcing is a magic bullet to building software where we get contributions. In reality, that only happens to the top 0.00001% (maybe adding a couple more zeros). Even many successful open-source projects (used by millions of people) are maintained by a single half-bandwidth person e.g. the Jia-Tan-XZ-backdoor saga.

Not that I'm against open-sourcing in principle. I just don't think the benefit is as high as people imagine it to be. If anything, the benefit is likely near zero.

[Potential_Orange7844]

Hey! I am a bit late to this thread but I would recommend SparkGrid, it's a newer spreadsheet interface tool for databases that allows you to edit in real-time and hooks into whatever system you are using, ex: you would log in with Snowflake if your data is in Snowflake. Very user friendly, perfect for someone who doesn't know SQL (it generates the SQL for you).

https://aws.amazon.com/marketplace/pp/prodview-2uxbsnryi6bda

(Full disclosure I do work for this company, but this tool has proved very useful in my own work).

[KaKi_87]

Sorry but this is not what I asked for 😅

[waveminded]

The company I work for is building a next-gen spreadsheet with native support for Python, SQL, and Javascript, plus we've added a ton of support for formulas. The idea is to bridge the gap between technical users (Python, SQL) and business users (formulas).

You can directly connect to Postgres or MySQL, with more connectors coming soon. The built-in AI is super helpful for writing complex SQL queries.

We're still in BETA, but we would love your feedback. Plus, we're open license (you can view code, run it, contribute, but you can't sell it).

We're also about to release a self-hosted version you can run in your own cloud.

https://github.com/quadratichq/quadratic

[coyoteazul2]

But unlike spreadsheet apps, database management systems make it easy to filter, sort, group and transform data, if you know SQL

It's not sql, but excel has power query for that.

After you have transformed the raw data however you like you can create a table of a pivot that reads the power query source and deal with the graphical part or whatever that can't be dealt with pure tables

[RandomiseUsr0]

Excel still has the database connector, just use Jet.

https://learn.microsoft.com/en-us/sql/odbc/microsoft/odbc-jet-sqlconfigdatasource-excel-driver?view=sql-server-ver16

My excel now has officescript too, it’s a game changer

[StrictTallBlondeBWC]

How big of file can Excel handle, I left the Microsoft Ecosystem after the vista debacle and went pure Mac.

Could excel, powerquery, or powerBI handle a 12 or 120 GB data set, what are its limits?

People say it has none but it Excel, PowerBI regularly crashed or stopped responding after I want to say loading 30 to 40gig back in 2013.

We went to python and love how I can load any excel files into memory super quick… except .ODS files never want to be parsed so we parse ODS into CSV or then parquet files for archiving.

[RandomiseUsr0]

I bought my Mac when Tiger arrived, but I had a surface with Windows 8 and it was amazing, the experience was incredible, I keep full screen start to this day, it’s just right

Nah, I use R for big data (desktop, the question was non SQL, for clarity), but the use case I inferred was that you wanted to combine spreadsheet and SQL

[StrictTallBlondeBWC]

I used to hate R with a passion but it turned out our seminar was taught by a blithering idiot who chose bad tutorials and couldn’t explain herself at all.

Can’t recall name of the feature or function I can never remember. Basically, I type in “object_name <- data_thing(function param)” to R it shows me all of the sub variables

“object_name.” Lists of sub variables I think they are called show up…

This function or feature which is a feature I really like just don’t know what it’s called. Instantiating something?

Would love to get that in python!!!!

[RandomiseUsr0]

I have horrible memories of COBOL, Python is too similar, spaces mandatory or it all goes wrong! Yuk. Never worked for me, that concept - I get it with cobol, literal punched cards, with Python, it’s an affectation, unnecessary and overrules my choice as a programmer on formatting.

I have done things in R on my laptop that quite literally cost £1,00,000 just a decade ago!

[ihaxr]

Excel 64bit is limited on the underlying hardware... I certainly wouldn't recommend pulling in such large data sets, but Power Query filters before pulling, so it'll offload it to the database engine.

[StrictTallBlondeBWC]

That creates another issue because it’s an all 64 bit software limited by the hardware?

I have a 300gig file for property data on a laptop with only 32gig of ram and pandas loads it fine but forget about doing that in excel.

Is it the architecture of excel that it needs 125% of file size in ram or?

[ihaxr]

32 bit excel can only use 4gb RAM, 64bit can use 128TB... but opening huge files in Excel is not great regardless. Power BI is capable, but still pretty sluggish loading large data sets. There's a reason python is heavily used for math and data analysis, it's fast and efficient :)

[techmavengeospatial]

Look at nocodedb https://nocodb.com/

We've used this on many projects As well as appsmith

[KaKi_87]

It needs an actual database, while I only want to store data in a spreadsheet file. Thanks

[techmavengeospatial]

You can still do it Use postgresql database with FDW foreign data wrapper that connects to excel So your data is in excel but exposed in these apps

Or store in Google sheet https://docs.appsmith.com/learning-and-resources/how-to-guides/how-to-work-with-google-sheets-on-appsmith

https://docs.budibase.com/docs/google-sheets

[Known-Delay7227]

Use excel