Table of Contents
	qsv is a command line program for indexing, slicing, analyzing, splitting, enriching, validating & joining CSV files. Commands are simple, fast & composable. * Available Commands * Installation * Whirlwind Tour * Cookbook * FAQ * Changelog * Performance Tuning * Benchmarks * NYC School of Data 2022 slides * Sponsor

NOTE: qsv is a fork of the popular xsv utility, merging several pending PRs since xsv 0.13.0's May 2018 release. It also has numerous new features & 53 additional commands/subcommands/operations (for a total of 73). See FAQ for more details.

Pre-built binaries for Windows, Linux and macOS are available from GitHub.

There are three versions of qsv. qsv supports features, with the pre-built binaries enabling all valid platform features⁶; qsvlite has all features disabled (half the size of qsv); qsvdp is optimized for use with DataPusher+, with only DataPusher+ relevant commands and the self-update engine removed (a sixth of the size of qsv).

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

cargo install qsv --features full

If you encounter compilation errors, ensure you're using the exact version of the dependencies qsv was built with by issuing:

cargo install qsv --locked --features full

Compiling from this repository also works similarly:

git clone [email protected]:jqnatividad/qsv.git
cd qsv
cargo build --release --features full
# or if you encounter compilation errors
cargo build --release --locked --features full

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

To enable optional features, use cargo --features (see Feature Flags for more info):

cargo install qsv --features apply,generate,lua,fetch,foreach,python,full
# or to build qsvlite
cargo install qsv --features lite
# or to build qsvdp
cargo install qsv --features datapusher_plus

# or when compiling from a local repo
cargo build --release --features apply,generate,lua,fetch,foreach,python,full
# for qsvlite
cargo build --release --features lite
# for qsvdp
cargo build --release --features datapusher_plus

Building qsv requires Rust stable - currently version 1.61.0.

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) and TSV files (.tsv and .tab). CSV files are assummed 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 with the QSV_DEFAULT_DELIMITER environment variable or automatically detected when QSV_SNIFF_DELIMITER is set.

When using the --output option, note that qsv will UTF-8 encode the file and automatically change the delimiter used in the generated file based on the file extension - i.e. comma for .csv, tab for .tsv and .tab files.

JSONL/NDJSON files are also recognized and converted to CSV with the jsonl command.

The fetch command also produces JSONL files when its invoked without the --new-column option.

The sniff and validate commands produce JSON files with their --json and --pretty-json options.

The excel command recognizes Excel and Open Document Spreadsheet(ODS) files (.xls, .xlsx, .xlsm, .xlsb and .ods files).

qsv validates against the RFC 4180 CSV standard. However IRL, CSV formats vary significantly and qsv is actually not strictly compliant with the specification so it can process "real-world" CSV files. qsv leverages the awesome Rust CSV library, which in turn, is built on top of the csv-core library to read CSV files.

Click here to find out how qsv conforms to the standard with csv-core.

qsv requires UTF-8 encoded (of which ASCII is a subset) input files. On startup, it scans the input if it's UTF-8 encoded (for files, the first 8k; for stdin, the entire buffer), and will abort if its not unless QSV_SKIPUTF8_CHECK is set. On Linux and macOS, UTF-8 encoding is the default.

Should you need to reencode 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 and Windows.

Unlike other modern operating systems, Windows' default encoding is UTF16-LE. This will cause problems when redirecting qsv's output to a CSV file and 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 Excel would properly recognize UTF16-LE encoded CSV files. Regardless, to create a properly UTF-8 encoded file, use the --output option instead:

qsv stats wcp.csv --output wcpstats.csv

• QSV_DEFAULT_DELIMITER - single ascii character to use as delimiter. Overrides --delimeter option. Defaults to "," (comma) for CSV files and "\t" (tab) for TSV files, when not set. Note that this will also set the delimiter for qsv's output to stdout. However, using the --output option, regardless of this environment variable, will automatically change the delimiter used in the generated file based on the file extension - i.e. comma for .csv, tab for .tsv and .tab files.
• QSV_SNIFF_DELIMITER - when set, the delimiter is automatically detected. Overrides QSV_DEFAULT_DELIMITER and --delimiter option.
• QSV_NO_HEADERS - when set, the first row will NOT be interpreted as headers. Supersedes QSV_TOGGLE_HEADERS.
• QSV_TOGGLE_HEADERS - if set to 1, toggles header setting - i.e. inverts qsv header behavior, with no headers being the default, and setting --no-headers will actually mean headers will not be ignored.
• QSV_AUTOINDEX - when set, automatically create an index when none is detected. Also automatically updates stale indices.
• QSV_SKIPUTF8_CHECK - when set, skip UTF-8 encoding check. Otherwise, qsv scans the first 8k of files. For stdin, it scans the entire buffer.
• QSV_MAX_JOBS - number of jobs to use for multithreaded commands (currently dedup, extsort, frequency, schema, sort, split, stats and validate). If not set, max_jobs is set to the detected number of logical processors. See Multithreading for more info.
• QSV_REGEX_UNICODE - if set, makes search, searchset and replace commands unicode-aware. For increased performance, these commands are not unicode-aware and will ignore unicode values when matching and will panic when unicode characters are used in the regex.
• QSV_RDR_BUFFER_CAPACITY - set to change reader buffer size (bytes - default when not set: 16384)
• QSV_WTR_BUFFER_CAPACITY - set to change writer buffer size (bytes - default when not set: 65536)
• QSV_COMMENT_CHAR - set to a comment character which will ignore any lines (including the header) that start with this character (default: comments disabled).
• QSV_LOG_LEVEL - set to desired level (default - off, error, warn, info, trace, debug).
• QSV_LOG_DIR - 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.
• QSV_NO_UPDATE - prohibit self-update version check for the latest qsv release published on GitHub.
• QSV_REDIS_CONNECTION_STRING - the fetch command can use Redis to cache responses. By default it connects to redis:127.0.0.1:6379. Set to connect to another Redis instance.
• QSV_REDIS_TTL_SECONDS - by default, Redis cached values have a time-to-live of 2,419,200 seconds (28 days).
• QSV_REDIS_TTL_REFRESH- set to enable 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 fetch, validate and --update functions and will honor proxy settings set through HTTP_PROXY, HTTPS_PROXY and NO_PROXY.

NOTE: To get a list of all active qsv-relevant environment variables, run qsv --envlist.

qsv has several features:

• mimalloc (default) - use the mimalloc allocator (see Memory Allocator for more info).
• apply - enable apply command. 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 - enable fetch command.
• generate - enable generate command.
• full - enable to build qsv.
• lite - enable to build qsvlite.
• datapusher_plus - enable to build qsvdp.
• nightly - enable to turn on nightly/unstable features in the rand and regex creates when building with Rust nightly/unstable.

The following "power-user" commands can be abused and present "foot-shooting" scenarios.

• lua - enable lua command.
• foreach - enable foreach command (not valid for Windows).
• python - enable py command (requires Python 3.8+). Note that qsv will automatically use the currently activated python version when run in a virtual environment.

NOTE: qsvlite, as the name implies, always has non-default features disabled. qsv can be built with any combination of the above features using the cargo --features & --no-default-features flags. The pre-built qsv binaries has all applicable features enabled for the target platform⁶.

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.