How to Import CSV into MySQL

Galaxy Glossary

How do I import a CSV file into MySQL?

LOAD DATA INFILE or mysqlimport quickly bulk-loads CSV data into a MySQL table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why use LOAD DATA INFILE to import CSV?

LOAD DATA INFILE streams a CSV file directly into a table, bypassing row-by-row INSERTs, so large imports finish in seconds instead of minutes.

What is the minimum syntax?

Use LOAD DATA INFILE 'file.csv' INTO TABLE table_name;. Add options to match delimiters, headers, and encodings.

How do I handle a header row?

Add IGNORE 1 LINES so MySQL skips the first CSV line and prevents header text from becoming data.

How can I import only selected columns?

List target columns after the table name. Unlisted CSV fields can be routed to user variables and discarded.

Which file paths are allowed?

LOCAL paths read from the client machine (LOAD DATA LOCAL INFILE). Non-LOCAL paths read from the server and need secure_file_priv access.

How do I import a CSV into the Orders table?

LOAD DATA LOCAL INFILE '/tmp/orders.csv' INTO TABLE Orders FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, customer_id, order_date, total_amount);

Can I automate daily imports?

Wrap LOAD DATA in a stored procedure or use the command-line mysqlimport tool in a cron job for hands-free updates.

How does mysqlimport differ?

mysqlimport is a shell wrapper around LOAD DATA LOCAL INFILE; it infers the target table from the filename.

mysqlimport example?

mysqlimport --local --fields-terminated-by=, \ --ignore-lines=1 demo_db /tmp/Products.csv

Best practices for reliable imports?

1) Always load into a staging table first. 2) Set sql_mode='STRICT_ALL_TABLES' to surface bad rows. 3) Disable indexes only for very large files, then rebuild.

Why How to Import CSV into MySQL is important

How to Import CSV into MySQL Example Usage


-- Bulk-load new products
LOAD DATA LOCAL INFILE '/tmp/new_products.csv'
INTO TABLE Products
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, price, stock);

How to Import CSV into MySQL Syntax


LOAD DATA [LOCAL] INFILE 'path/to/file.csv'
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [OPTIONALLY] [ENCLOSED BY 'char']
        [ESCAPED BY 'char'] ]
    [LINES TERMINATED BY 'string']
    [IGNORE n LINES]
    [(col1,col2,...)]
    [SET colx = expr,...];

mysqlimport [OPTIONS] db_name textfile1 [textfile2 ...]

E-commerce demo:
LOAD DATA LOCAL INFILE '/tmp/order_items.csv'
INTO TABLE OrderItems
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, order_id, product_id, quantity);

Common Mistakes

Forgetting IGNORE 1 LINES: Imports the header row as data, causing type errors. Fix by adding IGNORE 1 LINES.
Mismatching column order: CSV fields must align with table columns unless you specify a column list. Provide the list to avoid shifted data.

Frequently Asked Questions (FAQs)

Do I need FILE privilege for LOCAL files?

No. LOCAL reads from the client, so only mysql --local-infile must be enabled, not the FILE privilege.

Can I load a gzip file directly?

No. Decompress first or use a pipeline (gunzip -c file.gz | mysql ...).

How do I skip bad rows but keep good ones?

Use IGNORE instead of REPLACE and check SHOW WARNINGS for rejected rows after the import.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.