CSV Tools Worth Knowing and When to Reach for Each One

There are more CSV tools than anyone needs, and the sensible reaction to a long list is to learn one and stop. This guide is for the second step: when the one you know is awkward for a job, which of the others is worth the time to learn? I ran the same questions through the tools I would reach for, on the same 2 million row file, and wrote down what each is good at, how fast it was, and what it did to the data on the way. The tools are not equals. Some read a file and show it, some stream through it with a filter, some turn it into a database and some load it into memory as a table of columns. Knowing which kind you are holding explains most of the differences. The format itself is described here, and the shell basics that predate all of these are in working with CSV files in the shell.

Five questions that pick the tool

Before the tools, the questions. Each narrows the field.

What do I want to do? Looking at a file is a different job from changing it. A viewer needs to render wide tables in a terminal and let you scroll and sort. A filter needs to stream rows and write rows. A query tool needs a SQL engine. A transformation tool needs a language. Many tools do two of these, and none does all five well.

How big is the file? Under a hundred thousand rows, anything works and convenience wins. In the millions, per-row overhead decides: a tool that loads the file into Python objects slows noticeably, one that streams or uses a columnar engine does not. Past memory size, you need a tool that never holds the whole file.

Will I do it again? A one-off is a command. A repeated job is a script, and scripts need a tool with stable options, clear errors and an exit status.

Can I install it? A single binary (qsv, Miller, DuckDB) goes anywhere. A Python package needs an environment. On a locked-down server, what is installed decides, and awk and sort are always there.

How honest is it about quoting and types? This is the question people forget. A tool that splits on commas without understanding quotes is fast and wrong on the first address field. A tool that guesses types will turn a zip code into a number. Both are fine when you know it and expensive when you do not.

Look at the file

Start by looking, because most CSV problems are visible once you see the file in columns. column -s, -t < file.csv | less -S does a rough job with no installation, and breaks on quoted commas (previewing a CSV in the terminal). qsv table and csvkit's csvlook render a proper aligned table with quoting understood:

$ qsv select order_id,amount,status orders-200k.csv | qsv slice -l 3 | qsv table
order_id  amount  status
1         103.1   shipped
2         31.59   failed
3         89.98   pending

For anything bigger than a screen, an interactive viewer is the right tool. VisiData opens a file as a sheet, sorts with a keystroke, builds a frequency table of the current column with Shift-F, shows summary statistics with Shift-I, and can save the result in another format. It reads CSV, JSON, Excel and SQLite, and the learning curve is a day (VisiData for exploring CSV interactively). I reach for it when I do not yet know what question to ask, which is the situation a CSV from a stranger puts you in.

Slice and stream on the command line

For filters, column selection, counts and quick statistics, a streaming tool is the right shape: it reads a row, acts, writes a row, and memory stays flat whatever the file size. Three are worth knowing.

csvkit is a Python suite of commands (csvcut, csvgrep, csvsort, csvstat, csvjson, csvsql and more) with clear names and good documentation, and it is the gentlest start (getting started with csvkit). It is also slow, because it is Python and it infers types by reading the whole file before it begins.

qsv is a Rust tool, a fork of the older xsv with many added commands: count, headers, select, search, frequency, stats, sort, join, validate, schema, sniff, partition, split, apply and more. It uses an index file to answer questions quickly on large inputs, and its statistics command is the quickest way to profile a file (qsv for fast CSV statistics). Here it counts and tabulates the 200,000-row sample:

$ qsv count orders-200k.csv
200000
$ qsv frequency -s status orders-200k.csv | qsv table
field   value     count  percentage  rank
status  shipped   40077  20.0385     1
status  paid      40032  20.016      2
status  refunded  39988  19.994      3
status  pending   39977  19.9885     4
status  failed    39926  19.963      5

The prebuilt variant I tested lacked several features of the full build, sqlp and luau among them, so check which build you have before relying on a command. Using xsv and qsv side by side goes through the overlap.

Miller (mlr) treats a record as a set of named fields and gives you verbs (cut, sort, stats1, nest, count-distinct) chained with then, plus an expression language for computing new fields. It reads and writes CSV, TSV, JSON and more, which makes it good at conversion (Miller for records and pipelines):

$ mlr --icsv --opprint stats1 -a count,sum,mean -f amount -g status then sort -f status orders-200k.csv
status   amount_count amount_sum         amount_mean
failed   39926        3196886.2000000123 80.07028502730081
paid     40032        3196797.6999999774 79.85605765387633

