csvs

csvs (CSV Sql) is a command-line tool designed to streamline SQL queries on CSV or TSV files using an embedded SQLite engine. Whether you're a data analyst managing datasets or a developer processing text-based data, csvs enhances your workflow with SQL's full capabilities.

• Features
  • SQL Power for CSV Files
  • Automatic Encoding Detection
  • Multi-File Handling
  • Customizable Output
  • Interactive Mode
  • Multi-Statement Query Support
• Usage
  • Interactive Mode
  • Command Options
• Example
• SQL Query Notes
  • Mapping CSVs to Table Names
  • Quoting Columns with Special Characters
  • --in-no-header Option
  • Execute Multiple Statements in a Single Query
• Error Handling
• Build
• Limitations
• Acknowledgments
• License

Features

SQL Power for CSV Files

Run powerful SQL queries like JOIN, GROUP BY, SUM(), or COUNT() on CSV data. Query, filter, sort, group, and combine data more flexibly than traditional spreadsheet tools.

Automatic Encoding Detection

No more guessing file encodings! csvs auto-detects character encodings, ensuring smooth processing without garbled text or broken queries.

Multi-File Handling

Easily join multiple files with --in-file to create a temporary SQLite database. Perform SQL joins across files in seconds.

Customizable Output

Export query results as:

• CSV or TSV: For sharing or downstream processing.
• SQLite Database: Keep results as .db files for later queries.

Customize delimiters, headers, and quoting styles for total control.

Interactive Mode

Run csvs interactively when no query is provided. Explore data as SQLite tables, run ad-hoc queries, and preview results.

Multi-Statement Query Support

Execute multiple SQL statements in one command. Transform data across steps, with only the final result displayed.

Usage

• Display help:

csvs --help

Interactive Mode

• If neither --query nor --source is specified, csvs starts in interactive mode, allowing you to:
  • View and explore tables.
  • Preview table data.
  • Save query results to files.

See Interactive Mode Guide

Command Options

See Command Options Guide

Example

• Display version:

csvs --version

• Select "city","town","phone" from ./data/address.csv and save to picked.csv:

csvs -i ./data/address.csv -q 'SELECT "city","town","phone" FROM "address.csv"' -o picked.csv

• Read CSV data from STDIN:

csvs -q 'SELECT "city","town","phone" FROM "stdin"' < ./data/address.csv > picked.csv

or

cat ./data/address.csv | csvs -q 'SELECT "city","town","phone" FROM "stdin"' > picked.csv

• Select all fields from ./left.csv joined with ./right.tsv on a common field name. Multiple --in-file flags can be used to handle multiple input files in one command:

csvs -i ./left.csv -i ./right.tsv -q 'SELECT * FROM "left.csv" AS l JOIN "right.tsv" AS r ON l."name"=r."name"'

• Use standard SQLite functions (e.g., UPPER(), LOWER(), LENGTH(), COUNT(), SUM()).
  Example: Export the generated tables to a SQLite database file with --out-database:

csvs -i people.csv -q 'SELECT "city",COUNT(*) FROM "people.csv" GROUP BY "city" ORDER BY COUNT(*) DESC' --out-database out.db

• Start in interactive mode (triggered when neither --query nor --source is specified):

csvs -i MOCK_DATA.csv

SQL Query Notes

Mapping CSVs to Table Names

--in-file names map directly to SQLite tables (e.g. ./sample/address.csv becomes "address.csv", and data.2024.csv becomes "data.2024.csv").

Quoting Columns with Special Characters

If a CSV file name or header contains spaces, punctuation, or reserved words, the column or table name must be quoted ( e.g., "first name").
Example:

SELECT "first name", "last name" FROM "contacts.csv"

--in-no-header Option

If --in-no-header is specified, columns are named "c1", "c2", "c3", and so on automatically.

Execute Multiple Statements in a Single Query

csvs can process multiple SQL statements if each statement is separated by a semicolon. Only the final statement's result is displayed.
Example:

SELECT "first name" FROM "contacts.csv";
SELECT "age" FROM "contacts.csv"; 

In this example, csvs executes both queries but only shows the output of the second SELECT "age" FROM "contacts.csv".

Error Handling

See Error Handling Guide

Building csvs

See Build Guide

Limitations

• Interactive mode cannot be used if CSV data is provided via STDIN. Use --in-file to specify CSV files instead.
• Since csvs loads entire CSV files into memory, large files may require significant RAM.
• CSV files with filenames starting with sqlite_ cannot be specified using the --in-file option, as names beginning with sqlite_ are reserved by the SQLite database.

Acknowledgments

csvs is powered by these awesome projects:

• SQLite
• anyhow
• chardetng
• clap
• clap-help
• csv
• encoding_rs
• encoding_rs_rw
• indicatif
• lazy-regex
• ratatui
• r2d2
• r2d2_sqlite
• rusqlite
• smashquote
• sqlparser
• tracing
• tracing-logfmt
• tuirealm

License

• csvs is licensed under the MIT license.