Getting started with 123table

123table is distributed as a container image, so the easiest way to use it is through docker or podman.

Concepts

123table offers a command line interface, so that it can be launched from a terminal or a script.

The simplest run of 123table does one simple thing: inserts into the target db table every row it reads from the source table.

123table can use any data source as the db, provided that you have the appropriate JDBC driver.

A special data source is CSV, because the CSV JDBC driver can read any CSV file as a db table.

See Drivers for more details about JDBC drivers.

Examples

The next sections show some examples mainly based on sqlite and CSV since this way you can test them without the need of a DBMS server.

Copy CSV contents to a new table

Given a CSV file named foo.csv in the current directory, run

docker run --rm -it \
  -v $(pwd):/data ghcr.io/davidecavestro/123table:fast-latest \
  -stable foo \
  -create \
  -url jdbc:sqlite:/data/foo.db

to load its rows into a newly created foo table of a sqlite db.
Replace the -url value with the proper JDBC url for your target db.
Use the --help flag to get the full list of options.

Copy CSV rows to a table transforming values

The following command loads a CSV capitalizing both the firstname and surname fields

docker run --rm -it \
  -v $(pwd):/data ghcr.io/davidecavestro/123table:fast-latest \
  -stable foo \
  -url jdbc:sqlite:/data/foo.db \
  --mapper '[{ "name": "firstname", "expr": "orig.capitalize()" }, \
    { "name": "surname", "expr": "orig.capitalize()" }]'

Consider using a file for complex mappings. See Mapping fields for more details.

Copy a subset of CSV rows to a table

The following command loads the subset of CSV rows having the type field set to ‘gum’

docker run --rm -it \
  -v $(pwd):/data ghcr.io/davidecavestro/123table:fast-latest \
  -url jdbc:sqlite:/data/foo.db \
  -query "SELECT * FROM foo WHERE type='gum'"

Please note it explicitly pass the query for reading rows from the CSV file. This implies referring to the CSV as if it were a table.

CHeck the CLI syntax for more details.

Truncate a table then add rows from another db

The following command truncates the bar table on target db, then loads the rows of the foo table from source db

docker run --rm -it \
  -v $(pwd):/data ghcr.io/davidecavestro/123table:fast-latest \
  -surl jdbc:sqlite:/data/source.db \
  -stable foo \
  -url jdbc:sqlite:/data/target.db \
  -table bar \
  -trunc