csvkit Documentation

File: docs/scripts/csvclean.rst

======== csvclean

Description

Reports and fixes common errors in a CSV file.

Checks

Reports rows that have a different number of columns than the header row, if the :code:--length-mismatch option is set.
Reports columns that are empty, if the :code:--empty-columns option is set.

.. tip::

Enable all checks with :code:--enable-all-checks (:code:-a).

Fixes

If a CSV has unquoted cells that contain line breaks, like:

.. code-block:: none

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

Use :code:--join-short-rows to attempt to correct the errors by merging short rows into a single row:

.. code-block:: none

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

To change the string used to join the lines, use :code:--separator. For example, with :code:--separator ", ":

.. code-block:: none

id,address,country
1,"1 Main St, Springfield",US
2,"123 Acadia Avenue, London",GB
If a CSV has missing delimiters, like:

.. code-block:: none

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

You can add the missing delimiters with :code:--fill-short-rows:

.. code-block:: none

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

.. tip::

:doc:csvcut without options also adds missing delimiters!

To change the value used to fill short rows, use :code:--fillvalue. For example, with :code:--fillvalue "US":

.. code-block:: none

id,name,country
1,Alice,US
2,Bob,CA
If a CSV has empty columns, like:

.. code-block:: none

id,name,extra
1,Alice,
2,Bob,

You can drop them with :code:--remove-empty-columns:

.. code-block:: none

id,name
1,Alice
2,Bob

.. seealso::

:code:--header-normalize-space under :ref:csvclean-usage.

.. note::

Every csvkit tool does the following:

Removes optional quote characters, unless the --quoting (-u) option is set to change this behavior
Changes the field delimiter to a comma, if the input delimiter is set with the --delimiter (-d) or --tabs (-t) options
Changes the record delimiter to a line feed (LF or \n)
Changes the quote character to a double-quotation mark, if the character is set with the --quotechar (-q) option
Changes the character encoding to UTF-8, if the input encoding is set with the --encoding (-e) option

Output

:code:csvclean attempts to make the selected fixes. Then:

If the :code:--omit-error-rows option is set, only rows that pass the selected checks are written to standard output. If not, all rows are written to standard output.
If any checks are enabled, error rows along with line numbers and descriptions are written to standard error. If there are error rows, the exit code is 1.

.. _csvclean-usage:

Usage

.. code-block:: 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.
--remove-empty-columns
Remove empty columns from standard output.

See also: :doc:../common_arguments.

Examples

Process a file with data rows that are shorter and longer than the header row, and omit those rows:

.. code-block:: 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

.. code-block:: console

$ cat errors.csv
line_number,msg,column_a,column_b,column_c
1,"Expected 3 columns, found 4 columns",1,27,,I'm too long!
2,"Expected 3 columns, found 2 columns",,I'm too short!

.. note::

If any data rows are longer than the header row, you need to add columns manually: for example, by adding one or more delimiters (,) to the end of the header row. :code:csvclean can't do this, because it is designed to work with standard input, and correcting an error at the start of the CSV data based on an observation later in the CSV data would require holding all the CSV data in memory – which is not an option for large files.

Process a file with empty columns:

.. code-block:: console

$ csvclean --empty-columns examples/test_empty_columns.csv 2> errors.csv
a,b,c,,
a,,,,
,,c,,
,,,,

.. code-block:: console
:emphasize-lines: 3

$ cat errors.csv
line_number,msg,a,b,c,,
1,"Empty columns named 'b', '', ''! Try: csvcut -C 2,4,5",,,,,

Then, use :doc:csvcut to exclude the empty columns:

.. code-block:: console

$ csvcut -C 2,4,5 examples/test_empty_columns.csv
a,c
a,
,c
,

Check whether any errors found:

.. code-block:: console

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

Or:

.. code-block:: console

$ [ csvclean -a examples/bad.csv ] && echo "my message"
my message

