csvkit.readthedocs.io

csvkit is a suite of command-line tools for converting to and working with CSV. It is a good default for quick, scriptable CSV tasks, but the documentation itself names several situations where you should reach for something else: complex data analysis, larger files or higher performance, and deduplication or fuzzy matching. If your task falls into one of those categories, switch tools rather than pushing csvkit past its intended scope.

The three documented limits of csvkit

The csvkit 2.2.0 documentation lists three explicit "Note" boxes pointing to alternatives. These are the clearest signals for when to move on.

1. You need more complex data analysis than csvkit can handle

The docs state: "If you need to do more complex data analysis than csvkit can handle, use agate."

csvkit's own tools are oriented around conversion, selection, filtering, sorting, and summary statistics (csvstat). When your work shifts from reshaping and inspecting data to analyzing it — grouping, aggregating, joining with logic, computing derived metrics — csvkit's command surface becomes awkward.

Use agate when:

• You need programmatic analysis in Python rather than shell pipelines.
• Your logic involves multi-step transformations that would be painful to chain across csvcut, csvgrep, csvsort, and csvsql.
• You want a data-analysis library rather than a set of CLI tools.

Stay with csvkit when: you just need to convert Excel/JSON to CSV, print column names, select or reorder columns, grep rows by pattern, or produce a quick csvstat summary.

2. You need csvkit to be faster or to handle larger files

The docs state: "If you need csvkit to be faster or to handle larger files, you may be reaching the limits of csvkit. Consider loading the data into SQL, or using qsv or xsv."

This is a performance and scale boundary, not a correctness one. csvkit works, but at some file size or throughput requirement it stops being the right tool.

Options the docs suggest:

Stay with csvkit when: your files fit comfortably in memory and your pipelines run in acceptable time. For many day-to-day CSV chores, csvkit is fine.

3. You need to deduplicate or find fuzzy matches

The docs state: "Need to deduplicate or find fuzzy matches in your CSV data? Use csvdedupe and csvlink."

csvkit has no built-in deduplication or fuzzy matching. csvgrep matches by pattern or exact value; it does not find near-duplicates. If your task is "find rows that are probably the same record" or "link records across files," csvkit is the wrong layer.

Use:

• csvdedupe — for deduplicating CSV data.
• csvlink — for fuzzy matching / linking.

Stay with csvkit when: your matching is exact or regex-based, which csvgrep handles.

A quick decision guide

Two csvkit behaviors worth knowing before you decide

These are not reasons to leave csvkit, but they affect whether csvkit is the right fit for a given file.

• Format sniffing and type inference are on by default. csvkit sniffs the CSV format (comma, tab, space delimiters, etc.) based on the first 1024 bytes and performs type inference (converting text to numbers, dates, booleans). The docs note these "work well in most cases, but occasional errors occur." If you don't need them, set --sniflimit 0 (-y 0) and --no-inference (-I).
• Output formatting changes require csvformat. To change the field separator, line terminator, etc. of the output, you must use csvformat.

If either of these behaviors is causing trouble on your data, that is a signal to either adjust the flags or reconsider the tool — not necessarily to abandon csvkit.

Bottom line

Use csvkit for conversion, column selection, filtering, sorting, statistics, and SQL-style queries on CSVs that fit your performance envelope. Switch to agate for complex analysis, to SQL / qsv / xsv for speed or larger files, and to csvdedupe / csvlink for deduplication and fuzzy matching. Those are the boundaries the csvkit documentation itself draws.

csvkit detects the CSV dialect (delimiter, quoting, etc.) by sniffing the first 1024 bytes of input, and it infers column types by converting text that looks like numbers, dates, or booleans into those types. Both behaviors are on by default and work well in most cases, but they can produce occasional errors. If you don't want them, disable sniffing with --snifflimit 0 (or -y 0) and type inference with --no-inference (or -I).

Format detection: how the sniffing works

When csvkit reads a file, it doesn't assume commas. It samples the first 1024 bytes and deduces the dialect — for example, whether fields are separated by commas, tabs, or spaces. This means a tab-separated or space-separated file can often be read without you specifying anything.

The trade-off is that the sample is limited. If the first 1024 bytes don't represent the rest of the file well — say, a header block with unusual spacing before the real table begins — the deduced dialect can be wrong, and columns may split or merge incorrectly.

Controlling the sniff limit

Set --snifflimit 0 (-y 0) to turn sniffing off entirely. When you do this, csvkit falls back to its default dialect assumptions rather than guessing from the data. Use it when:

• You already know the file's format and want deterministic parsing.
• A file's opening bytes are misleading and cause wrong delimiter detection.

Type inference: what gets converted

By default csvkit performs type inference: it converts text to numbers, dates, booleans, and similar types. This is what lets tools like csvstat compute summary statistics without you writing any code — a column of digit strings is treated as numeric rather than as text.

Inference is convenient but not infallible. Values that resemble a type without being one (for example, an ID like 007 or a date-shaped string that isn't a date) can be converted in ways you didn't intend.

Disabling inference

Pass --no-inference (-I) to keep everything as text. This is the safer choice when:

• You're passing data through to another tool and want byte-for-byte fidelity.
• Columns contain codes, IDs, or leading zeros that must not be reinterpreted.
• You've seen unexpected conversions in your output.

Choosing between the defaults and the flags

The two flags are independent: you can disable sniffing while keeping inference, or the reverse. The documentation's own guidance is that these features "are useful and work well in most cases, but occasional errors occur" — so reach for the flags when you hit one of those errors, not as a default.

A related note on output formatting

Sniffing and inference affect how csvkit reads input. To change how output is formatted — the field separator, line terminator, and so on — you use csvformat, not the input flags above. Keep the two concerns separate: input parsing is controlled by --snifflimit and --no-inference; output formatting is controlled by csvformat.

When csvkit itself is the wrong tool

If you need more complex analysis than csvkit handles, the documentation points to agate. If you need more speed or larger files, you may be hitting csvkit's limits — consider loading the data into SQL, or using qsv or xsv instead.

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:

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.

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.