|
Written by Ron Bassett
|
|
Sunday, 14 June 2009 22:56 |
Many times you will want to load a csv file into a database. If your table is the same structure as the csv file there are some very quick ways to load the file into the db with out having to load it into an array and do foreach loops for each row. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.
LOAD DATA LOCAL INFILE "/location-of.csv" INTO TABLE csv.table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' LOAD%20DATA%20LOCAL%20INFILE%20%22%2Flocation-of.csv%22%20INTO%20TABLE%20csv.table%20%0AFIELDS%20TERMINATED%20BY%20%27%2C%27%0AOPTIONALLY%20ENCLOSED%20BY%20%27%22%27
Sometimes when exporting from a windose machine you will need to use the code below. Windows is bad about inserting poorly formated line breaks.
LOAD DATA LOCAL INFILE "/location-of.csv" INTO TABLE csv.table
FIELDS OPTIONALLY ENCLOSED BY '\"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n' LOAD%20DATA%20LOCAL%20INFILE%20%22%2Flocation-of.csv%22%20INTO%20TABLE%20csv.table%20%0AFIELDS%20OPTIONALLY%20ENCLOSED%20BY%20%27%5C%22%27%20%0ATERMINATED%20BY%20%27%2C%27%20%0ALINES%20TERMINATED%20BY%20%27%5Cr%5Cn%27 |