Or:

.. code-block:: console

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

Count the number of errors found:

.. code-block:: console

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

View only the errors found:

.. code-block:: console

$ csvclean -a examples/bad.csv 2>&1 >/dev/null
line_number,msg,column_a,column_b,column_c
1,"Expected 3 columns, found 4 columns",1,27,,I'm too long!
2,"Expected 3 columns, found 2 columns",,I'm too short!

To change the line ending from line feed (LF or \n) to carriage return and line feed (CRLF or \r\n) use:

.. code-block:: bash

csvformat -M $'\r\n' examples/dummy.csv

File: docs/scripts/csvcut.rst

====== csvcut

Description

Filters and truncates CSV files. Like the Unix "cut" command, but for tabular data:

.. code-block:: 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.
--ignore-unknown-columns
Ignore identifiers in --columns (-c) that do not match
a column in the input, instead of erroring.

See also: :doc:../common_arguments.

.. note::

text

csvcut does not implement row filtering, for this you should pipe data to :doc:`csvgrep`.

.. note::

text

If a data row is longer than the header row, its additional columns are truncated.

Examples

Print columns

Print the indices and names of all columns:

.. code-block:: console

$ csvcut -n examples/realdata/FY09_EDU_Recipients_by_State.csv
1: State Name
2: State Abbreviate
3: Code
4: Montgomery GI Bill-Active Duty
5: Montgomery GI Bill- Selective Reserve
6: Dependents' Educational Assistance
7: Reserve Educational Assistance Program
8: Post-Vietnam Era Veteran's Educational Assistance Program
9: TOTAL
10:

Print only the names of all columns, by removing the indices with the :code:cut command:

.. code-block:: console

$ csvcut -n examples/realdata/FY09_EDU_Recipients_by_State.csv | cut -c6-
State Name
State Abbreviate
Code
Montgomery GI Bill-Active Duty
Montgomery GI Bill- Selective Reserve
Dependents' Educational Assistance
Reserve Educational Assistance Program
Post-Vietnam Era Veteran's Educational Assistance Program
TOTAL

Extract columns

Extract the first and third columns:

.. code-block:: bash

csvcut -c 1,3 examples/realdata/FY09_EDU_Recipients_by_State.csv

Extract columns named "TOTAL" and "State Name" (in that order):

.. code-block:: bash

csvcut -c TOTAL,"State Name" examples/realdata/FY09_EDU_Recipients_by_State.csv

Extract a column that may not exist in all files:

.. code-block:: bash

echo d, | csvjoin examples/dummy.csv - | csvcut -c d
echo d, | csvjoin examples/join_no_header_row.csv - | csvcut -c d

Other

Add line numbers to a file, making no other changes:

.. code-block:: bash

csvcut -l examples/realdata/FY09_EDU_Recipients_by_State.csv

Display a column's unique values:

.. code-block:: bash

csvcut -c 1 examples/realdata/FY09_EDU_Recipients_by_State.csv | sed 1d | sort | uniq

Or:

.. code-block:: bash

csvcut -c 1 examples/realdata/FY09_EDU_Recipients_by_State.csv | csvsql --query 'SELECT DISTINCT("State Name") FROM stdin'

File: docs/scripts/csvformat.rst

========= csvformat

Description

Convert a CSV file to a custom output format.:

.. code-block:: none

