My latest weekend project. Mixing the scalability of SQL and with the easy of use of spreadsheets

I've been processing a lot of local CSVs lately. Often feeling like the tools I'm using don't behave quite how I would like them to.

I built a prototype of an application remedy these frustrations. And also explore some existing solutions to fix similar problem using tools like Postgres, SQLite, HTTPie, Bash and JQ.

[wolfeygamedev]

oh no my guy built access with postgres...

[DerCrafter]

I usually use datagrip, it supports editing sql tables like a spreadsheet.

[samuelswatson]

There's a really nice project called Teable that does exactly this.

[georg]

I usually use dataframes for local data processing. I convert CSVs and SQLite tables into dataframes, calculate/query something and convert the dataframe back to CSV or SQLite. In Julia the packages used for this are DataFrames.jl, CSV.jl and SQLite.jl, but other languages also have dataframe implementations, e.g. python (pandas data.frame), R (dplyr data.table) and Kotlin (dataframe).

[_modiX]

Well, you reinvented the wheel. You essentially need to look at headless CMS technology such as directus or strapi. It's literally just an UI on-top of a database and they offer features like REST/GraphQL API and authentication and permission layer. To catch your final use-case (Excel) you could write an Excel plugin that connects to a directus/strapi instance and go from there.

[ClasicRando]

I've definitely run into this type of thing before when I was working as a data analyst. I ended up making a custom python desktop GUI that could import several data formats (e.g. excel, csv, dbf) into a local SQLite database for easier analysis and then a table UI to see/query the data.

Not sure if you had already considered/used this, but if you end up working with postgres, its COPY command would be super helpful (similar to the SQLite feature you mentioned). As long as your data format is transformable into a CSV stream, you could bulk copy any data source into your local postgres database using most postgres clients. The COPY command also allows you to copy out data from a query as a CSV stream.

[joed]

You described Microsoft access. It is a user friendly interface that can use its built in JET database engine or use it as a front end for mssql server or any ODBC accessible database.

[dangerousdansg]

Feedback: Audio feels really soft for this video compared to your other ones

[AstorSkywalker]

You need to reformulate the PROBLEM. How would you handle relations between tables and NORMALIZATION ? Oracle has something called EXTERNAL TABLES. Basically, csv files as tables.

[DerSolinski]

I'm trying to replace a abomination of a multi file and multi user excel macro monster accessed via RDP with data structures that are more graph like then relational.
EVERYTHING is done in excel... The UI, the reports, the shipping label printing and the entire business logic.
Guess how much fun I have. I've tried over the years multiple approaches to slay the beast but it constantly finds new ways to elude.
Currently I'm learning to write a Interpreter so I can write something capable of parsing all the relations between the VBA, formulas and cells spanning over the multiple files.

[oblivion_]

My dad built something exactly like this for the financial interface for the lottery system owned and distributed by Tabcorp

[outwithrealitytoo]

NOTE: If exporting to flat text file then use TSV (tab separated values) not CSV... you will save yourself a world of non-standardised escaping pain.
TSV is achieved on Excel by "save as csv" then choosing tab as the separator... because MS UIs Imports likewise require an extra couple of clicks, but it beats the occasional record with transposed fields.

[jalenh]

Python + pandas in a Jupyter notebook handles this type of stuff extremely well. It definitely isn't the same experience as a spreadsheet, but it is generally much more powerful IMO.

[gobdovan]

Yeah, look into datagrids such as ag-grid. These projects incorporate already probably most of the features you will ever need for the presented use-cases. I also recommend people that start businesses on a spreadsheet (a dietary recommendation one pops to mind) to convert it to one of these datagrids.
For the 'more convenient interface on top of the database' part, there are already lots of projects geared towards that specifically, ranging from admin panels like the Django one, to things like, surprise, exactly what you tried to present in the video: Airtable, "a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet" (lifted directly from wikipedia)

[TimothyRogers]

Isn't this just like nocodb or bsserow?

[brnto]

2:06 That flow is describing most web apps too!

[xing]

Very interesting video! I'm not personally aware of any tools specifically like this. I do have one nitpicky bit of criticism, though. I think you meant, "ease of use" in the title instead of "easy of use".

[karliszemitis]

You know that Grist exists and is self-hostable?

[alexandrodisla]

Python does this with ease. A shame the other languages doesn't offer musch for basic data analysis.

[jcorey]

I'm just much more familiar with python programming, so I do pretty much anything you do here in pandas