csvkit csvsql: Run SQL Against CSV Files

csvsql runs a SQL SELECT (or generates DDL) against one or more CSV files using a throwaway in-memory SQLite database.

For joins, aggregates or filters that get awkward in awk, csvsql lets you write plain SQL over CSVs without standing up a database.

What it does

csvsql loads each input CSV as a table (named after the file) into an in-memory SQLite database, runs the --query SQL, and prints the result as CSV. Without --query it emits a CREATE TABLE statement inferred from the data.

Common usage

Terminal

# run a query against one CSV
csvsql --query "SELECT name, age FROM people WHERE age > 30" people.csv
# join two CSVs (tables named after files)
csvsql --query "SELECT o.id, c.name FROM orders o JOIN customers c ON o.cust_id = c.id" \
  orders.csv customers.csv
# generate CREATE TABLE DDL only
csvsql -i postgresql data.csv

Options

Flag	What it does
--query <sql>	SQL to run against the loaded tables
--tables <names>	Override the table names for the inputs
-i, --dialect <db>	SQL dialect for generated DDL (sqlite, postgresql, mysql)
--db <url>	Use an external database via a SQLAlchemy URL
--insert	Insert the data into the --db database
--no-inference	Treat all columns as text

In CI

Validate a data artifact with SQL: csvsql --query "SELECT count(*) FROM rows WHERE total IS NULL" out.csv and fail the job if the count is non-zero. The file basename becomes the table name, so weird-name.csv needs --tables clean_name because hyphens are not valid SQL identifiers.

Common errors in CI

OperationalError: no such table: my-file means the table name has characters SQLite rejects; use --tables. OperationalError: no such column usually means the header has spaces or mixed case, so quote identifiers. csvsql is slow on large files because it builds an in-memory DB; for big inputs --no-inference speeds loading. Missing the SQLAlchemy/driver dependency for --db raises ModuleNotFoundError.