To import a CSV file directly into a MySQL database table - in the MySQL console run the following command:
load data local infile 'myfile.csv' into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\n' (FIELD1, FIELD2, FIELD3);
where
'myfile.csv' : the path and name of the file to import
TABLENAME : the name of the table in the current database
FIELD1... : the names of the table columns, in the order the columns appear in the CSV file. The order is very important - if you mix it up, the columns will be imported incorrectly - data that was supposed to end up in column B, for example, will end up in column A.
Here's an example:
MySQL> use mydatabase;
MySQL> load data local infile '/home/iamuser/myfile.csv' into table mytable fields terminated by ',' enclosed by '"' lines terminated by '\n' (title, address, phone, email, comments);
P.S. If the CSV file was created by Excel or some other software that puts column names into it along with the actual data, you first need to edit it and remove all the extra information - column names, trailing blank lines, etc.
Applies to: MySQL 5.x
Comments