[dinedal]

You can do this with https://github.com/dinedal/textql as well, and a little easier as you don't need to import the data explicitly.

  SQL_QUERY=<<-EOS
    SELECT 
      category.name AS category, 
      composition.key, 
      composition.composer, 
      composition.name AS composition, 
      concert.name AS concert
    FROM 
      category, 
      concert, 
      composition, 
      program
    WHERE 
      julianday(concert.date) < julianday('now')
      AND composition.category = category.name
      AND program.key = composition.key
      AND program.date = concert.date
    ORDER BY 
      category.sequence, 
      composition.key
    ;
  EOS

  textql --header --dlm=tab --sql $SQL_QUERY category.tsv composition.tsv concert.tsv program.tsv

[SahAssar]

Seems like textql is a wrapper around sqlite, I'm guessing staying with sqlite directly makes it easier to interact with other tooling.

One nitpick with textql is that it says "sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.", but that's not true, you just need to tell it to use stdin by doing:

    sqlite3 '.import /dev/stdin {youtablename}'

I do this all the time for using gzip and sqlite to get compressed import with progress. For example filtering a compressed CSV from a SQL query with progress for import:

    pv icons.csv.gz | gzip -dc | sqlite3 ':memory:' -csv '.import /dev/stdin data' '.output result.csv' '.headers on' "SELECT name, keywords from data where name = 'chart-area'"

[tyingq]

Do keep in mind that /dev/stdin{out,err} aren't completely portable. Bash emulates them if they don't exist, but this usage bypasses Bash. So there are unix like platforms where this trick won't work. They do, though, seem to be supported on the ones that are most popular now (Linux, MacOS, FreeBSD).

[kingosticks]

Or with https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/

[seppel]

Or with http://harelba.github.io/q/

[hnjst]

Or with https://csvkit.readthedocs.io/en/latest/

[zimpenfish]

+1 for csvkit because it is extremely useful when dealing with people who love spreadsheets to the point of insanity.