Taming Table Chaos Right from the Terminal

If you regularly receive data exports, you probably know the pain. A three-hundred-megabyte file, Windows-1251 encoding, delimiter is a semicolon in some places and a comma in others, and there are unescaped line breaks inside rows. Opening this in Excel is a surefire way to freeze your office suite completely or accidentally strip leading zeros from phone numbers. Writing a quick Python script with pandas just to do a sanity check is also a hassle.

In such situations, csvkit comes to the rescue — a set of command-line utilities for working with tabular data. The project has been on GitHub since 2011, has gathered over 6400 stars, and still remains one of the most convenient ways to dissect CSVs right in bash.

What this utility set can do

The package creators, journalism outlet Wire Services, were inspired by tools like pdftk and GDAL. The idea is simple: give developers and data analysts a set of specialized commands for UNIX pipelines to clean, slice, format, and explore tabular files without writing code.

The utilities are split by task: conversion, slicing, analysis, and SQL query execution.

Quick preview and basic statistics

The csvlook command turns raw CSV into a neat ASCII table with proper column alignment. Combined with head or less, you get an excellent preview:

head -n 20 data.csv | csvlook

If you need to understand the structure of an unknown file, run csvstat. The utility analyzes data types in each column, finds missing values, calculates minimum, maximum, average, and shows a list of the most frequent values:

csvstat data.csv

Slicing and filtering

Traditional UNIX utilities like cut and grep often break on complex CSVs when commas or line breaks appear inside quoted fields. The package has specialized alternatives:

csvcut cuts out the needed columns by name or number:

csvcut -c "user_id,email,status" users.csv > clean_users.csv

csvgrep filters rows by values or regular expressions:

csvgrep -c "status" -m "active" users.csv

SQL right on top of files

One of the most convenient commands is csvsql. It can do two things. First, it generates a DDL schema for PostgreSQL, MySQL, or SQLite based on analyzing data types in the file. Second, it lets you write SQL queries directly against CSV files on disk via built-in SQLite:

csvsql --query "select status, count(*) from users group by status" users.csv

You can also join two different files together as if they were regular database tables:

csvsql --query "select u.name, o.total from users u join orders o on u.id = o.user_id" users.csv orders.csv

Format conversion

The in2csv tool converts pretty much anything to CSV: Excel files (.xls and .xlsx), JSON, NDJSON, and fixed-width. No need to launch Excel just to resave a received report:

in2csv report.xlsx --sheet "Sales" > sales.csv

How it works under the hood

The package is written in Python and distributed via PyPI. It uses SQLAlchemy for database operations, which explains the flexibility of csvsql when working with different SQL dialects.

The main advantage of the architecture is compatibility with standard input and output (stdin/stdout). Commands are easily chained through a regular pipe:

in2csv data.xlsx | csvgrep -c "country" -m "RU" | csvcut -c 1,3,5 | csvlook

This approach has a downside, though. Since the library is written in pure Python with runtime type checking, performance drops on multi-gigabyte files. For terabyte-scale exports, you're better off with xsv in Rust or duckdb, but for everyday tasks up to a couple hundred megabytes, csvkit's speed is more than enough.

Where it comes in handy in practice

1. Checking data structure on a remote server without GUI.
2. Pre-cleaning files in bash scripts before loading into an ETL pipeline.
3. Generating database table schemas for a new export (csvsql -i postgresql data.csv).
4. Quickly merging two disparate files by a common key via csvjoin.

Is it worth installing

The package installs with a single pip command:

pip install csvkit

If you often parse logs, work with database imports, or receive reports as archives with Excel and CSV, this utility set will save you a lot of nerves. It eliminates the need to open a heavy editor or write one-off scripts for basic filtering of two columns. Start with csvlook and in2csv — they pay for the installation in the first five minutes of use.