Note the sums: Miller adds floating-point numbers, and the trailing digits (3196886.2000000123) are the usual binary rounding. Round at the end, and use a decimal type in a database when the cents matter.

awk deserves a mention and a warning. It is everywhere and fast, and awk -F, splits on every comma, so a quoted field containing a comma shifts every column after it, silently. Use it on files you know are simple, and never on addresses, names or free text (awk and CSV files).

Ask SQL questions

If the question is a SQL question (group, join, window), use a SQL engine and let the file be a table. DuckDB reads CSV directly with no import, infers types from a sample and runs analytic SQL on a columnar engine, which is why it led the timings:

SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM read_csv('orders-2m.csv') GROUP BY status ORDER BY status;

On the 2 million row file this took 0.20 seconds. Querying a CSV file with DuckDB instead of loading it shows the habit, and running SQL directly on a CSV the one-liners. SQLite's shell can import a CSV into a table and query it, with every column text unless you create the table first (importing CSV files with the SQLite command line). sqlite-utils wraps that in one command, and turns a CSV into a database you can keep, index and search:

$ sqlite-utils insert orders.db orders orders-2m.csv --csv --pk order_id
elapsed 93.35 s, db size 293.7 MB

That was slow, 93 seconds for 2 million rows, because it is Python inserting row by row; the query on the result then ran in 0.98 seconds and full-text search was built in 1.73 seconds. The trade is clear: a long import for a database that answers many questions quickly afterward (using sqlite-utils to load CSV fast, Datasette for publishing CSV as a searchable site). csvkit's csvsql --query does the same with an in-memory SQLite and took 58.41 seconds to group 200,000 rows, so it is for small files.

Transform in code

When the work is more than a query, use a dataframe library. pandas is the default, with the largest ecosystem, and loads the whole file into memory: 0.99 seconds to group the 2 million rows with only two columns requested. Polars is a newer Rust-based library with a lazy mode that plans the work and can run it in a streaming fashion without loading everything: 0.47 seconds in my run, with a peak memory of about 266 MB while the file is 124 MB (Polars for large CSV processing).

import polars as pl
(pl.scan_csv("orders-2m.csv", schema_overrides={"zip": pl.String})
   .filter(pl.col("status") == "shipped")
   .group_by("country")
   .agg(pl.len().alias("orders"), pl.col("amount").sum().round(2).alias("revenue"))
   .sort("revenue", descending=True).head(3)
   .collect(engine="streaming"))

The lazy query took 0.18 seconds, and writing the same file out with sink_parquet took 0.29 seconds and gave 34.1 MB from 123.9 MB of CSV, which is the reason to convert a file you will query repeatedly (why Parquet beats CSV for repeated analysis).

The timings in one table, and how far to trust them

Here is the group-by from the sections above, collected in one place. The task was the same in each: count rows and sum amount for each of five status values in the 2 million row file, printing five lines. Each time is the median of three runs.

The caveats matter more than the order. The file was in the operating system's cache, so these numbers leave out disk speed; a cold read of 124 MB on a slow disk adds more than any tool's difference. The rows are narrow, eight short columns, and a file of fifty wide text columns would shift the ranking toward tools that parse less per field. The container ran on a laptop's virtual machine, so absolute values mean little and only the ratios carry over. I asked pandas for only the two columns it needed, as a careful user would, so a naive read of every column is a different measurement. And the awk number is for an answer that would be wrong on a file with quoted commas, so its speed buys nothing when correctness is in doubt.

What the table does show is the shape: engines built for analysis (DuckDB, Polars) finish in a fraction of a second on a file this size, the general-purpose streamers (Miller, awk) take about a second, and the Python tool that infers types over the entire file is two orders of magnitude slower again. If a job takes a second, the choice does not matter. If it runs hourly on a 20 GB file, it does. The benchmark that counts is the one you run on your own data, with your own columns, and benchmarking CSV loading with pandas and DuckDB shows how to do it without fooling yourself.

From a stranger's file to an answer

Tools are easier to remember as a sequence than as a list. Here is the order I would use on a file I have never seen, with the tool for each step, and what I am looking for.

First, how big and how wide. wc -l gives lines, and qsv count gives records, which differ if quoted fields contain newlines. qsv headers lists the columns with their positions. If the counts disagree with what the sender promised, stop here and ask. Second, what dialect. qsv sniff or DuckDB's sniff_csv report the delimiter, the quote character, the header and the encoding they inferred, and telling what delimiter an unknown CSV file uses shows the manual version. Third, whether it is well formed: qsv validate and csvclean -a find ragged rows and unterminated quotes before they cost you an hour of confusing errors.

