CSV to MySQL conversion and import

Question

Im working on a large project, havent had to do what I need help with before. I have a csv file, it contains a large amount of data, namely all of the cities, towns, suburbs in Australia. I need to convert the csv file to sql for mysql, and then import it into the database.

What would be the best way to achieve this?

score 3 · Accepted Answer · answered Jun 05 '10 at 02:19

3

Use LOAD DATA INFILE or the equivalent command-line tool mysqlimport.

These are easy to use for loading CSV data, and this method runs 20x faster than importing rows one at a time with SQL.

answered Jun 05 '10 at 02:19

Bill Karwin

538,548
86
673
828

Thanks. I did try to use the LOAD DATA INFILE method, but it would not accept my path to the csv file. Any suggestions? – Lea Jun 05 '10 at 02:35
@Lea - This is the way to go. What is the problem with the path ? +1 – Romain Hippeau Jun 05 '10 at 02:44
@Romain Hippeau it simply returns the error "csv file not found". I tried to use the absolute path to the file, but it doesnt seem to accept it. – Lea Jun 05 '10 at 02:46
@Lea can you run it from the location where your csv file is located ? – Romain Hippeau Jun 05 '10 at 02:54
Try `LOAD DATA LOCAL INFILE` otherwise it wants the file to be under the MySQL Server's data directory. – Bill Karwin Jun 05 '10 at 03:39
@Lea - Did you try Bill's idea above ? – Romain Hippeau Jun 05 '10 at 20:13

CSV to MySQL conversion and import

1 Answers1

Linked