What Is csvkit and What Is It Used For?

Updated

csvkit is a suite of command-line tools for converting to and working with CSV, described in its documentation as "the king of tabular file formats." You use it when you want to inspect, transform, filter, summarize, or move tabular data from a terminal without writing a script. It is a good fit for quick, reproducible data chores on files that fit comfortably on one machine; the documentation itself notes that for more complex analysis you should reach for agate, and for faster processing or larger files you may be hitting csvkit's limits and should consider loading data into SQL or using qsv or xsv.

What csvkit actually is

csvkit 2.2.0 is a collection of separate commands rather than one monolithic program. Each command does one job and writes its result to standard output, so you can chain them with pipes. The documentation says it was inspired by pdftk, GDAL, and the original csvcut tool by Joe Germuska and Aaron Bycoffe.

Two default behaviors matter for understanding its output:

  • Format sniffing. csvkit deduces whether fields are delimited by commas, tabs, or spaces by examining the first 1024 bytes.
  • Type inference. It converts text to numbers, dates, and booleans.

The docs call these features useful and generally reliable but note that "occasional errors occur." If you don't want them, disable them with --snifflimit 0 (-y 0) and --no-inference (-I).

What you can do with it

The documentation's own examples map directly to common tasks:

Task Command
Convert Excel to CSV in2csv data.xls > data.csv
Convert JSON to CSV in2csv data.json > data.csv
Print column names csvcut -n data.csv
Select a subset of columns csvcut -c column_a,column_c data.csv > new.csv
Reorder columns csvcut -c column_c,column_a data.csv > new.csv
Find rows with matching cells csvgrep -c phone_number -r "555-555-\d{4}" data.csv > new.csv
Convert to JSON csvjson data.csv > data.json
Generate summary statistics csvstat data.csv
Query with SQL csvsql --query "select name from data where age > 30" data.csv > new.csv
Import into PostgreSQL csvsql --db postgresql:///database --insert data.csv
Extract from PostgreSQL sql2csv --db postgresql:///database --query "select * from data" > new.csv

Beyond these, the table of contents covers csvlook for viewing data, csvsort for ordering, and a tutorial that walks through getting started, installing, and obtaining sample data.

How the pieces fit together

The design is composable. in2csv handles the "get it into CSV" step; csvcut, csvgrep, and csvsort handle selection, filtering, and ordering; csvstat summarizes; csvsql and sql2csv bridge to databases; csvjson converts out. Because each writes to stdout, the tutorial's "Putting it together with pipes" section reflects the intended workflow: small commands combined into a pipeline.

One important caveat from the docs: to change the field separator, line terminator, or similar properties of the output, you must use csvformat. Don't expect other commands to reformat output on their own.

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

Choose csvkit when:

  • Your data is in CSV, Excel, or JSON and you need it in another of those formats.
  • You want column selection, row filtering, sorting, statistics, or a one-off SQL query without writing code.
  • You want commands you can save in a shell script or Makefile for repeatable processing.

Look elsewhere when:

  • You need analysis more complex than csvkit handles — the docs point to agate.
  • You need speed or larger files — the docs suggest SQL, qsv, or xsv.
  • You need deduplication or fuzzy matching — the docs point to csvdedupe and csvlink.

Getting started

The documentation's first-time path is the Tutorial, beginning with "Getting started," which covers installing csvkit and obtaining the data used in the examples. The project's important links are the documentation at csvkit.rtfd.org, the repository at github.com/wireservice/csvkit, the issue tracker, and schemas at github.com/wireservice/ffs.