Tip: using sqlite3 to execute CLI one-liner queries against CSV files

(1) By Simon Willison (simonw) on 2022-06-21 00:18:14 [link] [source]

I figured out how to do this today: https://til.simonwillison.net/sqlite/one-line-csv-operations

sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

This shell one-liner loads the contents of taxi.csv in the current directory into a taxi table in an in-memory database, then runs the SQL query against it and returns the results.

If you leave out the -cmd 'mode column' at the end you get back the results in CSV. With that option you get back output like this:

passenger_count  COUNT(*)  AVG(total_amount)
---------------  --------  -----------------
                 128020    32.2371511482553 
0                42228     17.0214016766151 
1                1533197   17.6418833067999 
2                286461    18.0975870711456 
3                72852     17.9153958710923 
4                25510     18.452774990196  
5                50291     17.2709248175672 
6                32623     17.6002964166367 
7                2         87.17            
8                2         95.705           
9                1         113.6

A full list of output modes can be seen like this:

% sqlite3 -cmd '.help mode'
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements

(2) By Mark Lawrence (mark) on 2022-06-22 07:24:15 in reply to 1 [source]

The SQLite shell .import command command also has a -csv option (among others) so you can simplify your one-liner a bit:

sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

Some may find interesting the following bash function with a few more bells and whistles which I use for quickly investigating csv files:

function csql() {
    local USAGE
    local OPTIND
    local OPTARG
    local columns
    local db=':memory:'
    local file
    local noheader=0
    local table=csv

    USAGE="Usage: csql [-h] [-n] [-d DB] [-t TABLE] [-c COLUMNS] CSV [QUERY]\n"

    while getopts ":c:d:hnt:" opt; do

      case ${opt} in
        h )
            printf "$USAGE" 1>&2
            return 1
          ;;
        c )
            columns=$OPTARG
          ;;
        d )
            db=$OPTARG
          ;;
        n )
            noheader=1
          ;;
        t )
            table=$OPTARG
          ;;
       \? )
         echo "Invalid Option: -$OPTARG" 1>&2
         printf "$USAGE"
         return 1
         ;;
      esac
    done

    shift $((OPTIND -1))

    if [ "$#" -lt 1 ]; then
        printf "$USAGE"
        return 1
    fi

    file="$1"
    shift

    if [ ! -e "$file" ]; then
        echo "file not found: $file"
        return 1;
    fi

    create=""
    if [ -n "$columns" ]; then
        create="CREATE TABLE $table($columns)"
    fi

    sqlite3 \
        --bail \
        --cmd "$create" \
        --cmd ".import '$file' $table --csv" \
        --cmd ".schema $table" \
        $db "$@"
}