0

mySQL – Inserting data from a CSV file into an existing table

If normalization is not a concern, type the following command first:

SET foreign_key_checks = 0;

The rest of this query will import data from ‘data.csv’ and insert it into the specified table. If you are importing from an extended path in Windows, you need to use double backslashes (i.e. ‘C:\wamp\imports\data.csv’).

The line “LINES TERMINATED BY…” needs to end in ‘rn’ if you are importing from Access or just about anything from a Windows system. When importing from Linux-based systems, I believe ‘r’ alone will suffice.

LOAD DATA LOCAL INFILE ‘data.csv’
INTO TABLE name_of_existing_table
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘rn’
(field1, field2, field3, …)

If you did set foreign_key_checks = 0, be sure to set it back to 1 when done.

SET foreign_key_checks = 1;

Leave a Reply