Repository files navigation

Table of Contents

Hi-ho "Quicksilver" away! logo details

qsv is a command line program for querying, slicing, indexing, analyzing, filtering, enriching, transforming, sorting, validating, joining, formatting & converting tabular data (CSV, spreadsheets,etc.). Commands are simple, composable &"blazing fast". *Commands *Installation Options *Whirlwind Tour /Notebooks /Lessons & Exercises *Cookbook *FAQ *Performance Tuning * 👉Benchmarks 🚀 *Environment Variables *Feature Flags *Goals/Non-goals *Testing *NYC School of Data 2022/csv,conf,v8 slides *Sponsor

Command	Description
apply✨ 📇🚀🧠🤖🔣👆	Apply series of string, date, math & currency transformations to given CSV column/s. It also has some basicNLP functions (similarity,sentiment analysis,profanity,eudex,language &name gender) detection.
applydp✨ 📇🚀🔣👆	applydp is a slimmed-down version ofapply with onlyDatapusher+ relevant subcommands/operations (qsvdp binary variant only).
behead	Drop headers from a CSV.
cat 🗄️	Concatenate CSV files by row or by column.
clipboard✨	Provide input from the clipboard or save output to the clipboard.
count 📇🏎️🐻‍❄️	Count the rows and optionally compile record width statistics of a CSV file. (11.87 seconds for a 15gb, 27m row NYC 311 dataset without an index. Instantaneous with an index.) If thepolars feature is enabled, uses Polars' multithreaded, mem-mapped CSV reader for fast counts even without an index
datefmt 📇🚀👆	Formats recognized date fields (19 formats recognized) to a specified date format usingstrftime date format specifiers.
dedup 🤯🚀👆	Remove duplicate rows (See alsoextdedup,extsort,sort &sortcheck commands).
describegpt 🌐🤖🪄	Infer extended metadata about a CSV using a GPT model fromOpenAI's API or an LLM from another API compatible with the OpenAI API specification such asOllama orJan.
diff 🚀🪄	Find the difference between two CSVs with ludicrous speed! e.g.compare two CSVs with 1M rows x 9 columns in under 600ms!
edit	Replace the value of a cell specified by its row and column.
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 📇👆	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.
extdedup 👆	Remove duplicate rows from an arbitrarily large CSV/text file using a memory-mapped,on-disk hash table. Unlike thededup command, this command does not load the entire file into memory nor does it sort the deduped file.
extsort 🚀📇👆	Sort an arbitrarily large CSV/text file using a multithreadedexternal merge sort algorithm.
fetch✨ 📇🧠🌐	Send/Fetch data to/from web services for every row usingHTTP Get. Comes withHTTP/2adaptive flow control,jaq JSON query language support, dynamic throttling (RateLimit) & caching with available persistent caching usingRedis or a disk-cache.
fetchpost✨ 📇🧠🌐	Similar tofetch, but usesHTTP Post (HTTP GET vs POST methods). Supports HTML form (application/x-www-form-urlencoded), JSON (application/json) and custom content types - with the ability to render payloads using CSV data using theMiniJinja template engine.
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. e.g.qsv slice -i 5 data.csv | qsv flatten.
fmt	Reformat a CSV with different delimiters, record terminators or quoting rules. (Supports ASCII delimited data.)
foreach✨ 📇	Execute a shell command once per line in given CSV file.
frequency 📇😣🏎️👆🪄	Buildfrequency tables of each column. Uses multithreading to go faster if an index is present.
geocode✨ 📇🧠🌐🚀🔣👆	Geocodes a location against an updatable local copy of theGeonames cities database. With caching and multi-threading, it geocodes up to 360,000 records/sec!
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 (even the 15gb, 28m row NYC 311 dataset takes all of 14 seconds to index) & provides constant time indexing/random access into the CSV. With an index,count,sample &slice work instantaneously; random access mode is enabled inluau; and multithreading (🏎️) is enabled for thefrequency,split,stats,schema &tojsonl commands.
input	Read CSV data with special commenting, quoting, trimming, line-skipping & non-UTF8 encoding handling rules. Typically used to "normalize" a CSV for further processing with other qsv commands.
join 👆	Inner, outer, right, cross, anti & semi joins. Automatically creates a simple, in-memory hash index to make it fast.
joinp✨ 🚀🐻‍❄️🪄	Inner, outer, right, cross, anti, semi, non-equi & asof joins using thePola.rs engine. Unlike thejoin command,joinp can process files larger than RAM, is multithreaded, has join key validation, a maintain row order option, pre and post-join filtering, join keys unicode normalization, supports "special"non-equi joins andasof joins (which isparticularly useful for time series data) & its output columns can be coalesced.
json 👆	Convert JSON to CSV.
jsonl 🚀🔣	Convert newline-delimited JSON (JSONL/NDJSON) to CSV. Seetojsonl command to convert CSV to JSONL.
lens✨	Interactively view, search & filter a CSV using thecsvlens engine.
luau 👑✨ 📇🌐🔣📚	Create multiple new computed columns, filter rows, compute aggregations and build complex data pipelines by executing aLuau0.663 expression/script for every row of a CSV file (sequential mode), or usingrandom access with an index (random access mode). Can process a single Luau expression orfull-fledged data-wrangling scripts using lookup tables with discrete BEGIN, MAIN and END sections. It is not just another qsv command, it is qsv'sDomain-specific Language (DSL) withnumerous qsv-specific helper functions to build production data pipelines.
partition 👆	Partition a CSV based on a column value.
pivotp✨ 🚀🐻‍❄️🪄	Pivot CSV data. Features "smart" aggregation auto-selection based on data type & stats.
pro	Interact with theqsv pro API.
prompt✨	Open a file dialog to either pick a file as input or save output to a file.
pseudo 🔣👆	Pseudonymise the value of the given column by replacing them with an incremental identifier.
py✨ 📇🔣	Create a new computed column or filter rows by evaluating a Python expression on every row of a CSV file. Python'sf-strings is particularly useful for extended formatting,with the ability to evaluate Python expressions as well.Requires Python 3.8 or greater.
rename	Rename the columns of a CSV efficiently.
replace 📇👆	Replace CSV data using a regex. Applies the regex to each field individually.
reverse 📇🤯	Reverse order of rows in a CSV. Unlike thesort --reverse command, it preserves the order of rows with the same key. If an index is present, it works with constant memory. Otherwise, it will load all the data into memory.
safenames	Modify headers of a CSV to only have"safe" names - guaranteed "database-ready"/"CKAN-ready" names.
sample 📇🌐🏎️	Randomly draw rows (with optional seed) from a CSV using seven different sampling methods -reservoir (default),indexed,bernoulli,systematic,stratified,weighted &cluster sampling. Supports sampling from CSVs on remote URLs.
schema 📇😣🏎️👆🪄	Infer schema from CSV data, replete with data type & domain/range validation & output inJSON Schema format. Uses multithreading to go faster if an index is present. Seevalidate command to use the generated JSON Schema to validate if similar CSVs comply with the schema.
search 📇👆	Run a regex over a CSV. Applies the regex to selected fields & 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, reverse, duplicate or drop columns.
slice 📇🏎️	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).
snappy 🚀🌐	Does streaming compression/decompression of the input using Google'sSnappy framing format (more info).
sniff 📇🌐	Quickly sniff & infer CSV metadata (delimiter, header row, preamble rows, quote character, flexible, is_utf8, average record length, number of records, content length & estimated number of records if sniffing a CSV on a URL, number of fields, field names & data types). It is also a general mime type detector.
sort 🚀🤯👆	Sorts CSV data in alphabetical (with case-insensitive option), numerical, reverse, unique or random (with optional seed) order (See alsoextsort &sortcheck commands).
sortcheck 📇👆	Check if a CSV is sorted. With the --json options, also retrieve record count, sort breaks & duplicate count.
split 📇🏎️	Split one CSV file into many CSV files. It can split by number of rows, number of chunks or file size. Uses multithreading to go faster if an index is present when splitting by rows or chunks.
sqlp✨ 📇🚀🐻‍❄️🗄️🪄	RunPolars SQL queries against several CSVs - converting queries to blazing-fastLazyFrame expressions, processing larger than memory CSV files. Query results can be saved in CSV, JSON, JSONL, Parquet, Apache Arrow IPC and Apache Avro formats. Supports automatic decompression of gzip, zstd and zlib compressed input files using theread_csv() table function.
stats 📇🤯🏎️👆🪄	Computesummary statistics (sum, min/max/range, sort order/sortiness, min/max/sum/avg length, mean, standard error of the mean (SEM), geometric/harmonic means, stddev, variance, Coefficient of Variation (CV), nullcount, max precision, sparsity, quartiles, Interquartile Range (IQR), lower/upper fences, skewness, median, mode/s, antimode/s, cardinality & uniqueness ratio) & make GUARANTEED data type inferences (Null, String, Float, Integer, Date, DateTime, Boolean) for each column in a CSV (more info). Uses multithreading to go faster if an index is present (with an index, can compile "streaming" stats on NYC's 311 data (15gb, 28m rows) in less than 7.3 seconds!).
table 🤯	Show aligned output of a CSV usingelastic tabstops. To interactively view a CSV, use thelens command.
template 📇🚀🔣📚	Renders a template using CSV data with theMiniJinja template engine (Example).
to✨ 🚀🗄️	Convert CSV files toPostgreSQL,SQLite, XLSX andData Package.
tojsonl 📇😣🚀🔣🪄	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. Seejsonl command to convert JSONL to CSV.
transpose 🤯	Transpose rows/columns of a CSV.
validate 📇🚀🌐📚	Validate CSV datablazingly-fast usingJSON Schema Validation (Draft 2020-12) (e.g.up to 780,031 rows/second1 usingNYC's 311 schema generated by theschema command) & put invalid records into a separate file with an accompanying detailed validation error report file. Supports a customcurrency format withISO-4217 validation, and a customdynamicEnum keyword that supports enum validation against a CSV on the filesystem, or on a URL (http/https/ckan and dathere URL schemes supported). If no JSON schema file is provided, validates if a CSV conforms to theRFC 4180 standard and is UTF-8 encoded.

✨: enabled by afeature flag.
📇: uses an index when available.
🤯: loads entire CSV into memory, thoughdedup,stats &transpose have "streaming" modes as well.
😣: uses additional memory proportional to the cardinality of the columns in the CSV.
🧠: expensive operations are memoized with available inter-session Redis/Disk caching for fetch commands.
🗄️:Extended input support.
🐻‍❄️: command powered by at the py-1.25.2 tag.
🤖: command uses Natural Language Processing & General AI techniques.
🏎️: multithreaded and/or faster when an index (📇) is available.
🚀: multithreaded even without an index.
: hasCKAN-aware integration options.
🌐: has web-aware options.
🔣: requires UTF-8 encoded input.
👆: has powerful column selector support. Seeselect for syntax.
🪄: "automagical" commands that uses stats and/or frequency tables to work "smarter" & "faster".
📚: has lookup table support, enabling runtime "lookups" against local or remote reference CSVs.

qsv's big brother -qsv pro is available for download from its website and on theMicrosoft Store. Apart from a Graphical User Interface, it's superpowered with additional features and capabilities - an API,CKAN integration, a Natural Language interface, no false-positive Windows Defender warnings and more!

Full-featured prebuiltbinary variants of the latest qsv version for Linux, macOS & Windows are availablefor download, including binaries compiled withRust Nightly (more info).

These prebuilt binaries are also built with CPU optimizations enabled for x86_64 (e.g.SSE4.2,AVX2,AVX512, etc. on Intel and AMD processors) and Apple Silicon processors (ARM64 SIMD NEON) for even more performance gains. This may prevent some older CPUs from running qsv. If so, "portable" binaries (all CPU optimizations disabled) are also included in the release zip archives (qsv with a "p" suffix - e.g.qsvp,qsvpliteqsvpdp).

For Windows, an MSI Installer wrapping the x86_64-pc-windows-msvc build is also available for download.

For macOS,"ad-hoc" signatures are used to sign our binaries, so you will need toset appropriate Gatekeeper security settings or run the following command to remove the quarantine attribute from qsv before you run it for the first time:

# replace qsv with qsvlite or qsvdp if you installed those binary variantsxattr -d com.apple.quarantine qsv

An additional benefit of using the prebuilt binaries is that they have theself_update feature enabled, allowing you to quickly update qsv to the latest version with a simpleqsv --update. For further security, theself_update feature only fetchesreleases from this GitHub repo and automatically verifies the signature of the downloaded zip archive before installing the update.

NOTE: Theluau feature is not available inmusl prebuilt binaries².

All prebuilt binaries zip archives are signed withzipsign with the following public keyqsv-zipsign-public.key. To verify the integrity of the downloaded zip archives:

# if you don't have zipsign installed yetcargo install zipsign# verify the integrity of the downloaded prebuilt binary zip archive# after downloading the zip archive and the qsv-zipsign-public.key file.# replace <PREBUILT-BINARY-ARCHIVE.zip> with the name of the downloaded zip archive# e.g. zipsign verify zip qsv-0.118.0-aarch64-apple-darwin.zip qsv-zipsign-public.keyzipsign verify zip<PREBUILT-BINARY-ARCHIVE.zip> qsv-zipsign-public.key

qsv is also distributed by several package managers and distros.

Here are the relevant commands for installing qsv using the various package managers and distros:

# Arch Linux AUR (https://aur.archlinux.org/packages/qsv)yay -S qsv# Homebrew on macOS/Linux (https://formulae.brew.sh/formula/qsv#default)brew install qsv# MacPorts on macOS (https://ports.macports.org/port/qsv/)sudo port install qsv# Nixpkgs on Linux/macOS (https://search.nixos.org/packages?channel=unstable&show=qsv&from=0&size=50&sort=relevance&type=packages&query=qsv)nix-shell -p qsv# Scoop on Windows (https://scoop.sh/#/apps?q=qsv)scoop install qsv# Void Linux (https://voidlinux.org/packages/?arch=x86_64&q=qsv)sudo xbps-install qsv

Note that qsv provided by these package managers/distros enable different features (Homebrew, for instance, only enables theapply andluau features. However, it does automatically install shell completion forbash,fish andzsh shells).

To find out what features are enabled in a package/distro's qsv, runqsv --version (more info).

In the true spirit of open source, these packages are maintained by volunteers who wanted to make qsv easier to install in various environments. They are much appreciated, and we loosely collaborate with the package maintainers through GitHub, but know that these packages are maintained by third-parties.

datHere also maintains a Debian package targeting the latest Ubuntu LTS on x86_64 architecture to make it easier to install qsv with DataPusher+.

To install qsv on Ubuntu/Debian:

wget -O - https://dathere.github.io/qsv-deb-releases/qsv-deb.gpg| sudo gpg --dearmor -o /usr/share/keyrings/qsv-deb.gpgecho"deb [signed-by=/usr/share/keyrings/qsv-deb.gpg] https://dathere.github.io/qsv-deb-releases ./"| sudo tee /etc/apt/sources.list.d/qsv.listsudo apt updatesudo apt install qsv

If you haveRust installed, you can also install from source using Rust's cargo command³:

cargo install qsv --locked --features all_features

The binary will be installed in~/.cargo/bin.

To install differentvariants and enable optional features, use cargo--features (seeFeature Flags for more info):

# to install qsv with all features enabledcargo install qsv --locked --bin qsv --features feature_capable,apply,fetch,foreach,geocode,luau,polars,python,self_update,to,ui# or shorthandcargo install qsv --locked --bin qsv -F all_features# or enable only the apply and polars featurescargo install qsv --locked --bin qsv -F feature_capable,apply,polars# or to install qsvlitecargo install qsv --locked --bin qsvlite -F lite# or to install qsvdpcargo install qsv --locked --bin qsvdp -F datapusher_plus,luau

NOTE: if you get compilation errors when runningcargo install, use Option 4 to compile from source usingcargo build. The errors are usually due tocargo install only using the latest release version of qsv's dependencies, and ignoringpatch.crates-io entries in our Cargo.toml.

Compiling from source also works similarly³:

git clone https://github.com/dathere/qsv.gitcd qsvcargo build --release --locked --bin qsv --features all_features

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

To compile differentvariants and enable optionalfeatures:

# to compile qsv with all features enabledcargo build --release --locked --bin qsv --features feature_capable,apply,fetch,foreach,geocode,luau,polars,python,self_update,to,ui# shorthandcargo build --release --locked --bin qsv -F all_features# or build qsv with only the fetch and foreach features enabledcargo build --release --locked --bin qsv -F feature_capable,fetch,foreach# for qsvlitecargo build --release --locked --bin qsvlite -F lite# for qsvdpcargo build --release --locked --bin qsvdp -F datapusher_plus,luau

NOTE: To build with Rust nightly, seeNightly Release Builds.Thefeature_capable,lite anddatapusher_plus are MUTUALLY EXCLUSIVE features. SeeSpecial Build Features for more info.

There are four binary variants of qsv:

• qsv -feature-capable(✨), with theprebuilt binaries enabling all applicable features except Python²
• qsvpy - same asqsv but with the Python feature enabled. Three subvariants are available - qsvpy310, qsvpy311 & qsvpy312 - which are compiled with the latest patch version of Python 3.10, 3.11 & 3.12 respectively.
• qsvlite - all features disabled (~13% of the size ofqsv)
• qsvdp - optimized for use withDataPusher+ with only DataPusher+ relevant commands; an embeddedluau interpreter;applydp, a slimmed-down version of theapply feature; the--progressbar option disabled; and the self-update only checking for new releases, requiring an explicit--update (~12% of the the size ofqsv).

NOTE: There are "portable" subvariants of qsv available with the "p" suffix -qsvp,qsvplite andqsvpdp. These subvariants are compiled without any CPU features enabled. Use these subvariants if you're getting "Illegal instruction" errors when running the regular qsv binaries.

qsv has extensive, extendableshell completion support. It currently supports the following shells:bash,zsh,powershell,fish,nushell,fig &elvish.

To enable shell completion, see theShell Completion documentation. If you're using Bash, you can also follow the step-by-step tutorial at100.dathere.com.

The--select option and several commands (apply,applydp,datefmt,exclude,fetchpost,replace,schema,search,searchset,select,sqlp &stats) allow the user to specify regular expressions. We use theregex crate to parse, compile and execute these expressions.⁴

Its syntax can be foundhere and"is similar to other regex engines, but it lacks several features that are not known how to implement efficiently. This includes, but is not limited to, look-around and backreferences. In exchange, all regex searches in this crate have worst case O(m * n) time complexity, where m is proportional to the size of the regex and n is proportional to the size of the string being searched."

If you want to test your regular expressions,regex101 supports the syntax used by theregex crate. Just select the "Rust" flavor.

qsv recognizes UTF-8/ASCII encoded, CSV (.csv), SSV (.ssv) and TSV files (.tsv &.tab). CSV files are assumed to have "," (comma) as a delimiter, SSV files have ";" (semicolon) as a delimiterand 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 orwith theQSV_DEFAULT_DELIMITER environment variable or automatically detected whenQSV_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, semicolon for.ssv, tab for.tsv &.tab files.

JSON files are recognized & converted to CSV with thejson command.JSONL/NDJSON files are also recognized & converted to/from CSV with thejsonl andtojsonl commands respectively.

Thefetch &fetchpost commands also produces JSONL files when its invoked without the--new-column option & TSV files with the--report option.

Theexcel,safenames,sniff,sortcheck &validate commands produce JSON files with their JSON options following theJSON API 1.1 specification, so it can return detailed machine-friendly metadata that can be used by other systems.

Theschema command produces aJSON Schema Validation (Draft 7) file with the ".schema.json" file extension, which can be used with thevalidate command to validate other CSV files with an identical schema.

Theexcel command recognizes Excel & Open Document Spreadsheet(ODS) files (.xls,.xlsx,.xlsm,.xlsb &.ods files).

Speaking of Excel, if you're having trouble opening qsv-generated CSV files in Excel, set the QSV_OUTPUT_BOM environment variable to add aByte Order Mark to the beginning of the generated CSV file. This is a workaround forExcel's UTF-8 encoding detection bug.

Theto command converts CSVs to.xlsx,Parquet &Data Package files, and populatesPostgreSQL andSQLite databases.

Thesqlp command returns query results in CSV, JSON, JSONL, Parquet,Apache Arrow IPC &Apache AVRO formats. Polars SQL also supports reading external files directly in various formats with itsread_csv,read_ndjson,read_parquet &read_ipctable functions.

Thesniff command can also detect the mime type of any file with the--no-infer or--just-mime options, may it be local or remote (http and https schemes supported).It can detect more than 130 file formats, including MS Office/Open Document files, JSON, XML, PDF, PNG, JPEG and specialized geospatial formats like GPX, GML, KML, TML, TMX, TSX, TTML.Clickhere for a complete list.

Thecat,headers,sqlp &to commands have extended input support (🗄️). If the input is- or empty, the command will try to use stdin as input. If it's not, it will check if its a directory, and if so, add all the files in the directory as input files.

If its a file, it will first check if it has an.infile-list extension. If it does, it will load the text file and parse each line as an input file path. This is a much faster and convenient way to process a large number of input files, without having to pass them all as separate command-line arguments. Further, the file paths can be anywhere in the file system, even on separate volumes. If an input file path is not fully qualified, it will be treated as relative to the current working directory. Empty lines and lines starting with# are ignored. Invalid file paths will be logged as warnings and skipped.

For both directory and.infile-list input, snappy compressed files with a.sz extension will be automatically decompressed.

Finally, if its just a regular file, it will be treated as a regular input file.

qsv supportsautomatic compression/decompression using theSnappy frame format. Snappy was chosen instead of more popular compression formats like gzip because it was designed forhigh-performance streaming compression & decompression (up to 2.58 gb/sec compression, 0.89 gb/sec decompression).

For all commands except theindex,extdedup &extsort commands, if the input file has an ".sz" extension, qsv willautomatically do streaming decompression as it reads it. Further, if the input file has an extended CSV/TSV ".sz" extension (e.g nyc311.csv.sz/nyc311.tsv.sz/nyc311.tab.sz), qsv will also use the file extension to determine the delimiter to use.

Similarly, if the--output file has an ".sz" extension, qsv willautomatically do streaming compression as it writes it.If the output file has an extended CSV/TSV ".sz" extension, qsv will also use the file extension to determine the delimiter to use.

Note however that compressed files cannot be indexed, so index-accelerated commands (frequency,schema,split,stats,tojsonl) will not be multithreaded. Random access is also disabled without an index, soslice will not be instantaneous andluau's random-access mode will not be available.

There is also a dedicatedsnappy command with four subcommands for direct snappy file operations — a multithreadedcompress subcommand (4-5x faster than the built-in, single-threaded auto-compression); adecompress subcommand with detailed compression metadata; acheck subcommand to quickly inspect if a file has a Snappy header; and avalidate subcommand to confirm if a Snappy file is valid.

Thesnappy command can be used to compress/decompress ANY file, not just CSV/TSV files.

Using thesnappy command, we can compress NYC's 311 data (15gb, 28m rows) to 4.95 gb in5.77 seconds with the multithreadedcompress subcommand -2.58 gb/sec with a 0.33 (3.01:1) compression ratio. Withsnappy decompress, we can roundtrip decompress the same file in16.71 seconds -0.89 gb/sec.

Compare that tozip 3.0, which compressed the same file to 2.9 gb in248.3 seconds on the same machine - 43x slower at 0.06 gb/sec with a 0.19 (5.17:1) compression ratio - for just an additional 14% (2.45 gb) of saved space. zip also took 4.3x longer to roundtrip decompress the same file in72 seconds -0.20 gb/sec.

NOTE: In addition tosnappy support, thesqlp command also supports automatic decompression of gzip, zstd and zlib compressed input files using theread_csv() table function. It also supports automatic compression of output files when using the Arrow, Avro and Parquet output formats (using the--format and--compression options).

qsv follows theRFC 4180 CSV standard. However, in real life, CSV formats vary significantly & qsv is actually not strictly compliant with the specification so it can process "real-world" CSV files.qsv leverages the awesomeRust CSV crate to read/write CSV files.

Clickhere to find out more about how qsv conforms to the standard using this crate.

When dealing with "atypical" CSV files, you can use theinput &fmt commands to normalize them to be RFC 4180-compliant.

qsv requires UTF-8 encoded input (of which ASCII is a subset).

Should you need to re-encode CSV/TSV files, you can use theinput command to "lossy save" to UTF-8 - replacing invalid UTF-8 sequences with� (U+FFFD REPLACEMENT CHARACTER).

Alternatively, if you want to truly transcode to UTF-8, there are several utilities likeiconv that you can use to do so onLinux/macOS &Windows.

Unlike other modern operating systems, Microsoft Windows'default encodingis UTF16-LE. This will cause problems when redirecting qsv's output to a CSV file in Powershell & trying to open it with Excel - everything will be in the first column, as the UTF16-LE encoded CSV file will not be properly recognized by Excel.

# the following command will produce a UTF16-LE encoded CSV file on Windowsqsv stats wcp.csv > wcpstats.csv

Which is weird, since you'd thinkMicrosoft'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 on Windowsqsv stats wcp.csv > wcpstats.csv# do this instead, so it will be properly UTF-8 encodedqsv stats wcp.csv --output wcpstats.csv

Alternatively, qsv can add aByte Order Mark (BOM) to the beginning of a CSV to indicate it's UTF-8 encoded. You can do this by setting theQSV_OUTPUT_BOM environment variable to1.

This will allow Excel on Windows to properly recognize the CSV file as UTF-8 encoded.

Note that this is not a problem with Excel on macOS, as macOS (like most other *nixes) uses UTF-8 as its default encoding.

Nor is it a problem with qsv output files produced on other operating systems, as Excel on Windows can properly recognize UTF-8 encoded CSV files.

For complex data-wrangling tasks, you can use Luau and Python scripts.

Luau is recommended over Python for complex data-wrangling tasks as it is faster, more memory-efficient, has no external dependencies and has several data-wrangling helper functions as qsv's DSL.

SeeLuau vs Python for more info.

qsv supports three memory allocators - mimalloc (default), jemalloc and the standard allocator.
SeeMemory Allocator for more info.

It also has Out-of-Memory prevention, with two modes - NORMAL (default) & CONSERVATIVE.
SeeOut-of-Memory Prevention for more info.

qsv supports an extensive list of environment variables and supports.env files to set them.

For details, seeEnvironment Variables and thedotenv.template.yaml file.

qsv has severalfeature flags that can be used to enable/disable optional features.

SeeFeatures for more info.

qsv's MSRV policy is to require the latest stableRust version that issupported by Homebrew, currently.qsv itself may upgrade its MSRV, but a new qsv release will only be made once Homebrew supports the latest Rust stable.

QuickSilver's goals, in priority order, are to be:

• As Fast as Possible - To do so, it has frequent releases, an aggressive MSRV policy, takes advantage of CPU features, employsvarious caching strategies, usesHTTP/2, and is multithreaded when possible and it makes sense. It also uses the latest dependencies when possible, and will use Cargopatch to get unreleased fixes/features from its dependencies. SeePerformance for more info.
• Able to Process Very Large Files - Most qsv commands are streaming, using constant memory, and can process arbitrarily large CSV files. For those commands that require loading the entire CSV into memory (denoted by 🤯), qsv has Out-of-Memory prevention, batch processing strategies and "ext"ernal commands that use the disk to process larger than memory files. SeeMemory Management for more info.
• A Complete Data-Wrangling Toolkit - qsv aims to be a comprehensive data-wrangling toolkit that you can use for quick analysis and investigations, but is also robust enough for production data pipelines. Its many commands are targeted towards common data-wrangling tasks and can be combined/composed into complex data-wrangling scripts with its Luau-based DSL.
  Luau will also serve as the backbone of a whole library ofqsv recipes - reusable scripts for common tasks (e.g. street-level geocoding, removing PII, data enrichment, etc.) that prompt for easily modifiable parameters.
• Composable/Interoperable - qsv is designed to be composable, with a focus on interoperability with other common CLI tools like 'awk', 'xargs', 'ripgrep', 'sed', etc., and with well known ETL/ELT tools like Airbyte, Airflow, Pentaho Kettle, etc. Its commands can be combined with other tools via pipes, and it supports other common file formats like JSON/JSONL, Parquet, Arrow IPC, Avro, Excel, ODS, PostgreSQL, SQLite, etc. SeeFile Formats for more info.
• As Portable as Possible - qsv is designed to be portable, with installers on several platforms with an integrated self-update mechanism. In preference order, it supports Linux, macOS and Windows. SeeInstallation Options for more info.
• As Easy to Use as Possible - qsv is designed to be easy to use. As easy-to-use that is,as command line interfaces go 🤷. Its commands have numerous options but have sensible defaults. The usage text is written for a data analyst audience, not developers; and there are numerous examples in the usage text, with the tests doubling as examples as well. Withqsv pro, it has much expanded functionality while being easier to use with its Graphical User Interface.
• As Secure as Possible - qsv is designed to be secure. It has no external runtime dependencies, iswritteninRust, and it's codebase is automatically audited for security vulnerabilities with automatedDevSkim,"cargo audit" andCodacy Github Actions workflows.
  It uses the latest stable Rust version, with an aggressive MSRV policy and the latest version of all its dependencies.It has an extensive test suite with ~1,800 tests, including severalproperty tests whichrandomly generate parameters for oft-used commands.
  Its prebuilt binary archives arezipsigned, so you canverify their integrity. Its self-update mechanism automatically verifies the integrity of the prebuilt binaries archive before applying an update.SeeSecurity for more info.
• As Easy to Contribute to as Possible - qsv is designed to be easy to contribute to, with a focus on maintainability. It's modular architecture allows the easy addition of self-contained commands gated by feature flags, the source code is heavily commented, the usage text is embedded, and there are helper functions that make it easy to create additional commands and supporting tests. SeeFeatures andContributing for more info.

QuickSilver's non-goals are to be:

• As Small as Possible - qsv is designed to be small, but not at the expense of performance, features, composability, portability, usability, security or maintainability. However, we do have aqsvlite variant that is ~13% of the size ofqsv and aqsvdp variant that is ~12% of the size ofqsv. Those variants, however, have reduced functionality.Further, several commands are gated behind feature flags, so you can compile qsv with only the features you need.
• Multi-lingual - qsv'susage text andmessages are English-only. There are no plans to support other languages. This does not mean it can only process English input files.
  It can process well-formed CSVs inany language so long as its UTF-8 encoded. Further, it supports alternate delimiters/separators other than comma; theapply whatlang operation detects 69 languages; and itsapply thousands, currency and eudex operations supports different languages and country conventions for number, currency and date parsing/formatting.
  Finally, though the default Geonames index of thegeocode command is English-only, the index can be rebuilt with thegeocode index-update subcommand with the--languages option to return place names in multiple languages (with support for 254 languages).

qsv has ~1,800 tests in thetests directory.Each command has its own test suite in a separate file with the conventiontest_<COMMAND>.rs.Apart from preventing regressions, the tests also serve as good illustrative examples, and are often linked from the usage text of each corresponding command.

To test each binary variant:

# to test qsvcargotest --features all_features# to test qsvlitecargotest --features lite# to test all tests with "stats" in the name with qsvlitecargotest stats --features lite# to test qsvdpcargotest --features datapusher_plus,luau# to test a specific command# here we test only stats and use the# t alias for test and the -F shortcut for --featurescargo t stats -F all_features# to test a specific command with a specific feature# here we test only luau command with the luau featurecargo t luau -F feature_capable,luau# to test the count command with multiple features# we use "test_count" as we don't want to run other tests# that have "count" in the testname - e.g. test_geocode_countryinfocargo t test_count -F feature_capable,luau,polars# to test using an alternate allocator# other than the default mimalloc allocatorcargo t --no-default-features -F all_features,jemallocator

Dual-licensed under MIT or theUNLICENSE.

qsv is a fork of the popularxsv utility, merging several pending PRssince xsv 0.13.0's May 2018 release. On top of xsv's 20 commands, it adds numerous new features; 44 additional commands; 4apply subcommands & 38 operations; 4to subcommands; 3cat subcommands; 7geocode subcommands & 4 index operations; and 4snappy subcommands.SeeFAQ for more details.

qsv was made possible by
Standards-based, best-of-breed, open source solutions to make yourData Useful, Usable & Used.

This project is unrelated toIntel's Quick Sync Video.