csvsql

Execute SQL query on a CSV file

$ csvsql --query "[SELECT * FROM data WHERE id > 10]" [data.csv]

$ csvsql [data.csv]

$ csvsql --db [postgresql:///mydb] --insert [data.csv]

$ csvsql --query "[SELECT * FROM file1 JOIN file2 ON file1.id = file2.id]" [file1.csv] [file2.csv]

$ csvsql --db [sqlite:///data.db] --insert --create-if-not-exists [data.csv]

$ csvsql --query "[SELECT category, COUNT(*) FROM data GROUP BY category]" [data.csv]

csvsql [options] file...

csvsql is part of csvkit that enables SQL queries on CSV files or imports CSV data into databases. It creates an in-memory SQLite database for queries or connects to external databases for import operations.
Without --query, it outputs CREATE TABLE statements suitable for the data, useful for generating schema. With --query, it executes SQL against the CSV data, supporting joins, aggregations, and all SQL operations.
For database import, csvsql supports various databases through SQLAlchemy connection strings, including PostgreSQL, MySQL, SQLite, and others. It handles type inference to create appropriate column definitions.

--query SQL

Execute SQL query on the CSV data.

Database connection string (SQLAlchemy format).

Insert data into database (requires --db).

Create table if it doesn't exist.

Comma-separated table names (default: filenames).

Don't generate CREATE TABLE statement.

Field delimiter (default: comma).

Input file encoding.

Disable type inference.

In-memory queries load all data into RAM. Complex queries on large files may be slow. Type inference can misidentify columns. Database imports require appropriate drivers installed.

csvsql is part of csvkit, created by Christopher Groskopf in 2011. It bridges the gap between CSV files and databases, enabling SQL-powered data analysis without manual database setup.

csvkit(1), sqlite3(1), sql2csv(1)