Import CSV into MySQL with LOAD DATA

To import a CSV file into an existing MySQL table, use LOAD DATA. It reads a delimited text file and loads its rows into the table. Use LOAD DATA LOCAL INFILE when the file is on the client computer; use LOAD DATA INFILE when the file is on the MySQL server.

To export query results to a server-side CSV file, see Export MySQL to CSV.

Example: import a CSV file

Suppose products.csv is saved on the client computer and contains a header row:

product_id,product_name,price
101,Notebook,4.50
102,"Pen, blue",1.25
103,Backpack,32.00

Create a table with matching columns:

CREATE TABLE product_import (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Run LOAD DATA LOCAL INFILE from a MySQL client that can access the file:

LOAD DATA LOCAL INFILE '/path/to/products.csv'
INTO TABLE product_import
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(product_id, product_name, price);

In this statement:

• LOCAL tells the client to read the file and send it to MySQL. The path is on the client computer.
• CHARACTER SET utf8mb4 specifies the file’s character set. Use the character set that matches the file.
• FIELDS TERMINATED BY ',' separates comma-delimited values. OPTIONALLY ENCLOSED BY '"' lets text values be quoted, so the comma in "Pen, blue" stays inside one field.
• LINES TERMINATED BY '\n' matches the LF line endings used in the example. For a Windows CSV with CRLF line endings, use LINES TERMINATED BY '\r\n'.
• IGNORE 1 LINES skips the header row. The column list maps the fields to the target columns in order.

Inspect warnings immediately after loading, then verify the imported rows:

SHOW WARNINGS;

SELECT product_id, product_name, price
FROM product_import
ORDER BY product_id;

The SELECT query should return three products. Check warnings and the row count when importing real files; a successful LOAD DATA statement can still report data-conversion warnings.

Choose LOCAL or server-side INFILE

LOAD DATA LOCAL INFILE reads a file from the client computer and sends its contents to the server. Both the server and client must permit local loading. In MySQL 8.4, the server’s local_infile option is disabled by default; some clients also disable LOCAL. If you see error 3950, check the client and server settings rather than assuming the CSV syntax is wrong.

Use LOCAL only with a trusted MySQL server. The server initiates the file transfer request, so a malicious server could ask a client to send another file that the client user can read. Follow your administrator’s security policy and any client-side file restrictions.

Without LOCAL, LOAD DATA INFILE reads a file from the server host. The account needs the FILE privilege, and the path must comply with the server’s secure_file_priv setting. This is useful when the file is already on the database server and the account is allowed to read it.

Common CSV import issues

• Header row imported as data: Add IGNORE 1 LINES only when the file begins with a header.
• Columns land in the wrong fields: List target columns in the same order as the fields in the CSV, or preprocess fields with user variables and a SET clause.
• Text is garbled: Specify the correct CHARACTER SET for the file.
• The statement runs but some values look wrong: LOCAL affects error handling. Unless REPLACE is specified, it has the same effect as IGNORE for data interpretation; some invalid values can become warnings and be adjusted instead of stopping the load. Check SHOW WARNINGS and verify the imported data.
• The CSV is generated on Windows: Match its CRLF line ending with LINES TERMINATED BY '\r\n'.

For rows selected from another database table, use MySQL INSERT INTO SELECT. For inserting literal values in batches, see MySQL INSERT multiple rows.

Summary

• Use LOAD DATA LOCAL INFILE for a client-side file when both client and server allow it.
• Use LOAD DATA INFILE for a server-side file when the account and secure_file_priv permit it.
• Match the CSV delimiter, quoting, character set, and line endings; then inspect warnings and verify the rows.

See the MySQL 8.4 Reference Manual: LOAD DATA and Security Considerations for LOAD DATA LOCAL for the full syntax and security details.