← All Kits · SQL Kit

Loading Your First CSV Into a Database Without Ruining the Data

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

The import runs, it says it succeeded, and the data is now subtly different from the file. Postcodes lost their leading zeros, dates became text, a column with one stray word became text for every row, and a total row from the bottom of the export is now a record.

What you do: create the table yourself, load everything as text, check, then cast into a clean table. It is one extra step and it makes the whole load reversible.

The short version. Let the importer move the data. Do not let it decide the types.

Five decisions before you load

Answer all five by looking at the file before touching the database. Open the first thousand rows in a text editor, not in Excel, because Excel will have already changed some of them by the time you look. That peek command is in opening a large CSV.

The staging pattern

Two tables. One holds everything as text exactly as it arrived; the other holds the typed, cleaned version. It sounds like extra work and it takes about four minutes.

-- 1. staging: every column TEXT, nothing can be lost
CREATE TABLE orders_raw (
  order_id   TEXT,
  customer   TEXT,
  order_date TEXT,
  amount     TEXT,
  status     TEXT
);

-- 2. import into it (sqlite3 shell)
.mode csv
.import --skip 1 orders.csv orders_raw

-- 3. look before casting
SELECT COUNT(*) FROM orders_raw;
SELECT * FROM orders_raw LIMIT 10;
SELECT order_date FROM orders_raw
WHERE order_date NOT LIKE '____-__-__' LIMIT 20;

That third query is the one that earns the pattern. It lists the values that are not in the expected shape, before any conversion has thrown them away.

-- 4. cast into the real table, deliberately
CREATE TABLE orders (
  order_id   TEXT PRIMARY KEY,
  customer   TEXT,
  order_date DATE,
  amount     REAL,
  status     TEXT
);

INSERT INTO orders
SELECT TRIM(order_id),
       TRIM(customer),
       DATE(order_date),
       CAST(REPLACE(REPLACE(amount, ',', ''), '£', '') AS REAL),
       LOWER(TRIM(status))
FROM orders_raw
WHERE order_id IS NOT NULL AND order_id <> '';

Every transformation is visible and every one is a decision you made. If a cast fails or produces nulls, the raw table is still there and nothing has to be re-downloaded.

The three checks after loading

-- 1. row count against the file
SELECT COUNT(*) FROM orders;          -- compare with wc -l, minus the header

-- 2. no column silently empty
SELECT SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_dates,
       SUM(CASE WHEN amount     IS NULL THEN 1 ELSE 0 END) AS null_amounts
FROM orders;

-- 3. a value you can verify by eye
SELECT * FROM orders WHERE order_id = '00042';

The second one catches a failed cast, which is the most common way a load goes wrong while reporting success. A column that is entirely null after a cast means the source format did not match what you assumed, every time.

The other tools

DuckDB's read_csv_auto is unusually good at guessing, and you can override any column with the types argument when the guess is wrong. Postgres \copy runs client side, which matters because the plain COPY command needs the file to be on the server.

When the file will arrive every month

1. Write the load as a script, never as a sequence of clicks.
2. Put the source file name and the load date into a column, so any row can be traced back.
3. Load into staging, run the checks, and only then replace or append to the clean table.
4. Keep the raw files. Storage is cheap and a re-load is the only real recovery.
5. Log the row count of every load in a small table. Drift shows up as a number, not a feeling.

How to apply this to your own work

1. Create the table yourself for the next file you load, rather than letting the tool do it.
2. Add the staging step once and see how much it catches on a file you thought was clean.
3. Run the three checks and write the numbers into the project notes.
4. Make every ID column text, and check one known ID afterwards.
5. Turn a repeated import into a script the first time you do it twice.

The one habit to keep

Count the rows in the file before you import and after. It takes ten seconds, it is the only way to notice a truncated or partial load, and it is the number you will want when somebody asks whether the data is complete.

Do you know the row count of the last file you loaded into anything?