usage: csvformat [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
[-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING]
[-L LOCALE] [-S] [-H] [-K SKIP_LINES] [-v] [-l] [--zero] [-V]
[-E] [-D OUT_DELIMITER] [-T] [-A] [-Q OUT_QUOTECHAR]
[-U {0,1,2,3}] [-B] [-P OUT_ESCAPECHAR]
[-M OUT_LINETERMINATOR]
[FILE]

Convert a CSV file to a custom output format.

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
-E, --skip-header Do not output a header row.
-D OUT_DELIMITER, --out-delimiter OUT_DELIMITER
Delimiting character of the output file.
-T, --out-tabs Specify that the output file is delimited with tabs.
Overrides "-D".
-A, --out-asv Specify that the output file is delimited with the
ASCII unit separator and record separator. Overrides
"-T", "-D" and "-M".
-Q OUT_QUOTECHAR, --out-quotechar OUT_QUOTECHAR
Character used to quote strings in the output file.
-U {0,1,2,3}, --out-quoting {0,1,2,3}
Quoting style used in the output file: 0 quote
minimal, 1 quote all, 2 quote non-numeric, 3 quote
none.
-B, --out-no-doublequote
Whether or not double quotes are doubled in the output
file.
-P OUT_ESCAPECHAR, --out-escapechar OUT_ESCAPECHAR
Character used to escape the delimiter in the output
file if --quoting 3 ("Quote None") is specified and to
escape the QUOTECHAR if --out-no-doublequote is
specified.
-M OUT_LINETERMINATOR, --out-lineterminator OUT_LINETERMINATOR
Character used to terminate lines in the output file.

See also: :doc:../common_arguments.

Examples

Convert a comma-separated file to a pipe-delimited file:

.. code-block:: bash

csvformat -D "|" examples/dummy.csv

Convert to carriage return line-endings:

.. code-block:: bash

csvformat -M $'\r' examples/dummy.csv

Convert to a tab-delimited file (TSV) with no doubling of double quotes:

.. code-block:: bash

printf 'key,value\n1,"a ""quoted"" string"' | csvformat -T -Q🐍

To avoid escaping quote characters when using :code:--quoting 3, add :code:--out-quotechar "":

.. code-block:: bash

csvformat -u3 -U3 -Q🐍 examples/optional_quote_characters.csv

File: docs/scripts/csvgrep.rst

======= csvgrep

Description

Filter tabular data to only those rows where certain columns contain a given value or match a regular expression:

.. code-block:: none

usage: csvgrep [-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]
[-m PATTERN] [-r REGEX] [-f MATCHFILE] [-i] [-a]
[FILE]

Search CSV files. Like the Unix "grep" 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 searched, e.g. "1,id,3-5".
-m PATTERN, --match PATTERN
A string to search for.
-r REGEX, --regex REGEX
A regular expression to match.
-f MATCHFILE, --file MATCHFILE
A path to a file. For each row, if any line in the
file (stripped of line separators) is an exact match
of the cell value, the row matches.
-i, --invert-match Select non-matching rows, instead of matching rows.
-a, --any-match Select rows in which any column matches, instead of
all columns.

See also: :doc:../common_arguments.

NOTE: Even though '-m', '-r', and '-f' are listed as "optional" arguments, you must specify one of them.

Examples

Search for the row relating to Illinois:

.. code-block:: bash

csvgrep -c 1 -m ILLINOIS examples/realdata/FY09_EDU_Recipients_by_State.csv

Search for rows relating to states with names beginning with the letter "I":

.. code-block:: bash

csvgrep -c 1 -r "^I" examples/realdata/FY09_EDU_Recipients_by_State.csv

Search for rows that do not contain an empty state cell:

.. code-block:: bash

csvgrep -c 1 -r "^$" -i examples/realdata/FY09_EDU_Recipients_by_State.csv

Perform a case-insensitive search:

.. code-block:: bash

csvgrep -c 1 -r "(?i)illinois" examples/realdata/FY09_EDU_Recipients_by_State.csv

Remove comment rows:

.. code-block:: bash

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

Get the indices of the columns that contain matching text (\x1e is the Record Separator (RS) character <https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_separators>_):

.. code-block::

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

.. note::

This last example is not performant.

File: docs/scripts/csvjoin.rst

======= csvjoin

Description

Merges two or more CSV tables together using a method analogous to SQL JOIN operation. By default it performs an inner join, but full outer, left outer, and right outer are also available via flags. Key columns are specified with the -c flag (either a single column which exists in all tables, or a comma-separated list of columns with one corresponding to each). If the columns flag is not provided then the tables will be merged "sequentially", that is they will be merged in row order with no filtering:

.. code-block:: 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.

Note that the join operation requires reading all files into memory. Don't try
this on very large files.

See also: :doc:../common_arguments.

Examples

.. code-block:: bash

csvjoin -c 1 examples/join_a.csv examples/join_b.csv

Add two empty columns to the right of a CSV:

.. code-block:: bash

echo "," | csvjoin examples/dummy.csv -

Add a single column to the right of a CSV:

.. code-block:: bash

echo "new-column" | csvjoin examples/dummy.csv -

File: docs/scripts/csvjson.rst

======= csvjson

Description

Converts a CSV file into JSON or GeoJSON (depending on flags):

.. code-block:: none

usage: csvjson [-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 INDENT]
[-k KEY] [--lat LAT] [--lon LON] [--type TYPE]
[--geometry GEOMETRY] [--crs CRS] [--no-bbox] [--stream]
[-y SNIFF_LIMIT] [-I]
[FILE]

Convert a CSV file into JSON (or GeoJSON).

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
-i INDENT, --indent INDENT
Indent the output JSON this many spaces. Disabled by
default.
-k KEY, --key KEY Output JSON as an object keyed by a given column, KEY,
rather than as an array. All column values must be
unique. If --lat and --lon are specified, this column
is used as the GeoJSON Feature ID.
--lat LAT A column index or name containing a latitude. Output
will be GeoJSON instead of JSON. Requires --lon.
--lon LON A column index or name containing a longitude. Output
will be GeoJSON instead of JSON. Requires --lat.
--type TYPE A column index or name containing a GeoJSON type.
Output will be GeoJSON instead of JSON. Requires --lat
and --lon.
--geometry GEOMETRY A column index or name containing a GeoJSON geometry.
Output will be GeoJSON instead of JSON. Requires --lat
and --lon.
--crs CRS A coordinate reference system string to be included
with GeoJSON output. Requires --lat and --lon.
--no-bbox Disable the calculation of a bounding box.
--stream Output JSON as a stream of newline-separated objects,
rather than an as an array.
-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.

See also: :doc:../common_arguments.

Examples

Convert veteran's education dataset to JSON keyed by state abbreviation:

.. code-block:: 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
},
"...": {
"...": "..."
}
}

