How to Import Data into MySQL from a CSV File

I once imported a 2-million-row CSV into MySQL using individual INSERT statements generated by a script, and watched it crawl along for nearly four hours before I gave up and looked for a better way. LOAD DATA INFILE did the same import in under two minutes. That gap taught me that how you import data matters just as much as whether you import it correctly, so I want to cover both the mechanics and the performance side properly.

Method 1: LOAD DATA INFILE (Fastest Method)

LOAD DATA INFILE is MySQL’s purpose-built bulk import statement, and it’s dramatically faster than row-by-row INSERT statements because it bypasses much of the per-statement overhead.

First, let’s create a target table:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50),
    created_at DATETIME
);

Assume I have a file customers.csv:

1,Ayesha,Lahore,2026-01-05 10:00:00
2,Bilal,Karachi,2026-01-15 11:30:00
3,Sara,Lahore,2026-02-02 09:45:00

LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(customer_id, name, city, created_at);

Output:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Just like exports, LOAD DATA INFILE reads from the server’s filesystem by default, governed by the same secure_file_priv restriction I check before every import:

SHOW VARIABLES LIKE 'secure_file_priv';

Method 2: LOAD DATA LOCAL INFILE

When the CSV lives on my local machine rather than the database server (very common when connecting to a remote or cloud-hosted instance), I use the LOCAL keyword instead:

LOAD DATA LOCAL INFILE '/home/me/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(customer_id, name, city, created_at);

This requires both the client and server to have local_infile enabled, since it’s disabled by default for security reasons (a malicious server could otherwise request arbitrary local files from a connecting client):

SET GLOBAL local_infile = 1;

mysql --local-infile=1 -u root -p mydatabase

Skipping a Header Row

Most real-world CSVs include a header row that isn’t actual data. I skip it with IGNORE 1 LINES:

LOAD DATA INFILE '/var/lib/mysql-files/customers_with_header.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(customer_id, name, city, created_at);

Mapping Columns and Transforming Data During Import

Sometimes the CSV column order doesn’t match the table, or I need to transform a value on the way in — LOAD DATA INFILE supports both through user variables:

LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(customer_id, name, @raw_city, created_at)
SET city = UPPER(@raw_city);

Here, @raw_city captures the raw CSV field, and SET city = UPPER(@raw_city) transforms it before storing it — useful for normalizing casing, trimming whitespace, or parsing a composite field.

Handling Missing Columns and NULLs

If the CSV doesn’t include every table column (like an auto-increment ID you want MySQL to generate), I simply omit it from the column list and let the default/auto-increment behavior take over:

LOAD DATA INFILE '/var/lib/mysql-files/customers_no_id.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, city, created_at);

For explicit NULL values in the source CSV, I use the special \N sequence, which MySQL interprets as NULL rather than the literal string “NULL”:

Ayesha,Lahore,\N

Method 3: mysqlimport Command-Line Tool

mysqlimport is a command-line wrapper around LOAD DATA INFILE, convenient for scripting bulk imports without writing raw SQL:

mysqlimport --local --fields-terminated-by=',' --fields-enclosed-by='"' \
  --lines-terminated-by='\n' -u root -p mydatabase /path/to/customers.csv

By convention, mysqlimport expects the filename (minus extension) to match the target table name — customers.csv imports into a table called customers.

Method 4: INSERT Statements (Small Datasets Only)

For small CSVs (a few hundred rows or fewer), I sometimes just generate INSERT statements directly, especially if I need row-level validation logic that’s easier to express in application code than in a LOAD DATA transformation:

INSERT INTO customers (name, city, created_at) VALUES
('Ayesha', 'Lahore', '2026-01-05 10:00:00'),
('Bilal', 'Karachi', '2026-01-15 11:30:00');

Batching multiple rows into a single multi-row INSERT (rather than one statement per row) is still meaningfully faster than individual inserts, even though it’s far slower than LOAD DATA INFILE for genuinely large datasets.

Import Performance Comparison

Import Workflow Diagram

flowchart TD
    A[CSV file ready] --> B{File location}
    B -->|On DB server| C[LOAD DATA INFILE]
    B -->|On local client machine| D[LOAD DATA LOCAL INFILE or mysqlimport]
    C --> E[Data loaded directly, fastest path]
    D --> F[Data streamed from client to server]
    E --> G[Verify row counts and spot-check data]
    F --> G
    G --> H[Add/rebuild indexes if disabled during import]

Handling Errors and Duplicate Keys During Import

LOAD DATA INFILE supports two conflict-handling modifiers:

-- Skip rows that would violate a unique constraint, keeping the existing row
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
IGNORE INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(customer_id, name, city, created_at);

-- Overwrite existing rows on duplicate primary/unique key
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
REPLACE INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(customer_id, name, city, created_at);

I use IGNORE when re-running an import that might partially overlap with existing data (idempotent retries), and REPLACE when the CSV is meant to be the new source of truth for those specific rows.

