Using xsv and qsv Side by Side on the Command Line

xsv was the tool that made the command line pleasant for CSV. It is a small Rust program with a handful of verbs, each fast and each doing one thing with proper quoting, and it set the shape that later tools copied: xsv count, xsv select, xsv search, xsv frequency, xsv join, chained with pipes. Its development slowed years ago, and qsv grew out of it as a fork that kept the verbs and kept adding. The practical question for someone choosing now is what the two share, what qsv adds that is worth having and where the trap is. I ran the qsv side of that on a 2 million row file; xsv was not installed in my test environment, so its half comes from its documentation. The wider survey of tools is in CSV tools worth knowing, and qsv for fast CSV statistics covers the stats command in depth.

What they share

The core verbs are the same in both tools, and I have always found the names easy to remember because each is an English word for what it does. Per the xsv README, its commands are cat, count, fixlengths, flatten, fmt, frequency, headers, index, input, join, sample, search, select, slice, sort, split, stats and table. qsv has all of these and a few dozen more. Every command reads a file or standard input, writes CSV to standard output and understands quoted fields, which is the part that makes them safe where cut and awk are not.

The global options match as well: -d for the delimiter, -n for a file with no header, -o for an output file. A pipeline written for one is likely to run with the other after you change the program name, with two caveats: qsv has changed the defaults of a few commands, and some flags exist in only one tool. When a script moves between them, run it on a small file and compare the output before trusting it.

Here is the everyday set run through qsv on the 200,000-row sample:

$ qsv count orders-200k.csv
200000
$ qsv headers orders-200k.csv
1   order_id
2   customer_id
3   zip
4   amount
5   created_at
6   status
7   country
8   note
$ qsv search -s status '^failed$' orders-200k.csv | qsv count
39926
$ qsv select order_id,amount orders-200k.csv | qsv sort -s amount -N -R | qsv slice -l 3 | qsv table
order_id  amount
158192    651.04
11896     607.8
195261    592.95

-s names the column to search or sort, -N sorts numerically and -R reverses. qsv table aligns the result for the terminal. A join against a small lookup works the same way, with the key column named for each side:

$ qsv slice -l 3 o2m.csv | qsv select order_id,country | qsv join country - country countries.csv | qsv select order_id,country,country_name | qsv table
order_id  country  country_name
1         GB       United Kingdom
2         BR       Brazil
3         JP       Japan

The - stands for standard input as the left side. That is the same syntax as xsv's join, and one of the places where the muscle memory carries over.

The index

The feature of the pair that earns its keep on large files is the index. qsv index writes a small companion file with the byte offset of every record, so commands that need to jump (count, slice, sample, parallel stats and frequency) can do it without reading the whole file. xsv has the same command and the same idea.

On the 2 million row file, the numbers were:

$ qsv count o2m.csv                         0.15 s   (no index)
$ qsv slice -s 1000000 -l 2 o2m.csv         0.30 s   (no index)
$ qsv index o2m.csv                         0.16 s   (builds o2m.csv.idx, 16,000,016 bytes)
$ qsv count o2m.csv                         0.00 s   (indexed)
$ qsv slice -s 1000000 -l 2 o2m.csv         0.00 s   (indexed)

A count of 2,000,000 records took 0.15 seconds to scan, and nothing at all with an index, which stores the record count. Slicing two rows from the middle took 0.30 seconds without it, because the tool had to read a million records to get there, and under 0.01 seconds with it. The index is 16 MB for a 124 MB file, 8 bytes per record. It goes stale when the file changes, and qsv checks the modification time and ignores a stale index, which is the safe behavior. Build it once for a file you will query many times, and not at all for a file you read once.

What qsv adds

Beyond the xsv verbs, the additions that matter for everyday work are these. qsv sniff reports the delimiter, quote character, header, encoding, record count and a type guess per column, from a sample:

$ qsv sniff o2m.csv
Delimiter: ,
Header Row: true
Quote Char: "
Is UTF8: true
Num Records: 2,000,000
Num Fields: 8
Fields:
    3:  Unsigned  zip
    4:  Float     amount
    5:  DateTime  created_at

qsv validate checks that every record has the same field count and, given a JSON Schema, that values satisfy it (CSVLint and other validators compares it with the alternatives). qsv schema infers a JSON Schema from a file. qsv partition writes one file per value of a column, qsv apply and qsv replace transform columns, qsv diff compares two files and qsv dedup and qsv extsort handle duplicates and sorting beyond memory. The full build adds sqlp (SQL through Polars), luau scripting and more.

The type trap

In the sniff output above, zip is Unsigned. In fact it is not a number at all: the column holds five-digit postal codes, and some begin with a zero. qsv sniff looked at the first 1,000 records, which had none, and guessed from them. qsv stats reads the whole column and tells the truth:

$ qsv stats --everything -s zip o2m.csv | qsv select field,type,min,max,min_length,max_length | qsv table
field  type    min    max    min_length  max_length
zip    String  00500  99998  5           5

The type is String, the minimum is 00500 and every value is five characters long, which is what a zip code looks like. A search for values starting with zero finds 05398 and 07969 within the first dozen. The lesson is not about qsv in particular. Any tool that infers a type from a sample can be wrong about the rest, and the same thing happened with pandas and Polars on this file. Use sniff to learn the dialect, and stats over the full file to learn the types, and when a column is an identifier, treat it as text whatever the guess says (why pandas turns zip codes into numbers).

Which build you have

qsv is distributed in several variants. The prebuilt "standard" binary I used omits features that need extra dependencies, and asking for one that is missing gives an error that lists the commands available: asking for sqlp returned Could not match 'sqlp' with any of the allowed variants, followed by the list. Before following a tutorial, run qsv --list or qsv --help and check that your build has the command. The full build, or a build from source with the features enabled, has the rest.

When you need SQL instead

For a question with a group-by and several aggregates, qsv's verbs get awkward. qsv frequency counts values of a column, which answers "how many per status", and cannot sum amount per status in the standard build. At that point a SQL tool is the better fit: DuckDB reads the same file in place and answers it in one statement (querying a CSV file with DuckDB instead of loading it). Many people use qsv for the first look, the index and the filters, then hand the cleaned result to DuckDB.

If you are choosing a streamer, my default is qsv for its active development and its validation, with the caveat that xsv is a fine and stable choice if it is already installed on your servers and your needs are only the core verbs. The qsv project, with its command reference, is at github.com/dathere/qsv, and the original xsv lives at github.com/BurntSushi/xsv.