98 releases (60 breaking)
Uses new Rust 2021
|new 0.76.3||Dec 5, 2022|
|0.74.0||Nov 27, 2022|
|0.61.1||Jul 30, 2022|
|0.36.1||Mar 26, 2022|
|0.23.0||Nov 29, 2021|
#29 in Parser implementations
602 downloads per month
qsv: Ultra-fast CSV data-wrangling toolkit
|Table of Contents|
|qsv is a command line program for
indexing, slicing, analyzing, filtering,
enriching, validating & joining CSV files.
Commands are simple, fast & composable.
* Available Commands
* Whirlwind Tour
* Performance Tuning
* NYC School of Data 2022 slides
ℹ️ NOTE: qsv is a fork of the popular xsv utility, merging several pending PRs since xsv 0.13.0's May 2018 release. On top of xsv's 20 commands, it adds numerous new features, 27 additional commands, 6
applysubcommands & 33
applyoperations (for a total of 86). See FAQ for more details.
|apply[^4] ❇️||Apply series of string, date, math, currency & geocoding transformations to a CSV column. It also has some basic NLP functions (similarity, sentiment analysis, profanity, eudex & language detection).|
|behead||Drop headers from a CSV.|
|cat||Concatenate CSV files by row or by column.|
|count[^1]||Count the rows in a CSV file. (Instantaneous with an index.)|
|dedup[^2][^4]||Remove duplicate rows (See also
|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.|
|excel||Exports a specified Excel/ODS sheet to a CSV file.|
|exclude[^1]||Removes a set of CSV data from another set based on the specified columns.|
|explode||Explode rows into multiple ones by splitting a column value based on the given separator.|
|extsort[^4]||Sort an arbitrarily large CSV/text file using a multithreaded external merge sort algorithm.|
|fetch ❇️||Fetches data from web services for every row using HTTP Get. Comes with jql JSON query language support, dynamic throttling (RateLimit) & caching with optional Redis support for persistent caching.|
|fetchpost ❇️||Similar to
|fill||Fill empty values.|
|fixlengths||Force a CSV 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.
|fmt||Reformat a CSV with different delimiters, record terminators or quoting rules. (Supports ASCII delimited data.)|
|foreach ❇️||Loop over a CSV to execute bash commands. (not available on Windows)|
|frequency[^1][^3]||Build frequency tables of each column. (Uses multithreading to go faster if an index is present.)|
|generate ❇️||Generate test data by profiling a CSV using Markov decision process machine learning.|
|headers||Show the headers of a CSV. Or show the intersection of all headers between many CSV files.|
|index||Create an index for a CSV. This is very quick & provides constant time indexing into the CSV file. Also enables multithreading for
|input[^1]||Read CSV data with special quoting, trimming, line-skipping & UTF-8 transcoding rules. Typically used to "normalize" a CSV for further processing with other qsv commands.|
|join[^1]||Inner, outer, cross, anti & semi joins. Uses a simple hash index to make it fast.|
|jsonl||Convert newline-delimited JSON (JSONL/NDJSON) to CSV. See
|luau ❇️||Execute a Luau script over CSV lines to transform, filter or aggregate them.|
|partition||Partition a CSV based on a column value.|
|pseudo||Pseudonymise the value of the given column by replacing them with an incremental identifier.|
|py ❇️||Evaluate a Python expression over CSV lines to transform or filter them. Python's f-strings is particularly useful for extended formatting, with the ability to evaluate Python expressions as well.|
|rename||Rename the columns of a CSV efficiently.|
|replace||Replace CSV data using a regex.|
|reverse[^2]||Reverse order of rows in a CSV. Unlike the
|safenames||Modify headers of a CSV to only have "safe" names - guaranteed "database-ready" names.|
|sample[^1]||Randomly draw rows (with optional seed) from a CSV using reservoir sampling (i.e., use memory proportional to the size of the sample).|
|schema[^3]||Infer schema from CSV data, replete with data type & domain/range validation & output in JSON Schema format. Uses multithreading to go faster if an index is present. See
|search||Run a regex over a CSV. Applies the regex to each field individually & shows only matching rows.|
|searchset||Run multiple regexes over a CSV in a single pass. Applies the regexes to each field individually & shows only matching rows.|
|select||Select, re-order, duplicate or drop columns.|
|slice[^1][^2]||Slice rows from any part of a CSV. 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).|
|sniff[^1]||Quickly sniff CSV metadata (delimiter, header row, preamble rows, quote character, flexible, is_utf8, number of records, number of fields, field names & data types).|
|sort[^4]||Sorts CSV data in alphabetical, numerical, reverse or random (with optional seed) order (See also
|sortcheck[^1]||Check if a CSV is sorted. With the --json options, also retrieve record count, sort breaks & duplicate count.|
|split[^1][^3]||Split one CSV file into many CSV files of N chunks. (Uses multithreading to go faster if an index is present.)|
|stats[^1][^2][^3]||Infer data type (Null, String, Float, Integer, Date, DateTime) & compute descriptive statistics for each column in a CSV (sum, min/max, min/max length, mean, stddev, variance, nullcount, quartiles, IQR, lower/upper fences, skewness, median, mode & cardinality). Uses multithreading to go faster if an index is present.|
|table[^2]||Show aligned output of a CSV using elastic tabstops.|
|tojsonl[^3]||Smartly converts CSV to a newline-delimited JSON (JSONL/NDJSON). By scanning the CSV first, it "smartly" infers the appropriate JSON data type for each column. See
|transpose[^2]||Transpose rows/columns of a CSV.|
|validate[^1][^4]||Validate CSV data with JSON Schema (See
❇️: enabled by a feature flag on
qsv. Not available on
qsvdp binary variants.
[^1]: uses an index when available.
[^2]: loads the entire CSV into memory. Note that
transpose have modes that do not load the entire CSV into memory.
[^3]: multithreaded when an index is available.
[^4]: multithreaded even without an index.
brew install qsv
Prebuilt binary variants of the latest qsv version with more enabled features for Windows, Linux & macOS are also available for download, including binaries compiled with Rust Nightly/Unstable (more info).
There are three binary variants of qsv:
qsv- feature-capable(❇️), with the prebuilt binaries enabling all applicable features except Python [^6]
qsvlite- all features disabled (~40% of the size of
qsvdp- optimized for use with DataPusher+, with only DataPusher+ relevant commands (~40% of the the size of
Alternatively, you can install from source by installing Rust
qsv using Rust's cargo command[^5]:
[^5]: Of course, you'll also need a linker & a C compiler. Linux users should generally install GCC or Clang, according to their distribution’s documentation.
For example, if you use Ubuntu, you can install the
build-essential package. On macOS, you can get a C compiler by running
$ xcode-select --install.
For Windows, this means installing Visual Studio 2022. When prompted for workloads, include "Desktop Development with C++",
the Windows 10 or 11 SDK & the English language pack, along with any other language packs your require.
cargo install qsv --locked --features all_full
The binary will be installed in
Compiling from source also works similarly:
git clone firstname.lastname@example.org:jqnatividad/qsv.git cd qsv cargo build --release --locked --features all_full
The compiled binary will end up in
To enable optional features, use cargo
--features (see Feature Flags for more info):
cargo install qsv --locked --features apply,generate,luau,fetch,foreach,python,self_update,full # or shorthand cargo install qsv --locked --features all_full # or to install qsvlite cargo install qsv --locked --features lite # or to install qsvdp cargo install qsv --locked --features datapusher_plus # or when compiling from a local repo cargo build --release --locked --features apply,generate,luau,fetch,foreach,python,self_update,full # shorthand cargo build --release --locked --features all_full # for qsvlite cargo build --release --locked --features lite # for qsvdp cargo build --release --locked --features datapusher_plus
foreach feature is not available on Windows. The
python feature is not enabled on the prebuilt binaries. Compile with Python 3.6 and above development environment installed if you want to enable the
python feature. Lua support is enabled by default on the prebuilt binaries, with preference for
luau for platforms that support it.
Minimum Supported Rust Version
qsv's MSRV policy is to require Rust stable - currently version 1.65.
qsv's command-line options are quite extensive. Thankfully, since it uses docopt for CLI processing, we can take advantage of docopt.rs' tab completion support to make it easier to use qsv at the command-line (currently, only bash shell is supported):
# install docopt-wordlist cargo install docopt # IMPORTANT: run these commands from the root directory of your qsv git repository # to setup bash qsv tab completion echo "DOCOPT_WORDLIST_BIN=\"$(which docopt-wordlist)"\" >> $HOME/.bash_completion echo "source \"$(pwd)/scripts/docopt-wordlist.bash\"" >> $HOME/.bash_completion echo "complete -F _docopt_wordlist_commands qsv" >> $HOME/.bash_completion
qsv recognizes UTF-8/ASCII encoded, CSV (
.csv) & TSV files (
.tab). CSV files are assumed to have "," (comma) as a delimiter,
and TSV files, "\t" (tab) as a delimiter. The delimiter is a single ascii character that can be set either by the
--delimiter command-line option or
QSV_DEFAULT_DELIMITER environment variable or automatically detected when
QSV_SNIFF_DELIMITER is set.
When using the
--output option, qsv will UTF-8 encode the file & automatically change the delimiter used in the generated file based on the file extension - i.e. comma for
.csv, tab for
fetchpost commands also produces JSONL files when its invoked without the
--new-column option & TSV files with the
validate commands produce JSON files with their
schema command produces a JSON Schema Validation (Draft 7) file with the ".schema.json" file extension, which can be used with the
excel command recognizes Excel & Open Document Spreadsheet(ODS) files (
qsv validates against the RFC 4180 CSV standard. However IRL, CSV formats vary significantly & qsv is actually not strictly compliant with the specification so it can process "real-world" CSV files. qsv leverages the awesome Rust CSV crate to read/write CSV files.
Click here to find out more about how qsv conforms to the standard using this crate.
The following commands require UTF-8 encoded input (of which ASCII is a subset) -
For these commands, qsv checks if the input is UTF-8 encoded by scanning the first 8k & will abort if its not unless
QSV_SKIPUTF8_CHECK is set. On Linux & macOS, UTF-8 encoding is the default.
This was done to increase performance of these commands, as they make extensive use of
from_utf8_unchecked so as not to pay the repetitive utf-8 validation penalty, no matter how small, even for already utf-8 encoded files.
Should you need to re-encode CSV/TSV files, you can use the
input command to transcode to UTF-8. It will replace all invalid UTF-8 sequences with
�. Alternatively, there are several utilities you can use to do so on Linux/macOS & Windows.
Windows Usage Note
Unlike other modern operating systems, Microsoft Windows' default encoding is UTF16-LE. This will cause problems when redirecting qsv's output to a CSV file & trying to open it with Excel (which ignores the comma delimiter, with everything in the first column):
qsv stats wcp.csv > wcpstats.csv
Which is weird, since you would think Microsoft's own Excel would properly recognize UTF16-LE encoded CSV files. Regardless, to create a properly UTF-8 encoded file on Windows, use the
--output option instead:
# so instead of redirecting stdout to a file qsv stats wcp.csv > wcpstats.csv # do this instead qsv stats wcp.csv --output wcpstats.csv
python feature is NOT enabled by default on the prebuilt binaries, as doing so requires it to statically link to python, which presents distribution issues, as various operating systems have differing bundled Python versions.
If you wish to enable the
python feature - you'll just have to install/compile from source, making sure you have the development libraries for the desired Python version (Python 3.6 to 3.11 are supported) installed when doing so.
Note that if you plan to distribute your manually built
qsv with the
qsv will look for Python shared libraries (libpython* on Linux/macOS, python*.dll on Windows) against which it was compiled starting with the current directory & abort with an error if not found, detailing the Python library it was looking for.
Note that this will happen on qsv startup, even if you're not running the
PyO3 - the underlying crate that enables the
python feature, uses a build script to determine the Python version & set the correct linker arguments. By default it uses the python3 executable.
You can override this by setting
PYO3_PYTHON=python3.6), before installing/compiling qsv. See the PyO3 User Guide for more information.
py command cannot do aggregations because PyO3's GIL-bound memory limitations will quickly consume a lot of memory (see issue 449 for details).
To prevent this, the
py command processes CSVs in batches (default: 30,000 records), with a GIL pool for each batch, so no globals are available across batches.
Luau is a fast, small, safe, gradually typed embeddable scripting language derived from Lua. It lies at the heart of Roblox technology - powering all it's user generated content, with Roblox's own internal code having more than 2 millions lines of Luau.
Lua is much faster than Python, and Luau is even faster still - more so, as qsv precompiles Luau scripts into bytecode. In addition,
luau is embedded into qsv, has debug logging, can do aggregations with its
--epilogue options & has no external dependencies unlike the
||single ascii character to use as delimiter. Overrides
However, using the
||if set, the delimiter is automatically detected. Overrides
||if set, the first row will NOT be interpreted as headers. Supersedes
||if set to
||if set, automatically create an index when none is detected. Also automatically updates stale indices.|
||set to an ascii character. If set, any lines(including the header) that start with this character are ignored.|
||number of jobs to use for multithreaded commands (currently
||if set, prohibit self-update version check for the latest qsv release published on GitHub.|
||if set, date parsing will use DMY format. Otherwise, use MDY format (used with
||if set, makes
||if set, skip UTF-8 encoding check. Otherwise, for several commands that require UTF-8 encoded input (see UTF8-Encoding), qsv scans the first 8k.|
||reader buffer size (default (bytes): 16384)|
||writer buffer size (default (bytes): 65536)|
||desired level (default - off;
||when logging is enabled, the directory where the log files will be stored. If the specified directory does not exist, qsv will attempt to create it. If not set, the log files are created in the directory where qsv was started. See Logging for more info.|
||if set, enable the --progressbar option on the
||the maximum Redis connection pool size. (default: 20).|
||set time-to-live of Redis cached values (default (seconds): 2419200 (28 days)).|
||if set, enables cache hits to refresh TTL of cached values.|
Several dependencies also have environment variables that influence qsv's performance & behavior:
- Memory Management (mimalloc)
When incorporating qsv into a data pipeline that runs in batch mode, particularly with very large CSV files using qsv commands that load entire CSV files into memory, you can fine-tune Mimalloc's behavior using its environment variables.
- Network Access (reqwest)
qsv uses reqwest for its
--updatefunctions & will honor proxy settings set through the
ℹ️ NOTE: To get a list of all active qsv-relevant environment variables, run
qsv --envlist. Relevant env vars are defined as anything that starts with
MIMALLOC_& the proxy variables listed above.
qsv has several features:
mimalloc(default) - use the mimalloc allocator (see Memory Allocator for more info).
applycommand. This swiss-army knife of CSV transformations is very powerful, but it has a lot of dependencies that increases both compile time and binary size.
fetch- enables the
foreachcommand (not valid for Windows).
luaucommand. Embeds a Luau interpreter into qsv. Luau has type-checking, sandboxing, additional language operators, increased performance & other improvements over Lua.
pycommand. Note that qsv will look for the shared library for the Python version (Python 3.6 & above supported) it was compiled against & will abort on startup if the library is not found, even if you're not using the
pycommand. Check Python section for more info.
self_update- enable self-update engine, checking GitHub for the latest release. Note that if you manually built qsv,
self-updatewill only check for new releases. It will NOT offer the choice to update itself to the prebuilt binaries published on GitHub. You need not worry that your manually built qsv will be overwritten by a self-update.
full- enable to build
qsvbinary variant which is feature-capable.
all_full- enable to build
qsvbinary variant with all features enabled (apply,fetch,foreach,generate,luau,python,self_update).
lite- enable to build
qsvlitebinary variant with all features disabled.
datapusher_plus- enable to build
qsvdpbinary variant - the DataPusher+ optimized qsv binary.
nightly- enable to turn on nightly/unstable features in the
pyo3crates when building with Rust nightly/unstable.
qsvlite, as the name implies, always has non-default features disabled.
qsvcan be built with any combination of the above features using the cargo
--no-default-featuresflags. The prebuilt
qsvbinaries has all applicable features valid for the target platform[^6].
Dual-licensed under MIT or the UNLICENSE.
|qsv was made possible by|
|Standards-based, best-of-breed, open source solutions
to make your Data Useful, Usable & Used.
This project is unrelated to Intel's Quick Sync Video.