Using qsv for Fast CSV Statistics and Slicing of Big Files

qsv is a command line toolkit for CSV written in Rust, descended from xsv, and it is the fastest of the CSV-aware tools I have used. What I use it for most is the thing every new file needs first: how many records, what are the columns, what do the values look like. It answers those in a fraction of a second on a file of 2,000,000 rows, and it parses quoting correctly while doing it. This article goes through the commands I use daily, with their output and timings, and covers two behaviors that surprised me, the cache files it leaves behind and the cost of sorting. It is one of the tools in the tour of working with CSV in the shell.

Count records, not lines

The first number you want is how many records a file has. wc -l counts lines, and a CSV with a field that contains a line break has more lines than records. qsv count reads the file as CSV:

cd /batches/csv-0001-0200/work
qsv count orders-2m.csv; wc -l < orders-2m.csv

2000000
2000001

qsv count people.csv; wc -l < people.csv

4
6

The orders file has 2,000,000 records and wc -l says 2,000,001, the extra being the header. The four-record people file, which has one field with a line break and one with an empty note, is 4 records and 6 lines. A pipeline that checks "rows in equals rows out" with wc -l will be wrong on the second file and right on the first; qsv count is right on both, and finishes in 0.20 s on the large one. The same distinction, and why embedded newlines break line-oriented tools, is in the escaping article.

Column names and the first profile

qsv headers lists the names with positions, the equivalent of csvcut -n:

qsv headers orders-small.csv

1   id
2   customer
3   city
4   qty
5   price
6   status

qsv stats is the profile. Piped through select to choose the columns and table to align them:

qsv stats orders-small.csv | qsv select field,type,min,max,mean,nullcount | qsv table

field     type     min        max          mean     nullcount
id        Integer  1          8            4.5      0
customer  String   Ana Silva  Smith, Jane           0
city      String   Bern       Rome                  0
qty       Integer  1          12           5.7143   1
price     Float    1.05       100.0        19.9563  0
status    String   failed     refunded              0

For the small file, id is Integer from 1 to 8, qty is Integer with one null, price is Float from 1.05 to 100.0 with a mean of 19.9563, and the three text columns are String with their alphabetical minimum and maximum. On the big file, with --cardinality to count distinct values:

cd /batches/csv-0001-0200/work
qsv stats --cardinality --select amount,zip,status,country orders-2m.csv | qsv select field,type,min,max,mean,cardinality,nullcount | qsv table

field    type    min     max      mean     cardinality  nullcount
amount   Float   0.04    654.24   80.0873  35898        0
zip      String  00500   99998             99499        0
status   String  failed  shipped           5            0
country  String  BR      US                7            0

amount is a Float from 0.04 to 654.24 with a mean of 80.0873 and 35,898 distinct values. zip is a String, from 00500 to 99998, with 99,499 distinct values; it keeps the leading zeros because it is typed as text. status has 5 distinct values and country has 7, and neither has any nulls. That is a lot to know about a 130 MB file for a command that took about a quarter of a second:

cd /batches/csv-0001-0200/work
for c in "qsv count orders-2m.csv" "qsv stats --select amount orders-2m.csv" "qsv frequency -s country orders-2m.csv" "qsv search -s status refunded orders-2m.csv" "qsv sort -s amount -N orders-2m.csv" "qsv select amount orders-2m.csv"; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c"
done

0.20s qsv count orders-2m.csv
0.23s qsv stats --select amount orders-2m.csv
0.38s qsv frequency -s country orders-2m.csv
0.25s qsv search -s status refunded orders-2m.csv
2.76s qsv sort -s amount -N orders-2m.csv
0.39s qsv select amount orders-2m.csv

qsv stats --select amount took 0.23 s. The same table in the other tools: csvstat needed about a minute for one column of 200,000 rows, as the csvkit article measured.

The cache file

One behavior to know about. qsv stats stores its result next to the input so that later commands can reuse it:

cd /batches/csv-0001-0200/work
time qsv stats --everything --select amount orders-2m.csv > /dev/null
ls | grep -i stats || echo "(no stats cache file left in the folder)"

real	0m0.289s
user	0m0.433s
sys	0m0.063s
orders-200k.stats.csv
orders-2m.stats.csv

After running stats on the large file, the folder contained orders-2m.stats.csv and, from an earlier run on a smaller extract, orders-200k.stats.csv. These are the statistics files qsv writes beside the input it was given. Read-only folders, shared drives and deployment directories are places where an extra file appearing is a surprise, and a stale one can feed later commands that use the cache. Delete them when you are done, or check the options in the qsv documentation for turning the cache off. It is not a bug, and it is the kind of side effect that an analysis tool should state up front.

Frequency tables

qsv frequency counts the values of one or more columns, most common first, with a rank and a percentage:

cd /batches/csv-0001-0200/work
qsv frequency -s status,country --limit 3 orders-2m.csv

field,value,count,percentage,rank
status,pending,400329,20.01645,1
status,refunded,400135,20.00675,2
status,paid,400113,20.00565,3
status,Other (2),799423,39.97115,0
country,FR,286777,14.33885,1
country,IN,286243,14.31215,2
country,BR,285957,14.29785,3
country,Other (4),1141023,57.05115,0

