The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers
«
»

TIL you can run SQL queries directly against CSV files as a one-liner using the default sqlite3 command line utility

Posted by jpluimers on 2025/04/24

[Wayback/Archive] One-liner for running queries against CSV files with SQLite | Simon Willison’s TILs

I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility:
sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
This uses the special :memory: filename to open an in-memory database. Then it uses two -cmd options to turn on CSV mode and import the taxi.csv file into a table called taxi. Then it runs the SQL query.
Instead of setting the mode with .mode you can use .import -csv like this (thanks, [Wayback/Archive] Mark Lawrence):
sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

Via [Wayback/Archive] Simon Willison on Twitter: “TIL you can run SQL queries directly against CSV files as a one-liner using the default sqlite3 command line utility”

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' Output is an ASCII-art table of results

Thanks Simon!

(yes, another post in what by now apparently is a SQLite sequence)

--jeroen

https://twitter.com/simonw/status/1539038172599836672

This entry was posted on 2025/04/24 at 12:00 and is filed under CSV, Database Development, Development, Software Development, SQLite. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«
»