Turn a CSV Into a Reviewable SQLite Import Script

There are two common ways to move a CSV into SQLite. The SQLite command-line shell can import the file directly, or you can generate a SQL script containing a table definition and the rows to insert.

HappyCSV uses the second approach. It produces readable SQL rather than a binary .sqlite database. That is useful when you want to inspect the schema and values before anything runs.

What the generated file contains

Given this CSV:

facility_code,name,admissions
0012,Muna Clinic,146
0013,Custom House Clinic,108

a basic script may contain:

CREATE TABLE "facilities" (
  "facility_code" TEXT,
  "name" TEXT,
  "admissions" INTEGER
);

INSERT INTO "facilities" ("facility_code", "name", "admissions")
VALUES ('0012', 'Muna Clinic', 146);

The script is only a starting schema. A CSV does not declare primary keys, foreign keys, uniqueness, required fields, or business rules. Those decisions still belong to you.

Generate the script

Open CSV to SQLite, choose the source CSV, and provide a sensible table name. HappyCSV parses the file in your browser and downloads a .sql script.

Open that script in a text editor before running it. Review the CREATE TABLE statement first, then inspect several INSERT statements containing difficult values: apostrophes, blank cells, long identifiers, non-ASCII text, and decimals.

Check every inferred column type

SQLite uses type affinity. A declared type expresses the preferred storage type, but ordinary SQLite tables can still contain values of other storage classes. The official SQLite datatype documentation explains this flexible behaviour.

For imported CSV data, the main choices are usually:

• TEXT for names, labels, codes, identifiers, phone numbers, and dates you have not normalised;
• INTEGER for whole-number quantities;
• REAL for approximate decimal values; and
• BLOB for binary data, which ordinary CSV is poorly suited to carry.

Be conservative with identifiers. 0012 should remain text. If it is inserted into a numeric-affinity column, SQLite may store it as 12.

Dates deserve an explicit policy. SQLite has no dedicated date storage class. Applications commonly use ISO-formatted text, Unix timestamps, or Julian day numbers. Choose one representation and document it.

Blank fields need a rule

An empty CSV field could mean an empty string or a missing value. SQL distinguishes '' from NULL.

Do not convert every blank automatically without understanding the destination. An empty middle name and an unknown admission count may both look blank in CSV but mean different things.

Likewise, N/A, NULL, unknown, and - are text unless you deliberately map them.

Review names and constraints

CSV headers often make poor database identifiers. They may contain spaces, punctuation, duplicates, or reserved words such as order and group.

The generated script should quote identifiers safely. You may still want to rename columns to stable, readable names before creating the table.

Then add the constraints the source file cannot infer:

CREATE TABLE "facilities" (
  "facility_code" TEXT PRIMARY KEY,
  "name" TEXT NOT NULL,
  "admissions" INTEGER CHECK ("admissions" >= 0)
) STRICT;

SQLite's CREATE TABLE documentation covers PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and foreign-key constraints. STRICT tables provide stronger type enforcement, but they require deliberate schema choices and are not appropriate as an automatic guess for every CSV.

Run the script in a new database

Keep the source file unchanged and test the SQL against a new database:

sqlite3 review.db < facilities.sql

Then check the schema and data:

sqlite3 review.db ".schema facilities"
sqlite3 review.db "SELECT COUNT(*) FROM facilities;"
sqlite3 review.db "SELECT * FROM facilities LIMIT 10;"

Compare the database row count with the number of data rows in the CSV. Also check null counts, duplicate keys, minimum and maximum numeric values, and the first and last few identifiers.

For production-sized imports, thousands of individual INSERT statements may be slower and larger than direct bulk import. The SQLite shell supports CSV import with .import; applications can also use prepared statements inside a transaction. HappyCSV's script is best when reviewability and portability matter more than maximum loading speed.

Do not run an unfamiliar script blindly

A generated SQL file is executable code. Read it before running it, especially when the source file came from someone else. Confirm that it contains only the expected table creation and inserts, uses the intended table name, and does not include destructive statements.

Test in a disposable database first. If the final target already contains data, take a backup and decide how conflicts should be handled before importing.

Keep a small import record

For a repeatable hand-off, record the source filename, row count, delimiter and encoding, chosen schema, transformations, and validation queries. That note is often more valuable than a vague assurance that the import “worked.”

The advantage of generating SQL is visibility. Use it: inspect the assumptions, improve the schema, and only then create the database you intend to keep.