### 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. ```