Utilities
Run SQL on a CSV file with csvsql
SQL on one CSV. The table name is the file stem.
overview
What csvkit does
You have a CSV, and the next node needs a filtered or aggregated table. Connect the file to input and set --query. The wrapper runs csvsql and tees stdout to the output file. The table name is the filename without its extension. A script that writes output.txt must query FROM output.
-d sets the delimiter. -e sets encoding. --no-header-row is a boolean; leave it disconnected when the file has a header. Do not connect unused booleans. Hive will pass true or false as leftover argv and csvsql will fail.
csvkit is in-process SQL on one CSV. Use duckdb when you need Parquet, JSON, or a join across files. A query that names the wrong table fails the node. Check the input filename before you write FROM.
use cases
Where csvkit fits
Filter rows
Set --query to SELECT ... FROM stem WHERE .... The output file is the result CSV.
Aggregate
Use GROUP BY in --query. The table name is still the file stem.
Change the delimiter
Set -d when the file is not comma-separated.
Headerless CSV
Connect --no-header-row only when the file has no header. Leave it off otherwise.
reference
csvkit inputs and flags
| Name | Type | Flag | Description |
|---|---|---|---|
| input | FILE | · | Input CSV file to query. |
| query | STRING | --query | SQL query. Table name is the filename without its extension. |
| delimiter | STRING | -d | Field delimiter. Default comma. |
| encoding | STRING | -e | Character encoding. Default UTF-8. |
Showing key inputs. csvkit exposes 5 inputs in total.
Full flag reference (5 inputs)
| Name | Type | Flag | Description |
|---|---|---|---|
| delimiter | STRING | -d | Field delimiter character (default comma) |
| encoding | STRING | -e | Character encoding of input CSV (default UTF-8) |
| no-header | BOOLEAN | --no-header-row | Set if the CSV has no header row |
| query | STRING | --query | SQL query to execute. The table name is the filename without its extension. |
| input | FILE | · | Input CSV file to query |
example
Run csvkit
# csvkit csvsql; last argv is the Hive output filecsvsql --query "SELECT name, n FROM data WHERE n > 1" data.csvname,nb,2.0guidance
Choosing csvkit
Use csvkit when the input is one CSV and the query is SQL. Use duckdb when you need Parquet, JSON, or more than one file.
duckdb
In-process SQL on CSV, JSON, and Parquet. Use it when csvsql is too narrow.
wget
Downloads a CSV. It does not query it.
jq
Filters JSON, not CSV.
faq
csvkit questions
related
More Utilities tools
browser-fetch
Headless Chromium render: requested URL, final URL, status, and title next to the page.
cewl
Spider a URL and return a wordlist for password crackers.
dnsgen
Wordlist and mined-word subdomain permutation.
duckdb
In-process SQL on CSV, JSON, and Parquet. No server.
edge-tts
Microsoft Edge neural speech from text or a script file. No API key.
exiftool
Read embedded metadata from hundreds of image, media, and document formats.
Run csvkit yourself
A two-row CSV feeds csvsql WHERE n > 1. The result below is from a completed run.
Facts on this page come from the live Trickest tool library.