Importing and exporting csv files in MySQL

To import/output csv you need some permissions. As root on mysql run the following code:


> grant all privileges
on YOUR_DATABASE.*
to 'YOUR_USER'@'localhost'
identified by 'your_password';
> GRANT FILE ON *.* TO 'YOUR_USER'@'localhost';
> flush privileges;

Exporting CSV from MySQL

For security reasons mysql can only export files to a limited number of directories, this is especially true with Ubuntu which now uses AppArmor. You can change permissions to get around the security restrictions, but it is a major headache. It is much easier to export the file to /tmp and then move it.

To export into the /tmp directory use the following MySQL command. Make sure to replace “database.table_name” with your database and table name.

select * into outfile '/tmp/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' from database.table_name;

Now move the file from /tmp to your favorite directory:


cp /tmp/outfile.csv /home/kami/data/

Unfortunately there is no way to remove /tmp/outfile.csv without using sudo.

Importing a csv file into MySQL

For security reasons the ability to load files from the local directory is disabled by default. To temporarily override this restriction use the “–local-infile=1” parameter when starting MySQL.


mysql --local-infile=1 -u USERNAME -p DATABASE

Lets say my csv file out.csv looked like the following:

a,b,c
1,2,3
2,2,3
2,3,4

The MySQL table I want to load the file into is called my_table and has columns a,b,c,d. I would use the following MySQL command.


LOAD DATA local INFILE '/home/kami/outfile.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(a,b,c);

This command will load the csv file ‘/home/kami/outfile.csv’ into the columns a, b and c in table my_table. It will also ignore the first row.

Further Reading

  • http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile
  • http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold

4 Responses to Importing and exporting csv files in MySQL

  1. Anonymous says:

    thanks, very helpful. Reminder to Windows users to use “\\” on your file path or it won’t work

  2. sateesh says:

    Hi all, The above given information is helped me a lot… its worked fine..

    but i want to import excel file into database. for that what i need to replace near

    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    ESCAPED BY ‘\\’
    LINES TERMINATED BY ‘\n’ in the above code…

    thanks in advance…

  3. kami says:

    It is impossible to import an Excel file if it is still saved as an .xls or .xlsx file. You have to select “Save As” and save it as a csv file. Then the instructions should work just fine.

  4. Kolombo says:

    This has been unbelievably usfuel, after I rooted around for a couple of hours trying to do this. The key fact, that a downloaded .csv list becomes a Group with the date as its name, is something that should be at the top of the Google Import page.Marshall in Baltimore

Leave a Reply

Your email address will not be published.