For status and country with --limit 3 it printed the top three of each plus an Other row that sums the rest: pending 400,329 (20.02%), refunded 400,135, paid 400,113 and Other (2) 799,423. For country, FR 286,777, IN 286,243 and BR 285,957, with Other (4) holding the remaining four. The Other row is a convenience that means the percentages always add up to 100; set --limit 0 for the full table. On the 2,000,000 row file one column took 0.38 s.

Slicing and searching

qsv slice takes rows by position. --start is zero-based and --len is a count:

cd /batches/csv-0001-0200/work
qsv slice --start 1000000 --len 2 orders-2m.csv | qsv select order_id,amount,status
qsv slice --index 5 orders-2m.csv | qsv select order_id,amount

order_id,amount,status
1000001,92.75,failed
1000002,103.31,failed
order_id,amount
6,50.64

--start 1000000 --len 2 returned orders 1000001 and 1000002, so the start offset is a zero-based record index, not a line number. --index 5 returned a single record, order 6, the sixth. To search, qsv search -s COLUMN REGEX filters rows whose column matches:

cd /batches/csv-0001-0200/work
qsv search -s status '^refunded$' orders-2m.csv | qsv count
qsv search -s amount '^[5-9][0-9][0-9]\.' orders-2m.csv | qsv count

400135
101

The first command counted refunded orders: 400,135, the same as DuckDB's count in the Python benchmark. The second counted amounts of 500 or more with a regular expression, ^[5-9][0-9][0-9]\., and found 101. A regular expression on a number is a blunt instrument. For a numeric comparison, the filter in Miller or a SQL WHERE is clearer, and for several conditions I would use SQL.

Sorting is the slow one

Everything above is streaming and touches each row once. Sorting needs the whole file:

cd /batches/csv-0001-0200/work
time qsv sort -s amount -N -R orders-2m.csv | qsv slice --len 3 | qsv select order_id,amount

order_id,amount
1012726,654.24
158192,651.04
1112548,649.03

real	0m3.415s
user	0m8.350s
sys	0m0.740s

qsv sort -s amount -N -R (numeric, reversed) put the three largest orders first, 654.24, 651.04 and 649.03, and the pipeline took 3.4 s, 2.76 s in the table above. That is still fast compared with csvkit and Miller; it is also ten times the cost of the other commands. If you need only the top few, a tool that keeps a running top N is cheaper than a sort, and DuckDB sorted the whole file and wrote it out in 0.76 s in the sorting article.

The index

For a file you will slice or sample repeatedly, an index makes random access instant:

cd /batches/csv-0001-0200/work
qsv index orders-2m.csv && ls -l orders-2m.csv.idx | awk '{print $5, $9}'
time qsv slice --start 1999990 --len 2 orders-2m.csv | qsv select order_id
rm -f orders-2m.csv.idx

16000016 orders-2m.csv.idx
order_id
1999991
1999992

real	0m0.010s
user	0m0.004s
sys	0m0.010s

qsv index wrote a 16,000,016 byte .idx file (2,000,000 records at 8 bytes each, plus 16 bytes), and the slice of two rows from the very end of the file took 0.010 s. Without the index, reaching the end of a CSV means reading all of it. The index is another file beside the input, and like the stats cache it should be deleted or ignored in version control; the script above removes it. Sampling is the other everyday use:

cd /batches/csv-0001-0200/work
qsv sample 5 --seed 42 orders-2m.csv | qsv count

5

qsv sample 5 --seed 42 produced five records; fixing the seed makes the sample repeatable, which matters for anything you will reproduce.

What qsv thinks of your file

qsv sniff guesses the file's properties without reading it all:

qsv sniff orders-small.csv

Path: /batches/csv-0001-0200/lab/data/sh/orders-small.csv
Sniff Timestamp: 2026-10-26T00:18:47.848030335+00:00
Last Modified: 2026-10-26T00:11:43+00:00
Delimiter: ,
Header Row: true
Preamble Rows: 0
Quote Char: "
Flexible: false
Is UTF8: true
Detected Mime Type: application/csv
Detected Label: csv
Inference Score: 1
Retrieved Size (bytes): 287
File Size (bytes): 287
Sampled Records: 8
Estimated: false
Num Records: 8
Avg Record Len (bytes): 31
Num Fields: 6
Stats Types: false
Fields:
    1:  Unsigned  id
    2:  Text      customer
    3:  Text      city
    4:  Unsigned  qty
    5:  Float     price
    6:  Text      status

It reported the delimiter (a comma), a header row, the quote character, that the file is UTF-8, the record count (8), the average record length (31 bytes), and a type for each field: id and qty Unsigned, price Float, the others Text. As with every sniffer, use it to look, not to decide.

How to tell it worked

Compare qsv count with an independent count: DuckDB's count(*) on the same file. Compare one statistic, such as the sum or the distinct count, with a SQL query. Check the folder for the cache and index files you did not mean to create. And when a stats result looks wrong for a column, confirm the type it chose: an identifier shown as Integer will have lost its zeros if you export it.

What qsv leaves behind and what it does not do

It leaves cache and index files beside your input unless told otherwise, and I have shown where. It is not a database: it has no joins that match the flexibility of SQL for complex queries (it does have a join command, which I did not test here), and its regular-expression search is the wrong tool for numeric ranges. And it is a separate binary, so on a locked-down machine you may not be allowed to install it, where awk, sort and cut are already present. For the rest, the speed is hard to beat.