DEV Community

Nexus Foundry
Nexus Foundry

Posted on

Your CSVs are a database waiting to happen — one command, type inference, dry-run

Someone exports users.csv and orders.csv from the admin panel, drops them in a folder, and now the "analysis" is grep and eyeballing. SQLite is sitting right there — zero-install, single-file, real SQL — and the only thing between the CSVs and a queryable database is a loader that doesn't mangle types.

I built one because pandas felt like bringing a forklift to carry groceries.

csv2sqlite.py in one command

python3 tools/csv2sqlite.py data.db users.csv orders.csv
python3 tools/csv2sqlite.py data.db *.csv --dry-run   # preview schemas first
python3 tools/csv2sqlite.py data.db new.csv --append  # add rows safely
Enter fullscreen mode Exit fullscreen mode

Each file becomes a table named after its stem. The part that matters is per-column type inference: INTEGER where the column is all ints, REAL for floats, TEXT otherwise, NULL for empty cells and --nulls tokens. Dates stay ISO TEXT — which sorts correctly, unlike US-format strings.

Booleans stay TEXT ('true'/'false') because SQLite has no bool type — cast in queries if you need it. And the safety rail: re-running without --append refuses to clobber existing tables rather than silently duplicating rows.

Verified

  • users.csv + orders.csv → correct CREATE TABLE schemas (id INTEGER, score REAL with NULL for empty, dates TEXT), row counts confirmed via SELECT
  • Re-run without --append → "table exists" error, exit 1
  • --dry-run prints the inferred schema, creates no db file, exit 0

Honest limits

  • Flat schema — single table per file, no JOINs or relations created. This is a loader, not a schema designer.
  • Type inference is per-column majority: a column that's 99% integers with one 'N/A' becomes TEXT, not INTEGER-with-exception. Check the dry-run output.
  • No streaming — rows load into memory, fine to ~GBs; beyond that use csvkit or pandas.
  • All inserts parameterized — no SQL injection via cell content. That one's non-negotiable and it's in there.

$10, stdlib only:

https://wesleygitari2-cloud.github.io/nexus-store-redirect/p/csv2sqlite

What's the messiest CSV-to-analysis workflow you have — and does it involve a real database yet?

Top comments (0)