Page 1 of 1

How do I load a csv file in MYSQL

Posted: Tue Sep 14, 2010 9:46 pm
by Peter_w
Hello,

How do I load a csv file in MYSQL using shell.
I want to load a comma separated file from the command line.

I know that it can be done by PHP, but I wonder if their is simpler more direct way.

Re: How do I load a csv file in MYSQL

Posted: Thu Sep 16, 2010 10:40 pm
by chris
You can login in mysql:

Code: Select all

mysql -u You -p database
and the you can use the LOAD DATA INFILE command

Code: Select all

LOAD DATA INFILE 'data.csv' INTO TABLE table
You can get the complete explanation here:
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Re: How do I load a csv file in MYSQL

Posted: Wed Sep 22, 2010 11:53 am
by mister_v
Hi,

it doesn't work!
Everytime I try, I get a "file not found" error.
But the file is there!

Code: Select all

mysql> LOAD DATA INFILE '/home/me/postcodes.csv' INTO TABLE i_city;
ERROR 29 (HY000): File '/home/me/postcodes.csv' not found (Errcode: 13)

Re: How do I load a csv file in MYSQL

Posted: Wed Sep 22, 2010 12:10 pm
by chris
MYSQL needs not only read access on the file, but also execution rights on directory.
It reads using the rights of the mysqld process (user: mysql)

You probably don't want your home-dir accessible by everyone.

So a quick solution it to copy the file to the /tmp -dir and load it from there.

Code: Select all

LOAD DATA INFILE '/tmp/postcodes.csv' INTO TABLE i_city;

Re: How do I load a csv file in MYSQL

Posted: Mon Dec 27, 2010 6:42 pm
by chris
The following line is a more complete command:

Code: Select all

LOAD DATA INFILE '/tmp/postcodes.csv' INTO TABLE i_city COLUMNS TERMINATED BY ';' ENCLOSED BY '"';