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:
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 '"';