Common Use Cases and Examples of csvkit Commands

csvkit is a suite of command-line tools for converting to and working with CSV. If you need to convert Excel or JSON files to CSV, inspect or reshape columns, filter and sort rows, generate summary statistics, run SQL against CSV files, or move data between CSV and PostgreSQL, csvkit provides a dedicated command for each of those tasks. The examples below come from the csvkit 2.2.0 documentation and assume you have csvkit installed and are working in a terminal.

Converting files to CSV with in2csv

in2csv handles the "get my data into CSV" step. The documentation describes it as "the Excel killer" and shows two common conversions:

in2csv data.xls > data.csv
in2csv data.json > data.csv

The first converts an Excel file to CSV, the second converts JSON. The output goes to stdout, so you redirect it to a file. This is the typical starting point when your source data lives in a spreadsheet or a JSON export and you want to use the rest of the csvkit toolchain on it.

Inspecting, selecting, and reordering columns with csvcut

csvcut is described as "data scalpel" — it works on columns rather than rows.

Print the column names of a file:

csvcut -n data.csv

Select a subset of columns:

csvcut -c column_a,column_c data.csv > new.csv

Reorder columns by listing them in the order you want:

csvcut -c column_c,column_a data.csv > new.csv

The -c flag takes a comma-separated list of column names (or indices), and the output is written to a new file. This is useful when you need a narrower or differently ordered view of a wide table before passing it to another tool.

Finding and ordering rows with csvgrep and csvsort

csvgrep finds rows whose cells match a pattern. The documentation example uses a regular expression against a phone number column:

csvgrep -c phone_number -r "555-555-\d{4}" data.csv > new.csv

-c names the column to search, and -r supplies the regular expression. Matching rows are written to the output.

csvsort orders rows. The documentation lists it under "Examining the data" as the tool for when "order matters," and it is used the same way as the other commands — read a CSV, write a reordered CSV. Together, csvgrep and csvsort cover the two most common row-level operations: filtering and ordering.

Summarizing data with csvstat

csvstat generates summary statistics without writing code. The documentation's one-line example is:

csvstat data.csv

This is the command to reach for when you want a quick statistical profile of each column before deciding what to do with the data. It sits alongside csvgrep and csvsort in the "Examining the data" section of the tutorial, which suggests using it early in a workflow to understand what you have.

Querying CSV with SQL using csvsql

csvsql lets you run SQL directly against CSV files:

csvsql --query "select name from data where age > 30" data.csv > new.csv

The --query flag takes a SQL statement, and the CSV file is treated as the table to query. This is useful when you already know SQL and want to avoid loading the data into a database just to run a filter or aggregation.

Moving data between CSV and PostgreSQL

csvkit also covers database import and export:

csvsql --db postgresql:///database --insert data.csv
sql2csv --db postgresql:///database --query "select * from data" > new.csv

The first command inserts a CSV file into a PostgreSQL database; the second extracts query results from PostgreSQL into a CSV file. Both use a database connection string passed to --db. This pair is the bridge between flat files and a relational database when you need to persist data or pull a query result back out.

Converting CSV to JSON with csvjson

csvjson data.csv > data.json

csvjson converts a CSV file to JSON. This is the reverse direction of the in2csv JSON example and is useful when a downstream tool expects JSON rather than CSV.

Two behaviors to know before you rely on the output

The documentation flags two defaults that affect results:

• Format sniffing. csvkit deduces the delimiter (commas, tabs, spaces) from the first 1024 bytes. This works well most of the time but can occasionally guess wrong.
• Type inference. csvkit converts text to numbers, dates, and booleans automatically. This is convenient but can also produce occasional errors.

If you do not want either behavior, disable them with --sniflimit 0 (-y 0) and --no-inference (-I). Also note that to change the output's field separator or line terminator, you must use csvformat — the other commands do not expose those output settings directly.

When csvkit is the right tool — and when it isn't

csvkit is a good fit for command-line conversion, column and row manipulation, quick statistics, SQL queries over CSV, and PostgreSQL import/export. The documentation is explicit about its limits: if you need more complex data analysis than csvkit can handle, use agate; if you need csvkit to be faster or to handle larger files, consider loading the data into SQL or using qsv or xsv. For deduplication or fuzzy matching, the documentation points to csvdedupe and csvlink instead.

If your task is one of the conversions or transformations shown above, csvkit's single-purpose commands compose well through pipes and redirection. If your task is heavier analysis or larger-than-memory data, the documentation itself recommends stepping up to a different tool.