Convert locations of public art into GeoJSON:

.. code-block:: 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",
"photo_url": null,
"photo_credit": null,
"last_seen_date": "2012-03-30"
}
},
{
"...": "..."
}
],
"crs": {
"type": "name",
"properties": {
"name": "EPSG:4269"
}
}
}

File: docs/scripts/csvlook.rst

======= csvlook

Description

Renders a CSV to the command line in a Markdown-compatible, fixed-width format:

.. code-block:: 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.

If a table is too wide to display properly try piping the output to less -S or truncating it using :doc:csvcut.

If the table is too long, try filtering it down with grep or piping the output to less.

See also: :doc:../common_arguments.

.. note::

The fractional part of a decimal numberal is always truncated. To control this truncation, use --no-inference along with --max-column-width.

Examples

Basic use:

.. code-block:: bash

csvlook examples/testfixed_converted.csv

This tool is especially useful as a final operation when piping through other tools:

.. code-block:: bash

csvcut -c 9,1 examples/realdata/FY09_EDU_Recipients_by_State.csv | csvlook

If a data row contains more cells than the header row, csvlook will error. Use :doc:csvclean to remove such rows.

To ignore the extra cells, instead:

.. code-block:: bash

csvcut -C "" examples/bad.csv | csvlook

If these rows are at the top of the file (for example, copyright notices), you can skip the rows:

.. code-block:: bash

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