Fourth, what is in it. qsv stats gives the type guess, min, max and mean of every column, and qsv frequency shows the value distribution of the categorical ones; a column that should have five values and shows forty is a data quality finding you want now. Fifth, the questions. This is where DuckDB takes over: write SQL against the file, join it to another file or a lookup, and aggregate. Sixth, the keeper. If the answer will be asked again, write the cleaned data to Parquet or a database table and stop parsing CSV, since every later run then starts from typed, validated columns.

Six steps, four tools, and none of them requires loading the file into a spreadsheet. The few minutes spent on steps one to three, before any analysis, are what makes the numbers from step five believable.

Install, versions and trust

Most of these are one command to install and all of them change. csvkit, sqlite-utils, Datasette, VisiData and Frictionless are Python packages (pip install, ideally in a virtual environment or with pipx). DuckDB, qsv and Miller ship as single binaries for every platform and are also in common package managers. pandas and Polars are libraries you add to a project.

Pin the versions in anything you script. The defaults of these tools change between releases, and the changes are exactly the kind that matter here: sqlite-utils 4 turned type detection on by default where earlier versions left every CSV column as text, so a script written against the older behavior would start producing integer zip codes after an upgrade. DuckDB's sniffer improves and changes what it picks. A line in a lockfile or a Dockerfile, and a test with a file that has a leading zero, a quoted newline and an empty field, is a cheap guard.

Read the build notes as well. The qsv binary I tested is the standard prebuilt variant, and it lacks sqlp, luau and other commands that the full build includes; the error message when I called sqlp listed what was available, which is how I found out. A tutorial that uses a command your build does not have is not wrong, and it is a reason to check qsv --version and qsv --help first.

Scripting these tools without surprises

A command that works in a terminal can misbehave in a script. Four habits prevent most of the trouble.

Quote everything. A column name with a space or a pattern with a dollar sign needs single quotes in a shell script, and Miller's expressions are full of $, so mlr filter '$status == "failed"' needs the outer single quotes. Use set -euo pipefail so a failing tool at the start of a pipe fails the pipeline; without pipefail, qsv search ... | qsv count reports the count of whatever got through, even when the first command failed. Fix the locale: LC_ALL=C for sort and awk makes ordering and character handling reproducible between machines, and it also makes them treat multibyte text as bytes, so choose deliberately. And write the delimiter and encoding into every call, as you would in a reader library, instead of leaving it to the tool's guess.

Look at exit codes. qsv validate returned 1 on the bad file, csvlint returned 1 and csvclean -a returned a nonzero status as well, which is what a pipeline needs. A tool that prints a warning and exits 0 on a damaged file is one to wrap in an explicit check.

What each tool did to the data

Speed is the easy comparison. The one that costs money is what the tool does to your values. The same two questions, asked of each, give different answers. First, the type each tool chose for the zip column of the 200,000-row sample:

DuckDB and qsv saw the whole column, noticed values with leading zeros and kept text. pandas and Polars inferred from the first 1,000 rows, which contained only values without leading zeros, and chose integers. sqlite-utils 4 detects types by default, so the zips became integers in the database. Then a two-row file with 02134 and 90210:

Four of the seven lost the zero, and one made it a float. None of them printed a warning. This is the argument for reading as text first and converting on purpose, whichever tool you use (why pandas turns zip codes into numbers). In practice: DuckDB with all_varchar=true, pandas with dtype=str, Polars with infer_schema=False, sqlite-utils with --no-detect-types, and qsv, which reads everything as text by default.

Tools compose, which is the point

The best use of the kit is not one tool at a time. Command-line tools that read and write CSV on standard input and output can be chained, and each stage can be the tool that does that stage best. Here a streaming filter picks failed orders from the 200,000-row sample, a column selector keeps three fields, and DuckDB aggregates the rest by reading standard input as a file:

qsv search -s status '^failed$' orders-200k.csv \
  | qsv select order_id,country,amount \
  | duckdb -csv -c "SELECT country, count(*) AS n, round(sum(amount), 2) AS total
                    FROM read_csv('/dev/stdin') GROUP BY country ORDER BY total DESC LIMIT 3"

country,n,total
GB,5700,463220.36
BR,5804,461160.53
JP,5697,459961.49

