DOCSEARCHbyAlgolia
Submit Docs

csvkit Documentation

repository·master·Indexed 27 days ago

https://github.com/wireservice/csvkit

A suite of command-line tools for converting to and working with CSV and other tabular file formats. Version 2.2.0 includes utilities such as csvclean for data validation and correction, csvcut for column extraction, csvformat for format conversion, csvgrep for pattern searching, and csvjoin for SQL-like joins. The library also provides a high-performance Python module wrapping agate and a CSVKitUtility base class for creating custom CLI tools.

Tokens
27.1K
Snippets
47
Records
200
Agent score
89%

What's inside csvkit

  1. Overview of csvkit command categories

    master

    csvkit is a suite of command-line tools for working with CSV data, organized into three functional categories:

    1. Input: Tools for converting other formats into CSV (e.g., in2csv, sql2csv).
    2. Processing: Tools for manipulating CSV data (e.g., csvcut, csvgrep, csvjoin, csvsort, csvstack, csvclean).
    3. Output and Analysis: Tools for converting CSV to other formats or analyzing its content (e.g., csvsql, csvstat, csvjson, csvlook, csvformat, csvpy).

  2. Overview of csvkit

    master
    csvkit is a suite of command-line tools designed for converting to and working with CSV (Comma-Separated Values) and other tabular file formats. It provides a collection of utilities to inspect, transform, and manipulate tabular data directly from the command line.

  3. Install csvkit on macOS or Ubuntu

    master

    Follow these steps to install csvkit depending on your operating system:

    macOS (via Homebrew):

    1. Install Homebrew
    2. Install Python
    3. Install csvkit via pip

    Ubuntu:

    1. Install Python development headers, pip, setuptools, and build-essential
    2. Install csvkit via pip
    # macOS
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
brew install python
pip install csvkit

# Ubuntu
sudo apt-get install python-dev python-pip python-setuptools build-essential
pip install csvkit

  6. Build custom tools using agate

    master
    csvkit is generally not adding new tools to the core toolkit. If you wish to build your own CSV tool, it is recommended to create it as a separate Python package and use the agate library. agate is the library csvkit uses for most of its CSV reading and writing, and using it will help maintain common behavior with csvkit's core tools.

  7. Render CSV files as Markdown tables with csvlook

    master

    The csvlook command renders a CSV file in the console as a Markdown-compatible, fixed-width table. It can operate on a specified file or accept input via STDIN. If the resulting table is too wide for your terminal, pipe the output to less -S. If it is too long, pipe it to less or use grep to filter rows.

    csvlook examples/testfixed_converted.csv

  8. Use csvclean to report and fix CSV errors

    master

    The csvclean tool identifies and corrects common errors in CSV files, such as row length mismatches, empty columns, or unquoted cells containing line breaks.

    Error Reporting (Checks):

    • --length-mismatch: Reports rows that have a different number of columns than the header.
    • --empty-columns: Reports columns that are empty.
    • -a, --enable-all-checks: Enables all error reporting checks.

    Error Fixing:

    • --join-short-rows: Merges short rows (caused by unquoted cells with line breaks) into a single row. Use --separator SEPARATOR to specify the joining string (defaults to a newline).
    • --fill-short-rows: Adds missing delimiters to short rows. Use --fillvalue FILLVALUE to specify the value used to fill them (defaults to none).
    • --remove-empty-columns: Drops columns that are entirely empty.
    • --header-normalize-space: Strips leading/trailing whitespace and replaces whitespace sequences in the header with a single space.

    Output Behavior:

    • Standard Output (STDOUT): Contains the cleaned data. If --omit-error-rows is set, only rows passing all enabled checks are written. Otherwise, all rows are written.
    • Standard Error (STDERR): Contains error rows with line numbers and descriptions if any checks are enabled. If errors are found, the tool exits with code 1.

  9. Stack rows from multiple CSV files with `csvstack`

    master

    The csvstack command combines rows from multiple CSV files into a single output. It can optionally add a grouping column to identify which file each row originated from. If no files are provided as positional arguments, it accepts input from STDIN.

    csvstack -g 2009,2010 examples/realdata/FY09_EDU_Recipients_by_State.csv examples/realdata/Datagov_FY10_EDU_recp_by_State.csv

  10. Import CSV data directly into a SQL database

    master

    You can execute generated SQL statements directly on a database using the --db flag, which accepts a SQLAlchemy connection string. To import data, use the --insert flag.

    Common workflows:

    • Create table and import: Use --tables to specify the table name and --insert to load the data.
    • Bulk import directory: Pass multiple files to import them into the database.
    • Import identical headers: Use csvstack to combine multiple CSVs with identical headers before piping to csvsql for a single table import.

  11. Generate SQL statements from CSV files

    master

    Use csvsql to generate SQL CREATE TABLE and INSERT statements for one or more CSV files. You can specify the SQL dialect using the -i or --dialect flag. If no file is provided, csvsql accepts input via STDIN.

    csvsql -i postgresql examples/realdata/FY09_EDU_Recipients_by_State.csv

  12. Use csvcut to filter and truncate CSV files

    master

    The csvcut command is used to filter and truncate CSV files by selecting specific columns. It functions similarly to the Unix cut command but is designed for tabular data. You can provide a file as a positional argument or pipe data via STDIN.

    Note: csvcut does not perform row filtering; use csvgrep for that purpose. If a data row is longer than the header row, additional columns are truncated.