Importing CSV Data into MySQL

Once you have created the table, from the mySQL command prompt use:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' (col1,col2,...);

Once imported, if the data doesn’t look correct, then ensure the end-of-line characters are specified correctly. If you are moving data between Windows and *nix machines then the lines might be terminated by either \n or \r\n.

If the CSV data came from a spreadsheet program and the column headers have been left in then you can add IGNORE 1 LINES in order to skip the first line.

mysqlimport is essentially a wrapper for the above command. With mysqlimport there does not seem to be any access to any warnings generated by the import, so if the import does not work perfectly, the options to troubleshoot issues are limited.

Using the LOAD command from the mySQL command prompt affords access to the SHOW WARNINGS command to examine any warnings issued as a result of the import.