This error can also occur if csvlook incorrectly deduces ("sniffs") the CSV format. To disable CSV sniffing, set :code:--snifflimit 0 and then, if necessary, set the :code:--delimiter and :code:--quotechar options yourself. Or, set :code:--snifflimit -1 to use the entire file as the sample, instead of the first 1024 bytes.

File: docs/scripts/csvpy.rst

===== csvpy

Description

Loads a CSV file into a :class:agate.csv.Reader object and then drops into a Python shell so the user can inspect the data however they see fit:

.. code-block:: 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.

This tool will automatically use the IPython shell if it is installed, otherwise it will use the running Python shell.

.. note::

text

Due to platform limitations, csvpy does not accept file input as piped data via STDIN.

See also: :doc:../common_arguments.

Examples

Basic use:

.. code-block:: console

$ csvpy examples/dummy.csv
Welcome! "examples/dummy.csv" has been loaded in a reader object named "reader".

next(reader)
['a', 'b', 'c']

As a dictionary:

.. code-block:: console

$ csvpy --dict examples/dummy.csv
Welcome! "examples/dummy.csv" has been loaded in a DictReader object named "reader".

next(reader)
{'a': '1', 'c': '3', 'b': '2'}

As an agate table:

.. code-block:: 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

a	b	c
True	2	3

File: docs/scripts/csvsort.rst

======= csvsort

Description

Sort CSV files. Like the Unix "sort" command, but for tabular data:

.. code-block:: none

usage: csvsort [-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] [-n] [-c COLUMNS]
[-r] [-i] [-y SNIFF_LIMIT] [-I]
[FILE]

Sort CSV files. Like the Unix "sort" 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 sort by, e.g. "1,id,3-5". Defaults to all
columns.
-r, --reverse Sort in descending order.
-i, --ignore-case Perform case-independent sorting.
-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.

See also: :doc:../common_arguments.

.. note::

text

If your file is large, try :code:`sort -t, file.csv` instead.

Examples

Sort the veteran's education benefits table by the "TOTAL" column:

.. code-block:: bash

csvsort -c 9 examples/realdata/FY09_EDU_Recipients_by_State.csv

View the five states with the most individuals claiming veteran's education benefits:

.. code-block:: bash

csvcut -c 1,9 examples/realdata/FY09_EDU_Recipients_by_State.csv | csvsort -r -c 2 | head -n 5

File: docs/scripts/csvclean.rst Copy Chapter

======== csvclean

Description

Checks Copy Chapter

Fixes Copy Chapter

Output Copy Chapter

Usage Copy Chapter

Examples

File: docs/scripts/csvcut.rst Copy Chapter

====== csvcut

Description

Examples

Print columns Copy Chapter

Extract columns Copy Chapter

Other Copy Chapter

File: docs/scripts/csvformat.rst Copy Chapter

========= csvformat

Description

Examples

File: docs/scripts/csvgrep.rst Copy Chapter

======= csvgrep

Description

Examples

File: docs/scripts/csvjoin.rst Copy Chapter

======= csvjoin

Description

Examples

File: docs/scripts/csvjson.rst Copy Chapter

======= csvjson

Description

Examples

File: docs/scripts/csvlook.rst Copy Chapter

======= csvlook

Description

Examples

File: docs/scripts/csvpy.rst Copy Chapter

===== csvpy

Description

Examples

File: docs/scripts/csvsort.rst Copy Chapter

======= csvsort

Description

Examples

Explore Related Technologies & Topics

File: docs/scripts/csvclean.rst

Checks

Fixes

Output

Usage

File: docs/scripts/csvcut.rst

Print columns

Extract columns

Other

File: docs/scripts/csvformat.rst

File: docs/scripts/csvgrep.rst

File: docs/scripts/csvjoin.rst

File: docs/scripts/csvjson.rst

File: docs/scripts/csvlook.rst

File: docs/scripts/csvpy.rst

File: docs/scripts/csvsort.rst