Querying a CSV file directly with SQLite, no import step

You can run a SQL query directly against a CSV file using the sqlite3 command-line utility, without ever creating a database file:

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

This opens an in-memory database with the special :memory: filename, uses two -cmd options to turn on CSV mode and import taxi.csv into a table called taxi, then runs the query against it. No schema, no CREATE TABLE, no separate import step.

The output looks like this:

"",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

Add -cmd '.mode column' to get readable columns instead:

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

Or -cmd '.mode markdown' to get a table you can paste straight into notes like this one:

A full list of output modes:

% 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

If you outgrow the one-liner — joining several files, or querying JSON and CSV together — dsq is a solid next step; same idea, more formats.