CSVSQL(1)

📦
version 2.2.0 · 7 versions
, in arch rolling, nixos 26.05, nixpkgs unstable, ubuntu resolute

💅 NAME 💅

csvsql — the csvsql documentation, bestie. That's it, that's the name. Iconic. 🎀

🌸 DESCRIPTION 🌸

Okay so here's the tea ☕: csvsql generates SQL statements for a CSV file, or it executes those statements directly on a database. And in that second case? It supports both creating tables and inserting data. Both. At once. Main character energy fr fr. 👑

Here's the whole usage situation, verbatim, no cap:

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
                        --insert.
  --min-col-len MIN_COL_LEN
                        The minimum length of text columns.
  --col-len-multiplier COL_LEN_MULTIPLIER
                        Multiply the maximum column length by this multiplier
                        to accomodate larger values in later runs.

✨ OPTIONS ✨

Sit down, hydrate, we're going through every single one 🫶

  • -h, --help — pops the help message and dips. classic.
  • -i {mssql,mysql,oracle,postgresql,sqlite,duckdb,crate,ingres}, --dialect {mssql,mysql,oracle,postgresql,sqlite,duckdb,crate,ingres} — which dialect of SQL you want generated. 🪄 heads up: this one cannot be used with --db.
  • --db CONNECTION_STRING — if you put it in, it's a SQLAlchemy connection string, and it means run the generated SQL directly on a database. yes, directly. 💅
  • --engine-option ENGINE_OPTION ENGINE_OPTION — a keyword argument for SQLAlchemy's create_engine(), given as a space-separated pair. You can drop this option multiple times. example: thick_mode True
  • --query QUERIES — run one or more SQL queries delimited by --sql-delimiter, then spit out the result of the last query as CSV. QUERY can be a filename, and --query can be specified multiple times. 🌈
  • --insert — actually insert the data into the table. needs --db bestie, don't forget.
  • --prefix PREFIX — adds an expression right after the INSERT keyword, like OR IGNORE or OR REPLACE.
  • --before-insert BEFORE_INSERT — before the INSERT command runs, execute one or more SQL queries delimited by --sql-delimiter. requires --insert.
  • --after-insert AFTER_INSERT — after the INSERT command runs, execute one or more SQL queries delimited by --sql-delimiter. requires --insert.
  • --sql-delimiter SQL_DELIMITER — the delimiter separating SQL queries inside --query, --before-insert, and --after-insert. 🎀
  • --tables TABLE_NAMES — a comma-separated list of names for the tables you're creating. by default, tables get named after the filenames without extensions, or "stdin".
  • --no-constraints — generate a schema with no length limits and no null checks. super useful when you're sampling big tables. 👑
  • --unique-constraint UNIQUE_CONSTRAINT — a column-separated list of column names to include in a UNIQUE constraint.
  • --no-create — skip creating the table. requires --insert.
  • --create-if-not-exists — create the table only if it doesn't already exist, otherwise keep it moving. 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 the database schema to create table(s) in.
  • -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT — limit CSV dialect sniffing to that many bytes. say "0" to turn sniffing all the way off, or "-1" to sniff the entire file. 🦋
  • -I, --no-inference — disable type inference when parsing the input (and that means --locale, --date-format, --datetime-format, --no-leading-zeroes too).
  • --chunk-size CHUNK_SIZE — chunk size for batch insert into the table. requires --insert.
  • --min-col-len MIN_COL_LEN — the minimum length of text columns.
  • --col-len-multiplier COL_LEN_MULTIPLIER — multiply the maximum column length by this multiplier, so you can accomodate larger values in later runs. 📏

See also: Arguments common to all tools.

For the whole rundown on connection strings and which dialects are supported, go check the SQLAlchemy documentation. 💜

And if you'd rather not type your password into the connection string (valid, honestly), stash it securely in a PostgreSQL Password File, a MySQL Options File, or similar files for other systems. 🔐

NOTE: using the --query option may cause rounding (in Python 2) or introduce Python floating point issues (in Python 3). 💀
NOTE: if the CSV file was created from a JSON file using in2csv, remember to quote SQL columns properly. for example: echo '{"a":{"b":"c"},"d":"e"}' | in2csv -f ndjson | csvsql --query 'SELECT "a/b" FROM stdin'
NOTE: alternatives to csvsql are q and textql.

🌟 EXAMPLES 🌟

💖 Generate SQL statements

Generate a statement in the PostgreSQL dialect:

csvsql -i postgresql examples/realdata/FY09_EDU_Recipients_by_State.csv

🦋 Interact with a SQL database

Create a table and import data from the CSV directly into PostgreSQL:

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

For large tables it may not be practical to process the entire table. One solution to this is to analyze a sample of the table. In this case it can be useful to turn off length limits and null checks with the --no-constraints option:

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

Create tables for an entire directory of CSVs and import data from those files directly into PostgreSQL:

createdb test
csvsql --db postgresql:///test --insert examples/*_converted.csv

If those CSVs have identical headers, you can import them into the same table by using csvstack first:

createdb test
csvstack examples/dummy?.csv | csvsql --db postgresql:///test --insert

🌸 Query and output CSV files using SQL

You can use csvsql to "directly" query one or more CSV files. Please note that this will create an in-memory SQLite database, so it won't be very fast:

csvsql --query  "SELECT m.usda_id, avg(i.sepal_length) AS mean_sepal_length FROM iris AS i JOIN irismeta AS m ON (i.species = m.species) GROUP BY m.species" examples/iris.csv examples/irismeta.csv

Group rows by one column:

csvsql --query "SELECT * FROM 'dummy3' GROUP BY a" examples/dummy3.csv

Concatenate two columns:

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

If a column contains null values, you must COALESCE the column:

csvsql --query "SELECT a || COALESCE(b, '') FROM 'sort_ints_nulls'" --no-inference examples/sort_ints_nulls.csv

The UPDATE SQL statement produces no output. Remember to SELECT the columns and rows you want:

csvsql --query "UPDATE 'dummy3' SET a = 'foo'; SELECT * FROM 'dummy3'" examples/dummy3.csv

👑 AUTHOR 👑

Christopher Groskopf and contributors. legends, every one of them. 🫶

2016, Christopher Groskopf and James McKinney

and that's the whole page bestie!! this was csvkit 2.2.0, dated Aug 16, 2024, filed under CSVSQL(1) — go forth and query, you absolute icon 💖✨🎀🦋🌸💅

📦 where this page comes from