How to Use LOAD DATA INFILE in MySQL for Fast Data Import

Use the LOAD DATA INFILE 'file_path' command in MySQL to import data from a text file into a table. Specify options like INTO TABLE, FIELDS TERMINATED BY, and LINES TERMINATED BY to match your file format.

Syntax

The LOAD DATA INFILE statement imports data from a file into a MySQL table. Key parts include:

• 'file_path': Path to your data file.
• INTO TABLE table_name: The target table.
• FIELDS TERMINATED BY 'char': Character separating fields (columns).
• LINES TERMINATED BY 'char': Character separating rows.
• Optional clauses like IGNORE n LINES to skip headers.

sql

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Example

This example loads a CSV file named data.csv into a table called users. The file has columns separated by commas and rows separated by new lines. The first line is a header and is skipped.

sql

LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Output

Query OK, 3 rows affected (0.01 sec)

Common Pitfalls

• File path issues: The file must be accessible by the MySQL server and the path must be correct.
• Permissions: MySQL user needs FILE privilege to use LOAD DATA INFILE.
• Secure file path: On some systems, files must be in the MySQL secure directory.
• Wrong delimiters: Make sure FIELDS TERMINATED BY and LINES TERMINATED BY match your file format.
• Skipping headers: Use IGNORE 1 LINES if your file has a header row.

sql

/* Wrong way: Missing IGNORE for header */
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

/* Right way: Skip header line */
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Quick Reference

Clause	Description
LOAD DATA INFILE 'file_path'	Specifies the file to load data from
INTO TABLE table_name	Target table to import data into
FIELDS TERMINATED BY 'char'	Character that separates columns
LINES TERMINATED BY 'char'	Character that separates rows
IGNORE n LINES	Skip first n lines (e.g., headers)
OPTIONALLY ENCLOSED BY 'char'	Fields optionally enclosed by a character (like quotes)

Key Takeaways

Use LOAD DATA INFILE to quickly import data from files into MySQL tables.

Match field and line terminators to your file format for correct parsing.

Use IGNORE n LINES to skip headers in your data file.

Ensure MySQL has permission to read the file and the file path is correct.

Check MySQL server secure-file-priv setting if file access is denied.