One-liner for running queries against CSV files with SQLite (til.simonwillison.net)

Article summary

The article discusses a one-liner command for running SQL queries against CSV files using the SQLite command-line utility. The command uses the :memory: filename to open an in-memory database, imports the CSV file, and runs a SQL query. The article also mentions alternative tools and methods for achieving this, including the use of virtual tables and other command-line utilities. The output of the query can be formatted in various ways, including CSV, columns, and Markdown tables.

Main themes

• SQLite command-line utility
• CSV file querying
• virtual tables
• command-line option conventions
• strong typing in SQLite
• alternative tools and libraries
• data analysis and querying
• database engines and performance

What commenters say

• Some commenters appreciate the virtual table API in SQLite, but note that it has limitations, such as not being able to push down certain queries to the virtual table.
• Others mention that the CSV virtual table in SQLite is slow and not suitable for large files.
• There are alternative tools and libraries available that can parse CSV files more efficiently, such as zsvlib, which uses SIMD operations and minimizes memory copying.
• Some commenters discuss the importance of following standard command-line option conventions, while others defend non-standard options as being sufficient for their use cases.
• The use of strong typing in SQLite tables is also discussed, with some commenters appreciating the recent addition of opt-in strong typing, while others wish for more comprehensive typing support.
• Visidata is mentioned as a useful tool for querying and analyzing text files, including CSV and JSON files.
• DuckDB is also recommended as a fast and efficient database engine for querying large CSV and Parquet files.