Import CSV file into MySQL

In MySQL using LOAD DATA INFILE statement you can read data from a text file and import the data into a MySQL table i.e. Import CSV file into MySql.

But you need to match the CSV data with the columns of a table and the type of the each column of the table.

Suppose we have a `users` table with columns id, name, age.
And we have the following csv file where each row states each user’s data.

example.csv:
id,name,age
1,”John Doe”,25
2,”Jane Doe”,24
3,”Tim Shaw”,34

The below statements Import CSV file into users table:

LOAD DATA INFILE 'c:/xampp/example.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

You need to maintain some of the rules:

• use excel to edit the header names to have no spaces
• save as .csv
• open the new table insert a primary auto number column for ID
• change the type of the columns as desired.

My favorite for the import is Windows based HeidiSQL
It gives you a graphical interface to build the LOAD DATA command
You can re-use it programmatically later.