csvkit — Command-Line Swiss Army Knife for CSV Data

Introduction

csvkit is a collection of command-line tools written in Python for working with CSV files. It converts data from Excel, JSON, and SQL databases into CSV, provides inspection and statistics commands, and lets you query CSV files with SQL — all without leaving the terminal.

What csvkit Does

• Converts Excel, JSON, fixed-width, and database tables into clean CSV with in2csv and sql2csv
• Inspects CSV structure with csvstat for column types, min/max, and frequency counts
• Queries CSV files using SQL syntax via csvsql, including joins across files
• Cleans and transforms data with csvcut (select columns), csvgrep (filter rows), and csvsort
• Pretty-prints CSV as aligned tables with csvlook

Architecture Overview

Each csvkit tool is a standalone Python CLI that reads CSV from stdin or a file, processes it using the agate tabular data library, and writes results to stdout. csvsql translates CSV into a temporary SQLite database, runs the SQL query, and returns results as CSV. This composable design lets users chain tools with Unix pipes.

Self-Hosting & Configuration

• Install via pip or pipx for isolated environments
• Requires Python 3.8 or later; no compiled dependencies
• Set default CSV dialect options (delimiter, quoting) via command-line flags
• Integrate with shell scripts by piping between csvkit tools and standard Unix utilities
• Use csvsql --insert to load CSV data into PostgreSQL, MySQL, or SQLite databases

Key Features

• Converts from Excel, JSON, fixed-width, and SQL to CSV with in2csv and sql2csv
• Runs SQL queries directly on CSV files without importing into a database
• Provides descriptive statistics per column with csvstat
• Composable Unix-style tools that chain via pipes
• Pretty-prints CSV as aligned terminal tables with csvlook

Comparison with Similar Tools

• xsv — Rust-based and faster for large files but lacks SQL querying and format conversion
• Miller (mlr) — processes CSV, JSON, and TSV with its own verb language; csvkit uses standard SQL
• q — runs SQL on CSV but does not include conversion or statistics tools
• pandas — Python library for interactive analysis; csvkit is purpose-built for CLI workflows
• DuckDB CLI — analytical SQL engine that reads CSV natively; more powerful but heavier

FAQ

Q: Can csvkit handle large files? A: csvkit processes data in streaming fashion for most tools, but csvsql loads data into SQLite, which can be slow for very large files.

Q: Does csvkit support TSV or other delimiters? A: Yes. Use the -d flag to specify any delimiter (e.g., -d " " for TSV).

Q: Can I join two CSV files with SQL? A: Yes. Pass multiple files to csvsql and write a JOIN query referencing them by filename.

Q: Does csvkit validate or clean malformed CSV? A: csvclean detects and optionally fixes common issues like inconsistent row lengths.

Sources

• https://github.com/wireservice/csvkit
• https://csvkit.readthedocs.io