csvkit

https://github.com/wireservice/csvkit
Admin
A suite of utilities for converting to and working with CSV, the king of tabular file formats.
Tokens:24,347
Snippets:173
Trust Score:6.9
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Installing csvkit with pip (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Installs the core csvkit library using the Python package installer, pip. This is the standard method for installing csvkit. Requires Python and pip to be installed.

```bash
pip install csvkit
```

--------------------------------

### Installing csvkit with Zstandard Support (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Installs csvkit along with optional dependencies required for working with Zstandard compressed files (.zst). Use this command if you need to process Zstandard files. Requires Python, pip, and Zstandard libraries.

```bash
pip install csvkit[zstandard]
```

--------------------------------

### Installing csvkit with Homebrew (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Installs the csvkit library system-wide using the Homebrew package manager on macOS or Linux. This provides an alternative installation method for users familiar with Homebrew. Requires Homebrew to be installed.

```bash
brew install csvkit
```

--------------------------------

### Getting Started with csvkit Development (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/contributing.rst

Provides the necessary bash commands to clone the csvkit repository, navigate into the project directory, and install the project with testing dependencies for local development.

```bash
git clone git://github.com/wireservice/csvkit.git
cd csvkit
pip install -e .[test]
```

--------------------------------

### Displaying CSV as Formatted Table (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `csvlook` tool from csvkit to display the content of `data.csv` in a human-readable, formatted table in the terminal. This provides a quick way to inspect the data structure and values. Requires csvkit to be installed.

```bash
csvlook data.csv
```

--------------------------------

### Creating and Entering Tutorial Directory (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Creates a new directory named `csvkit_tutorial` and then changes the current working directory into the newly created one. This sets up a clean workspace for the tutorial files. Requires basic command-line access.

```bash
mkdir csvkit_tutorial
cd csvkit_tutorial
```

--------------------------------

### Listing CSV Column Names and Indices (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `csvcut` tool with the `-n` option to list the names and corresponding 1-based indices of all columns in the `data.csv` file. This is useful for identifying columns before selecting or manipulating them. Requires csvkit to be installed. The `$` prefix indicates a typical shell prompt.

```console
$ csvcut -n data.csv
  1: state
  2: county
  3: fips
  4: nsn
  5: item_name
  6: quantity
  7: ui
  8: acquisition_cost
  9: total_cost
 10: ship_date
 11: federal_supply_category
 12: federal_supply_category_name
 13: federal_supply_class
 14: federal_supply_class_name
```

--------------------------------

### Fetching Tutorial Data File (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Downloads the tutorial dataset, an Excel file named `ne_1033_data.xlsx`, from a specified URL using the `curl` command. The `-L` flag follows redirects, and `-O` saves the file with its original name. Requires the `curl` command-line tool.

```bash
curl -L -O https://raw.githubusercontent.com/wireservice/csvkit/master/examples/realdata/ne_1033_data.xlsx
```

--------------------------------

### Selecting CSV Columns by Index (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `csvcut` tool with the `-c` option followed by a comma-separated list of 1-based column indices (2, 5, and 6) to select and output only those specific columns from `data.csv`. Requires csvkit to be installed.

```bash
csvcut -c 2,5,6 data.csv
```

--------------------------------

### Install Database Backend with Homebrew Pip

Source: https://github.com/wireservice/csvkit/blob/master/docs/tricks.rst

This command demonstrates how to install a database backend package (like psycopg2) using the specific 'pip' executable associated with the csvkit installation managed by Homebrew. This is necessary if csvkit was installed via Homebrew.

```bash
$(brew --prefix csvkit)/libexec/bin/pip install psycopg2
```

--------------------------------

### Converting Excel to CSV and Saving (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `in2csv` tool to convert the Excel file to CSV and redirects the standard output (`>`) to save the result into a new file named `data.csv`. This is the standard way to save the converted data. Requires csvkit to be installed.

```bash
in2csv ne_1033_data.xlsx > data.csv
```

--------------------------------

### Converting Excel to CSV and Printing (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `in2csv` tool from csvkit to convert the specified Excel file (`ne_1033_data.xlsx`) into CSV format. By default, the output is printed directly to the standard output (terminal). Requires csvkit to be installed.

```bash
in2csv ne_1033_data.xlsx
```

--------------------------------

### Piping csvcut Output to csvlook and head (CSV Input)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Demonstrates chaining csvkit commands and standard Unix tools using pipes. This command first cuts specified columns from 'data.csv', then formats the output using 'csvlook', and finally displays only the first 10 lines using the 'head' command.

```bash
csvcut -c county,item_name,quantity data.csv | csvlook | head
```

--------------------------------

### Selecting CSV Columns by Name (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the `csvcut` tool with the `-c` option followed by a comma-separated list of column names (`county`, `item_name`, `quantity`) to select and output only those specific columns from `data.csv`. This is often more readable than using indices. Requires csvkit to be installed.

```bash
csvcut -c county,item_name,quantity data.csv
```

--------------------------------

### Displaying CSV File Content (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Uses the standard `cat` command to print the entire content of the `data.csv` file to the terminal. This is used to verify that the previous conversion and saving steps were successful. Requires the `cat` command.

```bash
cat data.csv
```

--------------------------------

### Piping in2csv Output to csvcut, csvlook, and head (Excel Input)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Shows how to combine file conversion and data processing using pipes. This command converts an Excel file ('ne_1033_data.xlsx') to CSV using 'in2csv', then cuts specific columns, formats the output with 'csvlook', and limits the display to the first 10 lines with 'head'.

```bash
in2csv ne_1033_data.xlsx | csvcut -c county,item_name,quantity | csvlook | head
```

--------------------------------

### Displaying Wide CSV with Horizontal Scrolling (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/1_getting_started.rst

Pipes the output of `csvlook` to the `less` command with the `-S` option. This allows viewing wide tables without line wrapping, enabling horizontal scrolling with arrow keys to see all columns. Requires csvkit and the `less` command.

```bash
csvlook data.csv | less -S
```

--------------------------------

### Database Backend Installation Error Message

Source: https://github.com/wireservice/csvkit/blob/master/docs/tricks.rst

This is the error message displayed when csvkit cannot find the necessary database backend (like psycopg2 or mysql-connector-python) for the specified connection string. It lists available backends and suggests installation commands.

```none
You don't appear to have the necessary database backend installed for connection string you're trying to use. Available backends include:

PostgreSQL: pip install psycopg2
MySQL:      pip install mysql-connector-python OR pip install mysqlclient

For details on connection strings and other backends, please see the SQLAlchemy documentation on dialects at:

https://www.sqlalchemy.org/docs/dialects/
```

--------------------------------

### Basic csvpy Usage Example

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvpy.rst

Demonstrates the basic command-line usage of `csvpy` to load a CSV file and interact with the resulting reader object in a Python shell. Shows how to access the first row.

```console
$ csvpy examples/dummy.csv
   Welcome! "examples/dummy.csv" has been loaded in a reader object named "reader".
   >>> next(reader)
   ['a', 'b', 'c']
```

--------------------------------

### Analyze CSV Sample with csvsql Bash

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvsql.rst

Provides an example of analyzing only the beginning of a large CSV file using `head` and `csvsql`, disabling length and null constraints with `--no-constraints` for faster processing of the sample.

```bash
head -n 20 examples/realdata/FY09_EDU_Recipients_by_State.csv | csvsql --no-constraints --tables fy09
```

--------------------------------

### Viewing csvclean Error Output File

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Example command showing how to display the contents of the file containing error messages generated by `csvclean`.

```console
$ cat errors.csv
line_number,msg,column_a,column_b,column_c
```

--------------------------------

### Example Output CSV after Joining with Custom Separator

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Demonstrates the effect of using `--join-short-rows` with the `--separator` option to join split lines using a specified string instead of the default newline.

```none
id,address,country
1,"1 Main St, Springfield",US
2,"123 Acadia Avenue, London",GB
```

--------------------------------

### Example Output CSV after Filling Short Rows

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Displays the result of applying the `--fill-short-rows` option to add missing delimiters and empty fields to short rows, ensuring a consistent number of columns.

```none
id,name,country
1,Alice,
2,Bob,CA
```

--------------------------------

### Convert Fixed-Width to CSV (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/in2csv.rst

Example demonstrating how to convert a fixed-width file to CSV using a schema file and specifying input encoding.

```bash
in2csv -e iso-8859-1 -f fixed -s examples/realdata/census_2000/census2000_geo_schema.csv examples/realdata/census_2000/usgeo_excerpt.upl
```

--------------------------------

### Example Input CSV with Missing Delimiters

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Presents a CSV file where some rows are shorter than others due to missing delimiters, leading to incomplete data.

```none
id,name,country
1,Alice
2,Bob,CA
```

--------------------------------

### Example Output CSV after Filling with Custom Value

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Shows the output when using `--fill-short-rows` along with the `--fillvalue` option to populate the added fields in short rows with a specific default value.

```none
id,name,country
1,Alice,US
2,Bob,CA
```

--------------------------------

### Get csvkit version

Source: https://github.com/wireservice/csvkit/blob/master/CONTRIBUTING.rst

Include the version of csvkit you are using in your bug report. This command outputs the installed csvkit version.

```Shell
csvcut --version
```

--------------------------------

### Get Python version

Source: https://github.com/wireservice/csvkit/blob/master/CONTRIBUTING.rst

Include the version of Python you are using in your bug report. This command outputs the installed Python version.

```Shell
python --version
```

--------------------------------

### Connect to MySQL with Charset (sql2csv)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/sql2csv.rst

Provides an example of connecting to a MySQL database using a SQLAlchemy connection string that includes parameters, specifically setting the character encoding, and executing a simple query.

```bash
sql2csv --db 'mysql://user:pass@host/database?charset=utf8' --query "SELECT myfield FROM mytable"
```

--------------------------------

### Example csvkit command with verbose flag

Source: https://github.com/wireservice/csvkit/blob/master/CONTRIBUTING.rst

When reporting a bug, include the specific csvkit command that caused the issue, run with the -v or --verbose flag to provide detailed output for debugging purposes.

```Shell
csvstat -v test.csv
```

--------------------------------

### Example Output CSV after Joining Short Rows

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Shows the result of using the `--join-short-rows` option to merge split rows caused by unquoted line breaks into single, corrected rows.

```none
id,address,country
1,"1 Main St
Springfield",US
2,"123 Acadia Avenue
London",GB
```

--------------------------------

### Convert Excel .xls to CSV (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/in2csv.rst

Simple example showing how to convert an old Excel .xls file to CSV.

```bash
in2csv examples/test.xls
```

--------------------------------

### Checking csvclean Exit Code (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Demonstrates running `csvclean -a`, redirecting its output, and then checking the `$?` variable in the console to get the exit code, which indicates whether errors were found.

```console
$ csvclean -a examples/bad.csv >/dev/null 2>&1
$ echo $?
```

--------------------------------

### Example Input CSV with Unquoted Line Breaks

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Illustrates a common CSV error where unquoted fields contain internal line breaks, causing rows to be split.

```none
id,address,country
1,1 Main St
Springfield,US
2,123 Acadia Avenue
London,GB
```

--------------------------------

### Skipping Header/Comment Lines with csvlook

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvlook.rst

Shows how to use the --skip-lines option to ignore a specified number of lines (1 in this example) at the beginning of the input file, useful for skipping headers or comments before rendering the data.

```bash
csvlook --skip-lines 1 examples/bad.csv
```

--------------------------------

### Convert CSV to JSON Keyed by Column

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvjson.rst

Example demonstrating how to convert a CSV file into a JSON object where each entry is keyed by the value of a specified column ('State Abbreviate') and the output is indented by 4 spaces.

```console
$ csvjson -k "State Abbreviate" -i 4 examples/realdata/FY09_EDU_Recipients_by_State.csv
{
    "AL": {
        "State Name": "ALABAMA",
        "State Abbreviate": "AL",
        "Code": 1.0,
        "Montgomery GI Bill-Active Duty": 6718.0,
        "Montgomery GI Bill- Selective Reserve": 1728.0,
        "Dependents' Educational Assistance": 2703.0,
        "Reserve Educational Assistance Program": 1269.0,
        "Post-Vietnam Era Veteran's Educational Assistance Program": 8.0,
        "TOTAL": 12426.0,
        "j": null
    },
    "...": {
        "...": "..."
    }
}
```

--------------------------------

### Counting csvclean Errors with csvstat (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Explains how to pipe the error output of `csvclean -a` (redirecting standard output to null) into `csvstat --count` to get a numerical count of the errors found.

```console
$ csvclean -a examples/bad.csv 2>&1 >/dev/null | csvstat --count
```

--------------------------------

### Cleaning CSV and Omitting Error Rows

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Example command demonstrating how to use `csvclean` to check for length mismatches, omit erroneous rows from standard output, and redirect error messages to a separate file.

```console
$ csvclean --length-mismatch --omit-error-rows examples/bad.csv 2> errors.csv
column_a,column_b,column_c
0,mixed types.... uh oh,17
```

--------------------------------

### Getting Column Statistics with csvstat (bash/console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/3_power_tools.rst

Uses the csvstat command to display statistical information for the 'state' and 'acquisition_cost' columns in the region.csv file. The -c flag specifies the columns to analyze. The output shows data type, unique values, common values, and numerical statistics.

```console
$ csvstat -c state,acquisition_cost region.csv
  1. "state"

 Type of data:          Text
 Contains null values:  False
 Unique values:         2
 Longest value:         2 characters
 Most common values:    KS (1575x)
                        NE (1036x)

  8. "acquisition_cost"

 Type of data:          Number
 Contains null values:  False
 Unique values:         127
 Smallest value:        0
 Largest value:         658,000
 Sum:                   9,440,445.91
 Mean:                  3,615.644
 Median:                138
 StDev:                 23,730.631
 Most common values:    120 (649x)
                        499 (449x)
                        138 (311x)
                        6,800 (304x)
                        58.71 (218x)

Row count: 2611
```

--------------------------------

### csvpy Usage Help

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvpy.rst

Displays the command-line usage, options, and arguments for the `csvpy` tool, explaining how to load CSV files and interact with the data.

```none
usage: csvpy [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
             [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
             [-S] [--blanks] [--null-value NULL_VALUES [NULL_VALUES ...]]
             [--date-format DATE_FORMAT] [--datetime-format DATETIME_FORMAT]
             [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V] [--dict] [--agate]
             [--no-number-ellipsis] [-y SNIFF_LIMIT] [-I]
             [FILE]

 Load a CSV file into a CSV reader and then drop into a Python shell.

 positional arguments:
   FILE                  The CSV file to operate on. If omitted, will accept
                         input as piped data via STDIN.

 optional arguments:
   -h, --help            show this help message and exit
   --dict                Load the CSV file into a DictReader.
   --agate               Load the CSV file into an agate table.
   --no-number-ellipsis  Disable the ellipsis if the max precision is exceeded.
   -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
                         Limit CSV dialect sniffing to the specified number of
                         bytes. Specify "0" to disable sniffing entirely, or
                         "-1" to sniff the entire file.
   -I, --no-inference    Disable type inference (and --locale, --date-format,
                         --datetime-format, --no-leading-zeroes) when parsing
                         the input.
```

--------------------------------

### Get Column Indices of Matches with csvgrep and csvkit tools

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvgrep.rst

Finds rows containing '22' in any column (1 or later), formats the output using the Record Separator character, and then uses `csvcut -n` to get column names/indices for those rows, finally filtering for '22'. Note: This example is not performant.

```bash
csvgrep -m 22 -a -c 1- examples/realdata/FY09_EDU_Recipients_by_State.csv | csvformat -M $'\x1e' | xargs -d $'\x1e' -n1 sh -c 'echo $0 | csvcut -n' | grep 22
```

--------------------------------

### csvclean Command Line Usage Syntax

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Provides the standard command-line syntax for the `csvclean` tool, listing positional and optional arguments.

```none
usage: csvclean [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
                  [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-S] [-H]
                  [-K SKIP_LINES] [-v] [-l] [--zero] [-V]
                  [FILE]

Fix common errors in a CSV file.

positional arguments:
  FILE                  The CSV file to operate on. If omitted, will accept
                        input as piped data via STDIN.

optional arguments:
  -h, --help            show this help message and exit
  --length-mismatch     Report data rows that are shorter or longer than the
                        header row.
  --empty-columns       Report empty columns as errors.
  -a, --enable-all-checks
                        Enable all error reporting.
  --omit-error-rows     Omit data rows that contain errors, from standard
                        output.
  --label LABEL         Add a "label" column to standard error. Useful in
                        automated workflows. Use "-" to default to the input
                        filename.
  --header-normalize-space
                        Strip leading and trailing whitespace and replace
                        sequences of whitespace characters by a single space
                        in the header.
  --join-short-rows     Merges short rows into a single row.
  --separator SEPARATOR
                        The string with which to join short rows. Defaults to
                        a newline.
  --fill-short-rows     Fill short rows with the missing cells.
  --fillvalue FILLVALUE
                        The value with which to fill short rows. Defaults to
                        none.
```

--------------------------------

### Basic csvlook Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvlook.rst

Demonstrates the simplest way to use csvlook to render a specified CSV file to the console as a fixed-width table.

```bash
csvlook examples/testfixed_converted.csv
```

--------------------------------

### Power Reactor Event - Emergency Diesel Generators Start (Indian Point)

Source: https://github.com/wireservice/csvkit/blob/master/examples/realdata/event-notification-rpt-lastmonth.txt

This notification reports a loss of offsite power from the 138 Kv circuit at Indian Point Unit 2. All three Emergency Diesel Generators automatically started as designed. All other plant systems functioned correctly. The unit is in a 72-hour LCO due to the loss of one offsite circuit. Unit 3 was unaffected.

```Text
Power Reactor|46649|INDIAN POINT|ENTERGY NUCLEAR|1|BUCHANAN|NY|WESTCHESTER||Y|05000247|2|||[2] W-4-LP,[3] W-4-LP|PHIL SANTINI|JOHN KNOKE|3/1/2011 00:00:00|12:54|3/1/2011 00:00:00|11:00|EST|3/1/2011 00:00:00|NON EMERGENCY|50.72(b)(3)(iv)(A)|VALID SPECIF SYS ACTUATION|||||||ANTHONY DIMITRIADIS|R1DO|||||||||||||||||||N|Y|100|Power Operation|100|Power Operation|N|N|0||0||N|N|0||0||
EMERGENCY DIESEL GENERATORS START ON A LOSS OF POWER FROM 138 KV CIRCUIT
"At 1100 EST, Indian Point Unit 2 experienced a loss of offsite power from the 138 Kv circuit. All three Emergency Diesel Generators automatically started as required. All other plant systems functioned as required. Restoration of offsite power from the 13.8 Kv offsite circuit is in progress. Investigation into the loss of the 138 Kv circuit is ongoing. Indian Point, Unit 2 continues in Mode 1 at 100 % power."
Indian Point, Unit 2 is in a 72 hour LCO due to a loss of 1 of 2 offsite circuits. Unit 3 was not affected. The licensee has notified the NRC Resident Inspector and will be notifying the Public Service Commission of the State of New York.|
```

--------------------------------

### csvlook Command Usage Syntax

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvlook.rst

Displays the command-line syntax and available options for the csvlook tool, including arguments for file input, delimiters, quoting, encoding, and display limits such as max rows, columns, and column width.

```none
usage: csvlook [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
                  [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
                  [-S] [--blanks] [--null-value NULL_VALUES [NULL_VALUES ...]]
                  [--date-format DATE_FORMAT] [--datetime-format DATETIME_FORMAT]
                  [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V]
                  [--max-rows MAX_ROWS] [--max-columns MAX_COLUMNS]
                  [--max-column-width MAX_COLUMN_WIDTH]
                  [--max-precision MAX_PRECISION] [--no-number-ellipsis]
                  [-y SNIFF_LIMIT] [-I]
                  [FILE]

   Render a CSV file in the console as a Markdown-compatible, fixed-width table.

   positional arguments:
     FILE                  The CSV file to operate on. If omitted, will accept
                           input as piped data via STDIN.

   optional arguments:
     -h, --help            show this help message and exit
     --max-rows MAX_ROWS   The maximum number of rows to display before
                           truncating the data.
     --max-columns MAX_COLUMNS
                           The maximum number of columns to display before
                           truncating the data.
     --max-column-width MAX_COLUMN_WIDTH
                           Truncate all columns to at most this width. The
                           remainder will be replaced with ellipsis.
     --max-precision MAX_PRECISION
                           The maximum number of decimal places to display. The
                           remainder will be replaced with ellipsis.
     --no-number-ellipsis  Disable the ellipsis if --max-precision is exceeded.
     -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
                           Limit CSV dialect sniffing to the specified number of
                           bytes. Specify "0" to disable sniffing entirely, or
                           "-1" to sniff the entire file.
     -I, --no-inference    Disable type inference (and --locale, --date-format,
                           --datetime-format, --no-leading-zeroes) when parsing
                           the input.
```

--------------------------------

### csvpy Usage with --agate Option

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvpy.rst

Illustrates using the `--agate` option with `csvpy` to load a CSV file into an `agate` table object. Shows how to print the loaded table.

```console
$ csvpy --agate examples/dummy.csv
   Welcome! "examples/dummy.csv" has been loaded in a from_csv object named "reader".
   >>> reader.print_table()
   |    a | b | c |
   | ---- | - | - |
   | True | 2 | 3 |
```

--------------------------------

### Convert JSON API Response to CSV (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/in2csv.rst

Example piping the JSON output from a curl request to the GitHub API into in2csv to convert it to CSV format.

```bash
curl https://api.github.com/repos/wireservice/csvkit/issues?state=open | in2csv -f json -v
```

--------------------------------

### sql2csv Command Line Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/sql2csv.rst

Displays the command-line arguments and options available for the `sql2csv` tool, including how to specify the database connection, query source (file, STDIN, or argument), encoding, and output format options.

```none
usage: sql2csv [-h] [-v] [-l] [-V] [--db CONNECTION_STRING] [--query QUERY]
               [-e ENCODING] [-H]
               [FILE]

Execute a SQL query on a database and output the result to a CSV file.

positional arguments:
  FILE                  The file to use as the SQL query. If FILE and --query
                        are omitted, the query is piped data via STDIN.

optional arguments:
  -h, --help            show this help message and exit
  --db CONNECTION_STRING
                        A SQLAlchemy connection string to connect to a
                        database.
  --engine-option ENGINE_OPTION ENGINE_OPTION
                        A keyword argument to SQLAlchemy's create_engine(), as
                        a space-separated pair. This option can be specified
                        multiple times. For example: thick_mode True
  --execution-option EXECUTION_OPTION EXECUTION_OPTION
                        A keyword argument to SQLAlchemy's
                        execution_options(), as a space-separated pair. This
                        option can be specified multiple times. For example:
                        stream_results True
  --query QUERY         The SQL query to execute. Overrides FILE and STDIN.
  -e ENCODING, --encoding ENCODING
                        Specify the encoding of the input query file.
  -H, --no-header-row   Do not output column names.
```

--------------------------------

### Remove Comment Rows using csvgrep

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvgrep.rst

Filters out rows that start with '#' by inverting a regex match on the first column, demonstrating how to remove comment lines from piped input.

```bash
printf "a,b\n1,2\n# a comment\n3,4" | csvgrep --invert-match -c1 -r '^#'
```

--------------------------------

### Fixed-Width Schema Format

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/in2csv.rst

Defines the required format for the schema file used with the -s option for converting fixed-width files. It specifies column name, start position (zero-based), and length.

```text
column,start,length
name,0,30
birthday,30,10
age,40,3
```

--------------------------------

### Querying CSV with SQL using csvkit (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/index.rst

Demonstrates how to execute a SQL query directly against a CSV file using the csvsql command with the --query flag. The results are redirected to a new CSV file.

```bash
csvsql --query "select name from data where age > 30" data.csv > new.csv
```

--------------------------------

### Convert XLSX and Avoid Unnamed Headers (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/in2csv.rst

Example demonstrating how to convert an XLSX file while preventing in2csv from automatically naming unnamed headers, often used with --no-header-row.

```bash
in2csv --no-header-row examples/test.xlsx | tail -n +2
```

--------------------------------

### csvcut Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvcut.rst

Displays the command-line usage syntax and available options for the csvcut tool, including arguments for specifying input file, delimiter, quote character, encoding, and columns to include or exclude.

```none
usage: csvcut [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
                 [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-S] [-H]
                 [-K SKIP_LINES] [-v] [-l] [--zero] [-V] [-n] [-c COLUMNS]
                 [-C NOT_COLUMNS] [-x]
                 [FILE]

   Filter and truncate CSV files. Like the Unix "cut" command, but for tabular
   data.

   positional arguments:
     FILE                  The CSV file to operate on. If omitted, will accept
                           input as piped data via STDIN.

   optional arguments:
     -h, --help            show this help message and exit
     -n, --names           Display column names and indices from the input CSV
                           and exit.
     -c COLUMNS, --columns COLUMNS
                           A comma-separated list of column indices, names or
                           ranges to be extracted, e.g. "1,id,3-5". Defaults to
                           all columns.
     -C NOT_COLUMNS, --not-columns NOT_COLUMNS
                           A comma-separated list of column indices, names or
                           ranges to be excluded, e.g. "1,id,3-5". Defaults to no
                           columns. Ignores unknown columns.
     -x, --delete-empty-rows
                           After cutting, delete rows which are completely empty.
```

--------------------------------

### csvsql Command Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvsql.rst

This snippet displays the command-line syntax and available options for the csvsql tool, including positional arguments and various flags for database interaction, SQL generation, and data handling.

```none
usage: csvsql [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
              [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
              [-S] [--blanks] [--null-value NULL_VALUES [NULL_VALUES ...]]
              [--date-format DATE_FORMAT] [--datetime-format DATETIME_FORMAT]
              [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V]
              [-i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase}]
              [--db CONNECTION_STRING] [--query QUERIES] [--insert]
              [--prefix PREFIX] [--before-insert BEFORE_INSERT]
              [--after-insert AFTER_INSERT] [--tables TABLE_NAMES]
              [--no-constraints] [--unique-constraint UNIQUE_CONSTRAINT]
              [--no-create] [--create-if-not-exists] [--overwrite]
              [--db-schema DB_SCHEMA] [-y SNIFF_LIMIT] [-I]
              [--chunk-size CHUNK_SIZE]
              [FILE [FILE ...]]

Generate SQL statements for one or more CSV files, or execute those statements
directly on a database, and execute one or more SQL queries.

positional arguments:
  FILE                  The CSV file(s) to operate on. If omitted, will accept
                        input as piped data via STDIN.

optional arguments:
  -h, --help            show this help message and exit
  -i {mssql,mysql,oracle,postgresql,sqlite,duckdb,crate,ingres}, --dialect {mssql,mysql,oracle,postgresql,sqlite,duckdb,crate,ingres}
                        Dialect of SQL to generate. Cannot be used with --db.
  --db CONNECTION_STRING
                        If present, a SQLAlchemy connection string to use to
                        directly execute generated SQL on a database.
  --engine-option ENGINE_OPTION ENGINE_OPTION
                        A keyword argument to SQLAlchemy's create_engine(), as
                        a space-separated pair. This option can be specified
                        multiple times. For example: thick_mode True
  --query QUERIES       Execute one or more SQL queries delimited by --sql-
                        delimiter, and output the result of the last query as
                        CSV. QUERY may be a filename. --query may be specified
                        multiple times.
  --insert              Insert the data into the table. Requires --db.
  --prefix PREFIX       Add an expression following the INSERT keyword, like
                        OR IGNORE or OR REPLACE.
  --before-insert BEFORE_INSERT
                        Before the INSERT command, execute one or more SQL
                        queries delimited by --sql-delimiter. Requires
                        --insert.
  --after-insert AFTER_INSERT
                        After the INSERT command, execute one or more SQL
                        queries delimited by --sql-delimiter. Requires
                        --insert.
  --sql-delimiter SQL_DELIMITER
                        Delimiter separating SQL queries in --query, --before-
                        insert, and --after-insert.
  --tables TABLE_NAMES  A comma-separated list of names of tables to be
                        created. By default, the tables will be named after
                        the filenames without extensions or "stdin".
  --no-constraints      Generate a schema without length limits or null
                        checks. Useful when sampling big tables.
  --unique-constraint UNIQUE_CONSTRAINT
                        A column-separated list of names of columns to include
                        in a UNIQUE constraint.
  --no-create           Skip creating the table. Requires --insert.
  --create-if-not-exists
                        Create the table if it does not exist, otherwise keep
                        going. Requires --insert.
  --overwrite           Drop the table if it already exists. Requires
                        --insert. Cannot be used with --no-create.
  --db-schema DB_SCHEMA
                        Optional name of database schema to create table(s)
                        in.
  -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
                        Limit CSV dialect sniffing to the specified number of
                        bytes. Specify "0" to disable sniffing entirely, or
                        "-1" to sniff the entire file.
  -I, --no-inference    Disable type inference (and --locale, --date-format,
                        --datetime-format, --no-leading-zeroes) when parsing
                        the input.
  --chunk-size CHUNK_SIZE
                        Chunk size for batch insert into the table. Requires
```

--------------------------------

### Launch Python Interactive Session with CSV using csvpy (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/4_going_elsewhere.rst

Illustrates how the `csvpy` command launches a Python interactive terminal with the specified CSV file already loaded into a variable named 'reader'. This allows for quick programmatic exploration and manipulation of the data using Python.

```console
$ csvpy data.csv
Welcome! "data.csv" has been loaded in a reader object named "reader".
>>> print(len(list(reader)))
1037
>>> quit()
```

--------------------------------

### Regenerate Man Pages - Bash

Source: https://github.com/wireservice/csvkit/blob/master/docs/release.rst

This command uses Sphinx to build the man pages from the documentation source files. It is a necessary step in the release process to ensure the man pages are current.

```bash
sphinx-build -b man docs man
```

--------------------------------

### Format CSV with Multiple Custom Options using csvformat (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/4_going_elsewhere.rst

Illustrates the flexibility of `csvformat` by combining multiple options: `-D` for delimiter (&), `-Q` for quote character ($), `-U 2` for quoting all strings, and `-M` for line ending (*). This shows how to tailor output for specific legacy system requirements.

```bash
csvformat -D \& -Q \$ -U 2 -M \* data.csv
```

--------------------------------

### Create Table and Import CSV Data into PostgreSQL with csvsql Bash

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvsql.rst

Illustrates creating a PostgreSQL database and then using `csvsql` with the `--db` and `--insert` options to create a table and load data directly from a CSV file.

```bash
createdb test
csvsql --db postgresql:///test --tables fy09 --insert examples/realdata/FY09_EDU_Recipients_by_State.csv
```

--------------------------------

### Generate Summary Statistics with csvstat

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/2_examining_the_data.rst

This command uses csvcut to select specific columns (county, acquisition_cost, ship_date) from data.csv and pipes the output to csvstat to generate summary statistics for those columns, providing an overview of the data types and distributions.

```console
$ csvcut -c county,acquisition_cost,ship_date data.csv | csvstat
```

--------------------------------

### Checking for csvclean Errors using Bash If (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Provides a bash `if` statement example to check the exit status of `csvclean -a` (analyze mode) to determine if any errors were found in the CSV file.

```console
$ if [ csvclean -a examples/bad.csv ]; then echo "my message"; fi
```

--------------------------------

### csvstack Command Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvstack.rst

Displays the command-line syntax and available options for the csvstack tool. It shows positional arguments for input files and optional arguments for delimiter, quoting, encoding, skipping lines, grouping, and output format.

```none
usage: csvstack [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
 [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-S] [-H]
 [-K SKIP_LINES] [-v] [-l] [--zero] [-V] [-g GROUPS]
 [-n GROUP_NAME] [--filenames]
 [FILE [FILE ...]]

 Stack up the rows from multiple CSV files, optionally adding a grouping value.

 positional arguments:
 FILE The CSV file(s) to operate on. If omitted, will accept
 input as piped data via STDIN.

 optional arguments:
 -h, --help show this help message and exit
 -g GROUPS, --groups GROUPS
 A comma-separated list of values to add as "grouping
 factors", one per CSV being stacked. These are added
 to the output as a new column. You may specify a name
 for the new column using the -n flag.
 -n GROUP_NAME, --group-name GROUP_NAME
 A name for the grouping column, e.g. "year". Only used
 when also specifying -g.
 --filenames Use the filename of each input file as its grouping
 value. When specified, -g will be ignored.
```

--------------------------------

### Analyze Joined Data with csvkit Pipeline (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/3_power_tools.rst

Uses a pipeline of csvkit commands (csvcut, csvsort, csvlook) and head to extract specific columns from the joined data, sort by total population, format as a table, and display the first 10 rows to find the lowest population counties receiving equipment.

```console
$ csvcut -c county,item_name,total_population joined.csv | csvsort -c total_population | csvlook | head
| county     | item_name                                                      | total_population |
| ---------- | -------------------------------------------------------------- | ---------------- |
| MCPHERSON  | RIFLE,5.56 MILLIMETER                                          |              348 |
| WHEELER    | RIFLE,5.56 MILLIMETER                                          |              725 |
| GREELEY    | RIFLE,7.62 MILLIMETER                                          |            2,515 |
| GREELEY    | RIFLE,7.62 MILLIMETER                                          |            2,515 |
| GREELEY    | RIFLE,7.62 MILLIMETER                                          |            2,515 |
| NANCE      | RIFLE,5.56 MILLIMETER                                          |            3,730 |
| NANCE      | RIFLE,7.62 MILLIMETER                                          |            3,730 |
| NANCE      | RIFLE,7.62 MILLIMETER                                          |            3,730 |
```

--------------------------------

### Regenerate Usage Information - Bash

Source: https://github.com/wireservice/csvkit/blob/master/docs/release.rst

This snippet shows how to regenerate the usage information in the documentation. It sets the terminal width to 80 columns, runs the `csvformat -h` command to capture help output, and then restores the terminal settings.

```bash
stty cols 80
csvformat -h
stty sane
```

--------------------------------

### Filter and Convert CSV to JSON using csvcut, csvgrep, and csvjson (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/tutorial/4_going_elsewhere.rst

Demonstrates a pipeline using csvkit tools to select specific columns (`csvcut`), filter rows based on a condition (`csvgrep`), and convert the result to indented JSON format (`csvjson`). This is useful for preparing a subset of data for web display.

```bash
csvcut -c county,item_name data.csv | csvgrep -c county -m "GREELEY" | csvjson --indent 4
```

```json
[
    {
        "county": "GREELEY",
        "item_name": "RIFLE,7.62 MILLIMETER"
    },
    {
        "county": "GREELEY",
        "item_name": "RIFLE,7.62 MILLIMETER"
    },
    {
        "county": "GREELEY",
        "item_name": "RIFLE,7.62 MILLIMETER"
    }
]
```

--------------------------------

### Viewing csvclean Error Output (Console)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvclean.rst

Shows how to use the `cat` command to view the contents of the error file generated by `csvclean`, which includes details about identified issues like empty columns.

```console
$ cat errors.csv
```

--------------------------------

### Concatenate Columns with csvsql Bash

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvsql.rst

Provides an example of using SQL's concatenation operator (`||`) within `csvsql`'s `--query` option to combine the values of two columns, disabling schema inference with `--no-inference`.

```bash
csvsql --query "SELECT a || b FROM 'dummy3'" --no-inference examples/dummy3.csv
```

--------------------------------

### Generating CSV Summary Statistics with csvkit (Bash)

Source: https://github.com/wireservice/csvkit/blob/master/docs/index.rst

Illustrates how to generate summary statistics (like count, min, max, mean, etc.) for a CSV file using the csvstat command.

```bash
csvstat data.csv
```

--------------------------------

### Set Python Standard Output Encoding

Source: https://github.com/wireservice/csvkit/blob/master/docs/tricks.rst

This command shows how to set the PYTHONIOENCODING environment variable to 'utf8' when running a csvkit command (like csvlook). This can resolve 'ascii' codec encoding errors when piping output to tools like 'less'.

```bash
env PYTHONIOENCODING=utf8 csvlook dummy.csv | less
```

--------------------------------

### Load Data and Query with sql2csv (PostgreSQL)

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/sql2csv.rst

Shows how to create a PostgreSQL database, load data from a CSV file into a table using `csvsql`, and then execute a specific SQL query with `sql2csv` to filter and limit results.

```bash
createdb recipients
csvsql --db "postgresql:///recipients" --tables "fy09" --insert examples/realdata/FY09_EDU_Recipients_by_State.csv
sql2csv --db "postgresql:///recipients" --query "select * from fy09 where \"State Name\" != '' order by fy09.\"TOTAL\" limit 3"
```

--------------------------------

### Convert CSV to GeoJSON

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvjson.rst

Example showing how to convert a CSV file containing latitude and longitude columns into a GeoJSON FeatureCollection. It specifies the columns for latitude ('latitude') and longitude ('longitude'), uses the 'slug' column as the Feature ID, includes a Coordinate Reference System (CRS), and indents the output by 4 spaces.

```console
$ csvjson --lat latitude --lon longitude --k slug --crs EPSG:4269 -i 4 examples/test_geo.csv
{
    "type": "FeatureCollection", 
    "bbox": [
        -95.334619, 
        32.299076986939205, 
        -95.250699, 
        32.351434
    ], 
    "crs": {
        "type": "name", 
        "properties": {
            "name": "EPSG:4269"
        }
    }, 
    "features": [
        {
            "type": "Feature", 
            "id": "dcl", 
            "geometry": {
                "type": "Point", 
                "coordinates": [
                    -95.30181, 
                    32.35066
                ]
            }, 
            "properties": {
                "title": "Downtown Coffee Lounge", 
                "artist": null, 
                "description": "In addition to being the only coffee shop in downtown Tyler, DCL also features regular exhibitions of work by local artists.", 
                "install_date": null, 
                "address": "200 West Erwin Street", 
                "type": "Gallery", 

```

--------------------------------

### csvjoin Command Usage

Source: https://github.com/wireservice/csvkit/blob/master/docs/scripts/csvjoin.rst

Displays the command-line syntax and available options for the `csvjoin` tool, including arguments for file inputs, join columns, join types, and various CSV parsing options.

```none
usage: csvjoin [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
                  [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
                  [-S] [--blanks] [--null-value NULL_VALUES [NULL_VALUES ...]]
                  [--date-format DATE_FORMAT] [--datetime-format DATETIME_FORMAT]
                  [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V] [-c COLUMNS]
                  [--outer] [--left] [--right] [-y SNIFF_LIMIT] [-I]
                  [FILE [FILE ...]]

   Execute a SQL-like join to merge CSV files on a specified column or columns.

   positional arguments:
     FILE                  The CSV files to operate on. If only one is specified,
                           it will be copied to STDOUT.

   optional arguments:
     -h, --help            show this help message and exit
     -c COLUMNS, --columns COLUMNS
                           The column name(s) on which to join. Should be either
                           one name (or index) or a comma-separated list with one
                           name (or index) per file, in the same order in which
                           the files were specified. If not specified, the two
                           files will be joined sequentially without matching.
     --outer               Perform a full outer join, rather than the default
                           inner join.
     --left                Perform a left outer join, rather than the default
                           inner join. If more than two files are provided this
                           will be executed as a sequence of left outer joins,
                           starting at the left.
     --right               Perform a right outer join, rather than the default
                           inner join. If more than two files are provided this
                           will be executed as a sequence of right outer joins,
                           starting at the right.
     -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
                           Limit CSV dialect sniffing to the specified number of
                           bytes. Specify "0" to disable sniffing entirely, or
                           "-1" to sniff the entire file.
     -I, --no-inference    Disable type inference (and --locale, --date-format,
                           --datetime-format, --no-leading-zeroes) when parsing
                           the input.
```
Tokens:
Raw