How do I load a csv file in MYSQL

Post Reply
Peter_w
Posts: 28
Joined: Tue Aug 19, 2008 9:28 am

How do I load a csv file in MYSQL

Post 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.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: How do I load a csv file in MYSQL

Post 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
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

Re: How do I load a csv file in MYSQL

Post 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)
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: How do I load a csv file in MYSQL

Post 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;
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: How do I load a csv file in MYSQL

Post 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 '"';
Post Reply