sqlite> .import test.csv foo --csv

    sqlite -column :memory: '.import --csv file.csv tmp' 'select * from tmp;' | bat

For example, this is how I use YouTube. I never use the YouTube website, with its gigantic pages and its "Javascript player", not to mention all of the telemetry. All the search results and information about videos is stored in SQL or CSV, viewed with a text-only sqlite3 output .mode, and optionally converted to simple HTML.

For me, this is better than a "modern" web browser that's too large for me to compile.

Between those two, I can work with not only CSVs, but also JSON and Parquet files (which are blazing fast -- CSVs are good for human readability and editability, but they're horrendous for queries).

CLI CSV tools pop up every now and then, but there's too many of them and I feel that my use cases are sufficiently addressed with only 2 tools.

https://docs.pola.rs/py-polars/html/reference/api/polars.SQL...

[1]:https://www.tadviewer.com/

Of course, there exist plenty of CSV files that don’t follow any particular standard, so the trick doesn’t always work (if there are tabs in the data, if there’s some very wide field, or if there’s a comma in the data). Works good enough for the files I have, though.

I have largely given up on spreadsheets, replacing them with relational databases. And on top of being a world class tool for understanding the data for import, visidata makes for a pretty great query pager.

It's really easy to wallow in the "considering" phase of a decision indefinitely (or maybe that's just me :D)

https://en.m.wikipedia.org/wiki/Lotus_1-2-3

(1) On one hand you can use it for data that is really tabular,

(2) But it also has an engine that can compute the dependency relationships between cells and recalculate the cells affected by a change. This is quite different from conventional programming languages where you are required to specify an order to put operations in. Of course this a problem for exploiting parallelism but I'd charge it is one more bit of cognitive load that makes it harder for beginners and non-professional programmers. (Professional programmers are just used to it and only run into problems in unusual cases where circularity is involved, but I think it's one more thing that beginners struggle with.)

The worst problem is a lack of separation between code and data. If you are doing an analysis you might put something like

  =SUM(A1:A28)

  =SUM(A1:A29)

