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.
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.