MySQL Import CSV: Load CSV Files Safely

A CSV file from a customer, a spreadsheet or another system is untrusted data. It can have empty fields, wrong dates, duplicate rows, text where a number should be and strange characters. Loading it straight into your real table is risky.

The safe pattern is:

Load the raw file into a staging table that accepts anything (all VARCHAR).
Check the data with SQL queries.
Insert the clean rows into the real table.
Drop the staging table.

Step 0: the real table

query.sql

SQL

CREATE TABLE members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    joined_on DATE NOT NULL,
    plan_price DECIMAL(8,2) NOT NULL
);

Step 1: a staging table with loose types

Everything is text, and nothing can fail on loading:

query.sql

SQL

CREATE TABLE members_staging (
    row_no INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    full_name VARCHAR(255),
    joined_on VARCHAR(30),
    plan_price VARCHAR(30)
);

Load the file with LOAD DATA (LOAD DATA) or a tool. To keep this lesson runnable on its own, we put some typical messy rows in by hand:

query.sql

SQL

INSERT INTO members_staging (email, full_name, joined_on, plan_price) VALUES
    ('asha@example.com',  'Asha Rao',    '2026-01-15', '499.00'),
    ('ben@example.com',   'Ben Carter',  '15/03/2026', '499.00'),
    ('chitra@example.com','Chitra Nair', '2026-03-10', 'free'),
    ('',                  'No Email',    '2026-03-11', '499.00'),
    ('asha@example.com',  'Asha Again',  '2026-04-01', '499.00'),
    (' dev@example.com ', ' Dev Malhotra ', '2026-04-05', '699.50');

Step 2: check the data

Look for the problems before you copy anything.

query.sql

SQL

SELECT row_no, 'bad date' AS problem, joined_on AS value FROM members_staging WHERE joined_on NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' OR STR_TO_DATE(joined_on, '%Y-%m-%d') IS NULL
UNION ALL
SELECT row_no, 'bad price', plan_price FROM members_staging WHERE plan_price NOT REGEXP '^[0-9]+(\\.[0-9]+)?$'
UNION ALL
SELECT row_no, 'missing email', email FROM members_staging WHERE TRIM(email) = ''
ORDER BY row_no;

Output

OUTPUT

+--------+---------------+------------+
| row_no | problem       | value      |
+--------+---------------+------------+
|      2 | bad date      | 15/03/2026 |
|      3 | bad price     | free       |
|      4 | missing email |            |
+--------+---------------+------------+
3 rows in set

A date in the wrong format (15/03/2026) fails the first test. STR_TO_DATE returns NULL in a plain SELECT for an impossible date such as 2026-02-30, so the second test catches those too. The REGEXP finds prices that are not numbers. Next, duplicates inside the file:

query.sql

SQL

SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS copies, GROUP_CONCAT(row_no ORDER BY row_no) AS rows_in_file
FROM members_staging
WHERE TRIM(email) <> ''
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

Output

OUTPUT

+------------------+--------+--------------+
| email            | copies | rows_in_file |
+------------------+--------+--------------+
| asha@example.com |      2 | 1,5          |
+------------------+--------+--------------+
1 row in set

Step 3: insert only the good rows

Clean as you copy: trim spaces, lower-case emails, skip the bad rows and keep the first copy of a duplicate.

query.sql

SQL

INSERT INTO members (email, full_name, joined_on, plan_price)
SELECT LOWER(TRIM(email)), TRIM(full_name), STR_TO_DATE(joined_on, '%Y-%m-%d'), plan_price
FROM members_staging s
WHERE TRIM(email) <> ''
  AND joined_on REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$'
  AND plan_price REGEXP '^[0-9]+(\\.[0-9]+)?$'
  AND row_no = (SELECT MIN(row_no) FROM members_staging WHERE LOWER(TRIM(email)) = LOWER(TRIM(s.email)));

SELECT id, email, full_name, joined_on, plan_price FROM members ORDER BY id;

Output

OUTPUT

+----+------------------+--------------+------------+------------+
| id | email            | full_name    | joined_on  | plan_price |
+----+------------------+--------------+------------+------------+
|  1 | asha@example.com | Asha Rao     | 2026-01-15 |     499.00 |
|  2 | dev@example.com  | Dev Malhotra | 2026-04-05 |     699.50 |
+----+------------------+--------------+------------+------------+
2 rows in set

Only Asha (first copy) and Dev were loaded. Ben (date in the wrong format), Chitra (bad price), the blank email and the duplicate Asha were left out.

info

Strict mode and impossible dates

The REGEXP above checks the format. A date that has the right format but does not exist (2026-02-30) is not removed by it. Inside an INSERT or UPDATE, STR_TO_DATE raises error 1411 for such a value, and the whole statement fails with nothing loaded. That is the safe outcome: find the row with the check query in step 2 (a plain SELECT shows NULL), fix it in the staging table and run the insert again.

Step 4: keep a report of rejected rows

query.sql

SQL

SELECT s.row_no, s.email, s.full_name
FROM members_staging s
WHERE NOT EXISTS (SELECT 1 FROM members m WHERE m.email = LOWER(TRIM(s.email)))
ORDER BY s.row_no;

Output

OUTPUT

+--------+--------------------+-------------+
| row_no | email              | full_name   |
+--------+--------------------+-------------+
|      2 | ben@example.com    | Ben Carter  |
|      3 | chitra@example.com | Chitra Nair |
|      4 |                    | No Email    |
+--------+--------------------+-------------+
3 rows in set

Give this list to the person who sent the file. Rejected rows must not disappear silently.

query.sql

SQL

DROP TABLE members_staging;

A checklist for every import

Back up the target table first.
Check the line endings (\n or \r\n) and the delimiter (comma, semicolon, tab).
Check the header row.
Dates: agree on one format (YYYY-MM-DD).
Decide what to do with duplicates (skip, update or reject) before you start.
Do the whole import in a transaction, or load into staging first, so a failed import leaves the real table untouched.
Log how many rows were read, loaded and rejected.

lightbulb

Excel and CSV

Spreadsheets can change data: leading zeros disappear (007 becomes 7), long numbers turn into scientific notation, and dates change format. Ask for the file as text CSV, UTF-8, and check a few rows by eye.