Real-World DBA Scenarios

• Data migrations: importing exported CSVs from a legacy system into a new MySQL schema, often combined with SET clauses to reshape or clean data during load.
• Nightly ETL pipelines: a scheduled job exports CSVs from an upstream system, then LOAD DATA INFILE bulk-loads them into a staging table before a stored procedure merges them into production tables.
• Bulk product catalog updates: e-commerce teams I’ve worked with regularly re-import large product CSVs from suppliers, using REPLACE semantics to keep the catalog current.
• Disaster recovery testing: restoring a known-good CSV snapshot into a test environment to validate data pipelines end-to-end.

Security Considerations

• LOAD DATA LOCAL INFILE has historically been a vector for security issues (a compromised or malicious MySQL server requesting arbitrary files from a connecting client), so I only enable local_infile when genuinely needed, and I keep it disabled by default in client configuration.
• Just like exports, imports touching sensitive data warrant validating the CSV’s source and integrity (checksums) before loading, especially in automated pipelines where a corrupted or tampered file could silently introduce bad data.
• I always import into a staging table first for anything touching production data, run validation queries, and only then merge into the real table — this has saved me from more than one malformed CSV wreaking havoc directly on live tables.

Common Mistakes

• Forgetting IGNORE 1 LINES and importing the header row as a garbage data row.
• Mismatched delimiter or enclosure characters between the actual CSV and the LOAD DATA statement, causing columns to shift.
• Assuming date formats will auto-parse — MySQL expects YYYY-MM-DD (or YYYY-MM-DD HH:MM:SS) by default; other formats need STR_TO_DATE() in a SET clause.
• Importing directly into a production table without a staging step, risking partial or corrupted data being immediately live.

Troubleshooting Table

FAQs

What’s the difference between LOAD DATA INFILE and LOAD DATA LOCAL INFILE? INFILE reads a file from the MySQL server’s filesystem; LOCAL INFILE reads from the client machine’s filesystem and streams it to the server.

Why is LOAD DATA LOCAL INFILE disabled by default? It’s a security measure — without restriction, a malicious or compromised server could request arbitrary files from any connecting client.

How do I represent NULL values in a CSV for import? Use the literal sequence \N for a field that should become SQL NULL.

Can I transform data while importing? Yes, using user variables in the column list combined with a SET clause to apply functions like UPPER(), STR_TO_DATE(), or arithmetic before storing the value.

What happens if my CSV has more or fewer columns than the target table? You explicitly list which table columns correspond to which CSV fields in the LOAD DATA statement; any table column omitted from that list gets its default value (or auto-increment, or NULL if allowed).

Interview Questions

1. Why is LOAD DATA INFILE significantly faster than row-by-row INSERT statements?
2. What’s the difference between LOAD DATA INFILE and LOAD DATA LOCAL INFILE, and what security concern does the latter raise?
3. How would you skip a header row during import?
4. How do you handle duplicate key conflicts during a bulk import — what’s the difference between IGNORE and REPLACE?
5. How would you transform or clean a column’s data during the import process itself?
6. Why might you disable indexes before a large import and rebuild them afterward?
7. What staging strategy would you use before merging an imported CSV into a live production table?

Optimization Tips

• Temporarily disable non-unique secondary indexes before a very large import (ALTER TABLE ... DISABLE KEYS) and re-enable them afterward (ALTER TABLE ... ENABLE KEYS), since building indexes in bulk at the end is faster than updating them per inserted row.
• Import into an unindexed staging table, validate the data, then move it into the final indexed table with a single INSERT ... SELECT.
• Increase bulk_insert_buffer_size for MyISAM tables, or tune innodb_buffer_pool_size appropriately for InnoDB, to accommodate large bulk loads efficiently.
• Split extremely large CSVs into chunks and import in batches if you need to monitor progress or handle partial failures gracefully.
• Run ANALYZE TABLE after a large import to refresh the optimizer’s statistics, since a freshly bulk-loaded table’s query plans can otherwise be based on stale statistics.

Summary and Key Takeaways

Getting data into MySQL efficiently comes down to picking LOAD DATA INFILE (or its LOCAL variant for remote servers) over row-by-row inserts whenever you’re dealing with anything beyond a trivial dataset — the performance difference is not subtle. Handling header rows, NULLs, date formats, and duplicate-key conflicts correctly up front avoids a lot of downstream cleanup, and staging imports before merging into production tables has consistently been the habit that’s protected me from a single bad CSV causing real damage. Once you’re comfortable with LOAD DATA INFILE‘s options, bulk imports go from a dreaded chore to a fast, routine part of the data pipeline.

References

• MySQL 8.0 Reference Manual — LOAD DATA Statement
• MySQL 8.0 Reference Manual — mysqlimport — A Data Import Program
• MySQL 8.0 Reference Manual — local_infile System Variable
• MySQL 8.0 Reference Manual — Bulk Data Loading for InnoDB Tables