CSV to MySQL conversion and import

views:

answers:

CSV to MySQL conversion and import

Hi all,

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?

Thanks all, Lea

+1 A:

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.

Bill Karwin 2010-06-05 02:19:27

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 2010-06-05 02:35:52

@Lea - This is the way to go. What is the problem with the path ? +1

Romain Hippeau 2010-06-05 02:44:27

@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 2010-06-05 02:46:07

@Lea can you run it from the location where your csv file is located ?

Romain Hippeau 2010-06-05 02:54:45

Try `LOAD DATA LOCAL INFILE` otherwise it wants the file to be under the MySQL Server's data directory.

Bill Karwin 2010-06-05 03:39:21

@Lea - Did you try Bill's idea above ?

Romain Hippeau 2010-06-05 20:13:44

ansaurus

tags:

views:

answers:

CSV to MySQL conversion and import

related questions