qsv: Ultra-fast, data-wrangling CLI toolkit for CSVs

qsv is a command line program for indexing, slicing, analyzing, splitting, enriching, validating and joining CSV/TSV files. Commands are simple, fast and composable:

1. Simple tasks are easy.
2. Performance trade offs are exposed in the CLI interface.
3. Composition does not come at the expense of performance.

---

• Available Commands
• Installation
• Whirlwind Tour
• Cookbook
• Benchmarks
• License
• Sponsor

NOTE: qsv is a fork of the popular xsv utility, merging several pending PRs since xsv 0.13.0's release, along with additional features & commands for data-wrangling (NEW/EXTENDED commands are marked accordingly).

Available commands

Command	Description
apply	Apply series of string, date, currency and geocoding transformations to a CSV column. (NEW)
behead	Drop headers from CSV file. (NEW)
cat	Concatenate CSV files by row or by column.
count1	Count the rows in a CSV file. (Instantaneous with an index.)
dedup2	Remove redundant rows. (NEW)
enum	Add a new column enumerating rows by adding a column of incremental or uuid identifiers. Can also be used to copy a column or fill a new column with a constant value. (NEW)
exclude1	Removes a set of CSV data from another set based on the specified columns. (NEW)
explode	Explode rows into multiple ones by splitting a column value based on the given separator. (NEW)
fill	Fill empty values. (NEW)
fixlengths	Force a CSV file to have same-length records by either padding or truncating them.
flatten	A flattened view of CSV records. Useful for viewing one record at a time. e.g., `qsv slice -i 5 data.csv
fmt	Reformat CSV data with different delimiters, record terminators or quoting rules. (Supports ASCII delimited data.) (EXTENDED)
foreach	Loop over a CSV file to execute bash commands. (*nix only) (NEW)
frequency13	Build frequency tables of each column in CSV data. (Uses parallelism to go faster if an index is present.)
headers	Show the headers of CSV data. Or show the intersection of all headers between many CSV files.
index	Create an index for a CSV file. This is very quick and provides constant time indexing into the CSV file.
input	Read CSV data with exotic quoting/escaping rules.
join1	Inner, outer and cross joins. Uses a simple hash index to make it fast. (EXTENDED)
jsonl	Convert newline-delimited JSON to CSV. (NEW)
lua	Execute a Lua script over CSV lines to transform, aggregate or filter them. (NEW)
partition	Partition CSV data based on a column value.
pseudo	Pseudonymise the value of the given column by replacing them with an incremental identifier. (NEW)
rename	Rename the columns of CSV data efficiently. (NEW)
replace	Replace CSV data using a regex. (NEW)
reverse2	Reverse order of rows in CSV data. (NEW)
sample1	Randomly draw rows from CSV data using reservoir sampling (i.e., use memory proportional to the size of the sample). (EXTENDED)
search	Run a regex over CSV data. Applies the regex to each field individually and shows only matching rows. (EXTENDED)
searchset	Run multiple regexes over CSV data in a single pass. Applies the regexes to each field individually and shows only matching rows. (NEW)
select1	Select or re-order columns from CSV data. (EXTENDED)
slice12	Slice rows from any part of a CSV file. When an index is present, this only has to parse the rows in the slice (instead of all rows leading up to the start of the slice).
sort	Sort CSV data. (EXTENDED)
split13	Split one CSV file into many CSV files of N chunks.
stats123	Show basic types and statistics of each column in the CSV file. (i.e., mean, standard deviation, variance, median, min/max, nullcount, mode, quartiles, etc.) (EXTENDED)
table2	Show aligned output of any CSV data using elastic tabstops. (EXTENDED)
transpose2	Transpose rows/columns of CSV data. (NEW)

Installation

Binaries for Windows, Linux and macOS are available from Github.

Alternatively, you can compile from source by installing Cargo (Rust's package manager) and installing qsv using Cargo:

cargo install qsv

Compiling from this repository also works similarly:

git clone git://github.com/jqnatividad/qsv
cd qsv
cargo build --release

The binary will end up in ./target/release/qsv.

If you want to squeeze more performance from your build, set this environment variable before compiling:

export CARGO_BUILD_RUSTFLAGS='-C target-cpu=native'

Do note though that the resulting binary will only run on machines with the same architecture as the machine you compiled from. To find out your CPU architecture and other valid values for target-cpu:

rustc --print target-cpus

Benchmarks

Some very rough benchmarks of various qsv commands.

License

Dual-licensed under MIT or the UNLICENSE.

Sponsor

qsv was made possible by datHere - Data Infrastructure Engineering.
Standards-based, best-of-breed, open source solutions to make your Data Useful, Usable & Used.

Footnotes

1. uses an index when available ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹

2. loads the entire CSV into memory. Note that stats and transpose have modes that do not load the entire CSV into memory. ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶

3. runs parallel jobs by default (use --jobs option to adjust) ↩ ↩² ↩³