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.
An author of Namaste UI, published several articles focused on blogging, business, web design & development, e-commerce, finance, health, lifestyle, marketing, social media, SEO, travel.
For any types of queries, contact us on info[at]namasteui.com.