transmute

Convert CSV to SQL INSERT Statements

You have a CSV export — users, products, sensor readings — and you need it into a database. Most answers online are one-off Python scripts or websites that choke on files with more than a few thousand rows. Here's the shortest reliable path, straight from the terminal.

Option 1: The Transmute CLI (free, works offline)

Given users.csv:

id,name,email
1,Alice,[email protected]
2,O'Brien,[email protected]
$ transmute users.csv --output sql --table users
-- Generated by Transmute
INSERT INTO "users" ("id", "name", "email") VALUES
  (1, 'Alice', '[email protected]'),
  (2, 'O''Brien', '[email protected]');

It handles the details that break naive scripts:

  • Quotes are escaped correctly — O'Brien becomes O''Brien, not a syntax error.
  • Numbers stay numbers — 42 inserts as 42, not '42'.
  • Empty cells become NULL, not empty strings.
  • Pipe it through filters first — clean your data on the way in:
$ transmute users.csv     --pipe '[{"op":"filter","expr":"item.email"},{"op":"unique","by":"email"}]'     --output sql --table users

The same --output sql flag works from JSON, YAML or XML input too — any format Transmute reads can become INSERT statements.

PostgreSQL: faster alternative for big files

For very large CSVs (100k+ rows) in Postgres, COPY beats row-by-row INSERTs:

=# COPY users (id, name, email) FROM '/path/users.csv' CSV HEADER;

But COPY needs server-side file access and exact column order. INSERT statements work everywhere — including hosted databases where you only have a SQL editor.

SQLite and MySQL notes

  • SQLite: save the output to a file and run sqlite3 mydb.db < inserts.sql. The generated statements use double-quoted identifiers, which SQLite accepts.
  • MySQL: works as-is. If your session has ANSI_QUOTES off and the table name is plain (no reserved words), you can strip the quotes around identifiers without risk.

Bonus: seed data from JSON

Fixtures and API exports convert the same way — handy for test databases:

$ cat fixtures.json | transmute --format json --output sql --table products