The first two stages never hold the file in memory, and DuckDB reads only what the earlier stages let through. Swapping the first stage for Miller (mlr --icsv --ocsv filter '$status == "failed"' then cut -f order_id,country,amount) gave the same 39,926 rows and the same total of 3,196,886.20, which is the kind of cross-check worth doing once on any pipeline you intend to trust: two independent tools agreeing on a count and a sum is stronger evidence than either one alone.

Two cautions apply to pipes. Standard input cannot be read twice, so a tool that needs two passes (type inference over the whole file, then the real read) may buffer it or fail, and DuckDB's CSV sniffer reads a sample from the stream, so give it explicit column types if the first rows are unrepresentative. And a pipeline's exit status is that of its last command unless pipefail is set, as noted above, so a failed first stage can leave a quiet, plausible, partial answer.

Reading the errors these tools give

Each tool reports a damaged file differently, and learning the shape of the messages saves time. qsv validate names the record, the line and the byte offset: record 3 (line: 4, byte: 66): found record with 2 fields, but the previous record has 3 fields. csvkit's csvclean -a writes a CSV of errors with a line_number and a message such as Expected 3 columns, found 2 columns. Frictionless prints a table with the row, the field and a type such as missing-cell. DuckDB's reader raises a message that names the line and suggests options (ignore_errors, store_rejects), and pandas raises a ParserError that cites the line and the expected field count.

The common thread is that the line number is the useful part. It is the line of the physical file, which is also what an editor shows, except when quoted newlines make lines and records diverge, in which case the tool's "record" number and the editor's line number differ by the number of embedded line breaks before that point. When a tool's line looks wrong, count the quoted newlines. And when the error is about the first bad row, remember there may be hundreds more after it: re-run with the tool that lists them all (logging rejected rows from a CSV import for review).

What I would skip

A few categories are not worth the time for most readers. GUI tools that import a file into a proprietary window are fine for a person and a poor fit for anything you want to repeat. Online converters that take an upload are a data leak for anything that is not public. A one-off Python script that reimplements the CSV parser with line.split(",") is the most common source of damaged data in the field; use the standard library's csv module or one of the tools above, which handle quoting. And installing five overlapping command-line suites to find the one you like usually costs more time than learning one properly.

If you are choosing between two tools with the same capabilities, take the one your team already runs. The cost of a second tool is the new set of defaults, and the cost of the first is nothing.

Check and repair

Last in the kit are the tools that tell you whether a file is sound. qsv validate checks that every record has the same number of fields and reports the first violation with a line and byte offset. csvkit's csvclean -a reports every length mismatch and writes the good rows separately. Frictionless validates against a schema, with types and constraints, and CSVLint, a Ruby gem, checks structure and a CSVW schema (CSVLint and other validators). On a file with a short row and a long row, qsv validate stopped at the first, csvclean -a listed both with line numbers, and csvlint reported ragged_rows for the short row. The tools differ in whether they report all errors or the first, which matters when a file has hundreds.

For the checks that belong in a pipeline, writing data quality checks for a CSV shows what to assert, and validating a CSV file the structure first.

A small kit that covers most days

If I had to pick a kit for someone starting, it would be three tools and nothing else. One viewer, VisiData, for the first look. One SQL engine, DuckDB, for anything that is a question about the data, from a count to a join of two files (joining two CSV files with SQL). One command-line streamer, qsv or Miller, for fast filters and conversions in a shell script, where DuckDB's startup and syntax are heavier than a pipe. Add pandas or Polars when the work is code, and the checkers when the work is a pipeline.

The reason to stay small is that every tool has its own quoting rules, type guessing and corners, and each one you add is one more set to remember at the moment something goes wrong. Learn the tool's defaults on the awkward file, the one with quoted newlines and leading zeros, before you trust it on the real one.

When none of these tools is the right one

These tools are for files you can name and look at. When the data is large enough that a single machine is the bottleneck, they stop being the answer, and the file belongs in a warehouse or a lake with a proper engine (loading CSV into cloud warehouses). When the file is part of a daily pipeline, the tool is a component and the job needs a ledger, validation and an owner, which is the subject of automating CSV imports into a database.

When the file is the wrong format for the job, converting it once is better than tooling around it forever: Parquet for repeated analysis, a database table for lookups, JSON Lines for streams. And when the data is sensitive, a command-line tool on a laptop is still a copy of it, with the retention and access questions that implies (CSV security risks that catch teams out). The projects themselves document their options best: DuckDB's CSV overview, the qsv repository, the Miller documentation and the csvkit documentation.