(Note some of the same problems still exist with "notebooks" and "workspaces" where you wind up with a file that has both code and data in it which can be problematic to check into git, particularly when your are working for people who would like to have a beautiful notebook with an analysis in it to view in GitHub but will then struggle to version control it. Many "data scientists" fail to rise above the December sales report even though there's a clear path to turn a Jupyter notebook into a Python script.)

---

As much as that is a rant I think there's a huge untapped market for things that are like spreadsheets but different. That is, something that looks like Excel but is specialized for editing tabular data (no formulas, CSV import 'just works' all the time, ...) or something that has formulas like Excel but not on a grid or not just on a grid. For the latter there was this product

https://en.wikipedia.org/wiki/TK_Solver

which is still around. People were amazed with TK Solver when it came out and I'm surprised to this day that it hasn't had a lot of competition.

"Unlike models in a spreadsheet, Javelin models are built on objects called variables, not on data in cells of a report. For example, a time series, or any variable, is an object in itself, not a collection of cells which happen to appear in a row or column... Calculations are performed on these objects, as opposed to a range of cells, so adding two time series automatically aligns them in calendar time, or in a user-defined time frame. Data are independent of worksheets..."

That was forty years ago and as far as I know there hasn't been a program that works the same way since. Perhaps someone could reverse-engineer it (the jav.exe file is only 56,448 bytes!)

But, they are not very fashionable.

It would be fun to have a pipeline to compile a spreadsheet, that produces an executable. Potential outputs: cpu, gpu, fpga. Haha.

https://github.com/andmarti1424/sc-im/

https://github.com/dhruvasagar/vim-table-mode

Back then I stopped using sc-im because it could not import/export XLSX, if I remember correctly. Apparently it can today!

vim-table-mode always felt a little fragile and I don't want to be bound to vim anymore. That said, it still feels like a small miracle to me to have functional spreadsheet formulas inside markdown documents – calculation and typesetting all in one place.

Wow!

1. SQLite 2. DucksDB 3. clickhouse-local

- For querying csv data from command line, I use clickhouse-local.

- For querying csv data programmatically using a library, I use chdb (embedded version of clickhouse)

- For querying large amount of csv data programmatically, I offload it to clickhouse cluster which can do processing in distributed fashion.

If you are looking from query performance perspective, this blog is useful: https://www.vantage.sh/blog/clickhouse-local-vs-duckdb

https://github.com/alexhallam/tv

I've enjoyed using csvkit[^0] in the past. The viewer isn't as good as csvlens seem to be, but it comes with the ability to grep, cut and pipe CSV data which has come in handy.

csvlens + csvkit might be a great combination.

[^0]: https://csvkit.readthedocs.io/en/latest/

For example maybe you're doing end of year taxes and now you have this large CSV export from your bank or payment provider with multiple categories and you want to get the totals for certain things.

In a GUI tool it's really easy to sort by a column and drag your mouse to select what you want and see it summed in real time.

Oftentimes things aren't clean enough to have 100% confidence that you can solve this with an automated script because maybe something is spelled slightly different but it's really the same thing. This feels like one of those things where spending a legit 10-15 minutes once a year to do it manually is better than trying to account for every known and unknown edge case you could think of. The stakes are too high if you get it wrong since it's related to taxes.

Has anyone found a really good standalone basic spreadsheet app that "just works" which isn't Microsoft Excel that works on Windows or Linux? I don't know why but Libre and Open Office both struggle to parse columns out in certain types of CSVs and the sorting behavior is typically a lot worse than Google's spreadsheet app but I'd like to remove some dependence on using Google.

[1]: https://www.visidata.org

With a SQL / code approach you have to account for these things without being able to see them and then adjust the code afterwards to include your custom groupings. It ends up taking more time. If the categories didn't change every year it would for sure be worth it to code up a solution since you'll know the edge cases by looking at the existing CSV but it's a moving target because it could change next year.

https://github.com/andmarti1424/sc-im

    E: Failed to fetch http://archive.ubuntu.com/ubuntu/pool/main/e/evince/evince-common_42.3-0ubuntu3_all.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/p/poppler/libpoppler118_22.02.0-2ubuntu0.2_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/p/poppler/libpoppler-glib8_22.02.0-2ubuntu0.2_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/g/ghostscript/libgs9-common_9.55.0%7edfsg1-0ubuntu5.5_all.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/g/ghostscript/libgs9_9.55.0%7edfsg1-0ubuntu5.5_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://archive.ubuntu.com/ubuntu/pool/main/e/evince/libevdocument3-4_42.3-0ubuntu3_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://archive.ubuntu.com/ubuntu/pool/main/e/evince/libevview3-3_42.3-0ubuntu3_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://archive.ubuntu.com/ubuntu/pool/main/e/evince/evince_42.3-0ubuntu3_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/w/webkit2gtk/libjavascriptcoregtk-4.0-18_2.42.1-0ubuntu0.22.04.1_amd64.deb  404  Not Found [IP: 91.189.91.83 80]
    E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/w/webkit2gtk/libwebkit2gtk-4.0-37_2.42.1-0ubuntu0.22.04.1_amd64.deb  404  Not Found [IP: 91.189.91.83 80]

Ed: looks like it is in universe on Ubuntu - you have universe enabled?

https://packages.ubuntu.com/search?keywords=gnumeric

Ed: Did you apt update? Core pieces of evince missing sounds very strange?

awk -v OFS='\t' '{$1=$1; print}' | column -t

BTW I just realized that I was omitting the field separator ‘-F,’ actually.

For those pesky nested commas from top off simplistic text hat still I’d go back to sed for fun and potential profit:

sed 's/","/"\t"/g; s/^"/"\t/; s/"$//; s/,,/, ,/g' | column -t

Not elegant nor hands-off and might be missing something else; def needs the right data / volume and fiddling as we know ymmv; can get unwieldy quickly etc etc

TLDR; use column -t when applicable

For instance, the shown example filters for "Bug" but seems to filter for rows that contain "Bug" in any of the columns. Can I specify to filter for "Bug" only looking at a certain column?

At the same time - is filtering by numbers implemented, like filtering for rows that have a numeric value above X in a certain column?

If not these should be on TODO list - those are very common operations for .csv type data.

(I have used this utility weekly for almost 10 years. It isn't pretty. It's effective.)

I remember that it was tab-based. Any idea what that could have been?

Call me lame, but are there any open-source projects that accomplish this type of gui in typescript? I have an idea but it uses something written in JavaScript.