How to Import CSV Files into MySQL Easily

To import a CSV file into MySQL, use the LOAD DATA INFILE command specifying the file path, target table, and CSV format options. This command reads the CSV file and inserts its data into the specified MySQL table quickly and efficiently.

Syntax

The basic syntax to import a CSV file into MySQL is:

• LOAD DATA INFILE 'file_path': Specifies the path to the CSV file.
• INTO TABLE table_name: The table where data will be inserted.
• FIELDS TERMINATED BY ',': Defines the delimiter used in the CSV (usually a comma).
• ENCLOSED BY '"': Optional, if fields are enclosed in quotes.
• LINES TERMINATED BY '\n': Defines the line ending (usually newline).
• IGNORE 1 LINES: Skips the header row if present.

sql

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Example

This example imports a CSV file named data.csv into a MySQL table called employees. The CSV has a header row, so it is skipped.

sql

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

Output

Query OK, 5 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when importing CSV files include:

• File path errors: The file must be accessible by the MySQL server, often requiring absolute paths.
• Permission issues: MySQL needs read permission on the CSV file.
• Incorrect delimiters: Ensure the FIELDS TERMINATED BY matches the CSV delimiter.
• Not skipping header rows: Use IGNORE 1 LINES if your CSV has headers.
• Line endings mismatch: Use LINES TERMINATED BY '\n' for Unix or '\r\n' for Windows.

Example of a wrong and right way:

sql

/* Wrong: Missing IGNORE 1 LINES causes header to be imported as data */
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

/* Right: Skips header row */
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Quick Reference

Option	Description
LOAD DATA INFILE 'file_path'	Path to the CSV file to import
INTO TABLE table_name	Target table for data insertion
FIELDS TERMINATED BY ','	Character that separates fields (usually comma)
ENCLOSED BY '"'	Character enclosing fields (optional)
LINES TERMINATED BY '\n'	Line ending character (newline for Unix)
IGNORE 1 LINES	Skip header row in CSV

Key Takeaways

Use LOAD DATA INFILE with correct file path and permissions to import CSV files.

Match FIELDS TERMINATED BY and ENCLOSED BY options to your CSV format.

Use IGNORE 1 LINES to skip CSV headers and avoid importing them as data.

Ensure MySQL server can access the CSV file location with proper permissions.

Check line endings to match your operating system for smooth imports.