Import CSV File Into MySQL Table

Importing data from a CSV (Comma-Separated Values) file into a MySQL table is a common operation, often used for bulk data insertion. Here’s how you can do it efficiently.

1. Import CSV Using `LOAD DATA INFILE`

The fastest way to import a CSV file into MySQL is by using the LOAD DATA INFILE statement.

Syntax:

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

Example:

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

Explanation:

✔ FIELDS TERMINATED BY ',' → Specifies the delimiter (comma).
✔ ENCLOSED BY '"' → Ensures that values inside quotes are handled correctly.
✔ LINES TERMINATED BY '\n' → Defines row separation.
✔ IGNORE 1 ROWS → Skips the first row (column headers).

⚠ Common Error:
If you get a "The MySQL server is running with the --secure-file-priv option" error, check the directory using:
SHOW VARIABLES LIKE 'secure_file_priv';
Move your CSV file to this directory before running LOAD DATA INFILE.

2. Import CSV Using MySQL Workbench

Steps:

Open MySQL Workbench.
Select your database.
Click Table > Import Wizard.
Select CSV file and configure settings.
Click Next to finish the import.

3. Import CSV Using `INSERT INTO ... SELECT`

If LOAD DATA INFILE is restricted, use INSERT INTO.

Example:

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);

INSERT INTO employees (id, name, age, department)
SELECT * FROM (
    SELECT '1' AS id, 'Alice' AS name, '30' AS age, 'HR' AS department
    UNION ALL
    SELECT '2', 'Bob', '28', 'IT'
) AS tmp;

For bulk insertion, use a script to read a CSV file and generate multiple INSERT statements.

4. Import CSV Using the MySQL Command Line

If you're using the terminal, use:

mysql -u root -p database_name -e "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;"

5. Handling Common Issues

Issue	Solution
`secure_file_priv` error	Move the file to the specified directory
Encoding issues	Ensure CSV uses UTF-8
Incorrect column mapping	Specify columns explicitly in `LOAD DATA INFILE`

Summary

Method	Pros	Cons
`LOAD DATA INFILE`	Fastest import method	Requires file permission setup
MySQL Workbench	User-friendly GUI	Slower for large files
`INSERT INTO ... SELECT`	Works without file access	Not ideal for large datasets
MySQL CLI	Simple for quick imports	Requires correct file path

Would you like help automating CSV imports in MySQL? 🚀

Souy Soeng

Import CSV File Into MySQL Table