How to import a CSV into a database

There are two jobs hiding in “import a CSV”: make a table whose column types fit the data, and load the rows. Bulk loaders do the second well and the first badly. INSERT scripts do both, slowly. Use the right one for the size.

Which route for which size

Rows	Use
Up to ~100,000	A generated script of CREATE TABLE and batched INSERTs. Readable, reviewable, repeatable, works in any client.
Hundreds of thousands and up	The database’s bulk loader. Generate only the CREATE TABLE (or write it from the column report), then load.

The bulk loaders

-- PostgreSQL (client-side file, from psql)
\copy imported_data FROM 'data.csv' WITH (FORMAT csv, HEADER true)

-- MySQL (needs local_infile enabled)
LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE imported_data
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

# SQLite (shell)
sqlite3 app.db ".mode csv" ".import --skip 1 data.csv imported_data"

# BigQuery
bq load --source_format=CSV --skip_leading_rows=1 mydataset.imported_data data.csv

PostgreSQL’s server-side COPY reads a file on the database server, not your laptop; \copy in psql reads from your machine. In SQLite, .import into a table that does not exist creates one with every column as text, which is why creating the table first matters.

The traps that bite every time

Leading zeros. ZIP codes, account numbers and phone numbers stored as integers lose their zeros. Keep them as text.
Ambiguous dates. 03/04/2024 is 3 April or 4 March depending on who exported it. Decide, then convert to ISO YYYY-MM-DD. Mixed formats in one column must be cleaned first.
Empty string vs NULL. A blank cell is not automatically NULL to a loader. Decide which you mean, especially for numeric and date columns, where an empty string is an error.
Encoding. Excel often exports Windows-1252 or UTF-8 with a BOM. A garbled é means the encoding is wrong, not the data.
Column widths from a sample. A VARCHAR(32) sized from the first rows rejects the 40-character name in row 90,000. When in doubt, use TEXT (PostgreSQL, SQLite) or a generous width.
Money. Use NUMERIC/DECIMAL, never floating point, for amounts you will add up.

Using the generator

Paste the CSV into the tool, pick your database, read the notes it prints about anything it had to guess, and run the script against a copy first. From a terminal: node cli.mjs data.csv --dialect mysql.