{"id":524,"date":"2012-05-26T17:09:36","date_gmt":"2012-05-26T21:09:36","guid":{"rendered":"http:\/\/kamivaniea.com\/?p=524"},"modified":"2012-11-02T16:33:20","modified_gmt":"2012-11-02T20:33:20","slug":"importing-and-exporting-csv-files-in-mysql","status":"publish","type":"post","link":"https:\/\/kamivaniea.com\/?p=524","title":{"rendered":"Importing and exporting csv files in MySQL"},"content":{"rendered":"

To import\/output csv you need some permissions. As root on mysql run the following code:<\/p>\n


\n> grant all privileges
\n on YOUR_DATABASE.*
\n to 'YOUR_USER'@'localhost'
\n identified by 'your_password';
\n> GRANT FILE ON *.* TO 'YOUR_USER'@'localhost';
\n> flush privileges;
\n<\/code><\/p>\n

Exporting CSV from MySQL<\/h3>\n

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<\/a>, but it is a major headache. It is much<\/strong> easier to export the file to \/tmp and then move it. <\/p>\n

To export into the \/tmp directory use the following MySQL command. Make sure to replace “database.table_name” with your database and table name.
\n
\nselect * into outfile '\/tmp\/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' from database.table_name;
\n<\/code><\/p>\n

Now move the file from \/tmp to your favorite directory: <\/p>\n


\ncp \/tmp\/outfile.csv \/home\/kami\/data\/
\n<\/code><\/p>\n

Unfortunately there is no way to remove \/tmp\/outfile.csv without using sudo. <\/p>\n

Importing a csv file into MySQL<\/h3>\n

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.<\/p>\n


\nmysql --local-infile=1 -u USERNAME -p DATABASE
\n<\/code><\/p>\n

Lets say my csv file out.csv looked like the following:
\n
\na,b,c
\n1,2,3
\n2,2,3
\n2,3,4
\n<\/code><\/p>\n

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. <\/p>\n


\nLOAD DATA local INFILE '\/home\/kami\/outfile.csv'
\nINTO TABLE my_table
\nFIELDS TERMINATED BY ','
\nENCLOSED BY '\"'
\nESCAPED BY '\\\\'
\nLINES TERMINATED BY '\\n'
\nIGNORE 1 LINES
\n(a,b,c);
\n<\/code><\/p>\n

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. <\/p>\n

Further Reading<\/h3>\n
    \n
  • http:\/\/stackoverflow.com\/questions\/2783313\/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile\n
  • http:\/\/stackoverflow.com\/questions\/6091427\/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[7],"tags":[48],"class_list":["post-524","post","type-post","status-publish","format-standard","hentry","category-application-debug","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2u4LH-8s","jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/posts\/524","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=524"}],"version-history":[{"count":9,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/posts\/524\/revisions"}],"predecessor-version":[{"id":628,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=\/wp\/v2\/posts\/524\/revisions\/628"}],"wp:attachment":[{"href":"https:\/\/kamivaniea.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=524"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=524"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kamivaniea.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=524"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}