{"id":293732,"date":"2021-07-08T12:00:00","date_gmt":"2021-07-08T09:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it\/"},"modified":"2021-07-08T12:00:00","modified_gmt":"2021-07-08T09:00:00","slug":"how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it\/","title":{"rendered":"#How to Export a CSV file from MySQL Command Line \u2013 CloudSavvy IT"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2920aa7afa0\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2920aa7afa0\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it\/#From_The_Database_Server\" >From The Database Server<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it\/#From_The_MySQL_Command_Line\" >From The MySQL Command Line<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-export-a-csv-file-from-mysql-command-line-cloudsavvy-it\/#Do_It_Manually_With_a_Real_Programming_Language\" >Do It Manually With a Real Programming Language<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Export a CSV file from MySQL Command Line \u2013 CloudSavvy IT&#8221;<\/strong><\/p>\n<div id=\"article-content-area\">\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage  alignnone wp-image-636 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2019\/06\/5d7b15b7-1.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Transporting data between applications.\" width=\"700\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Comma Separated Values files (CSV) are a way of transferring data between <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>lications. Databases like MySQL and spreadsheet software like Excel support importing and exporting through CSV, so you can use <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/348960\/what-is-a-csv-file-and-how-do-i-open-it\/\">CSV files<\/a> to exchange data between the two.<\/p>\n<p>CSV files are plaintext, so they\u2019re naturally lightweight and easy to export from MySQL.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"From_The_Database_Server\"><\/span>From The Database Server<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you have access to the server that MySQL is running on, you can export a selection with the <code>INTO OUTFILE<\/code>\u00a0command.<\/p>\n<pre>SELECT id, column1, column2 FROM table&#13;\nINTO OUTFILE '\/tmp\/mysqlfiles\/table.csv'&#13;\nFIELDS TERMINATED BY ','&#13;\nENCLOSED BY '\"'&#13;\nLINES TERMINATED BY 'n'<\/pre>\n<p>This will output a CSV file to <code>\/tmp\/mysqlfiles\/table.csv<\/code>, or wherever you configured it to. You\u2019ll need to make sure the user running MySQL (usually <code>mysql<\/code>\u00a0or <code>root<\/code>) has ownership and write access to the directory.<\/p>\n<p>You\u2019ll also need to make sure the <code>secure_file_priv<\/code>\u00a0setting allows MySQL to access that directory. This, by default, blocks read and write access from SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker would only have access to MySQL, and not the rest of the filesystem.<\/p>\n<p>You can whitelist specific directories by editing your MySQL config file (usually located at <code>\/etc\/my.cnf<\/code>) to include:<\/p>\n<pre>[mysqld]&#13;\nsecure-file-priv = \"\/tmp\/mysqlfiles\"<\/pre>\n<p>Which will allow MySQL to read and write to <code>\/tmp\/mysqlfiles\/<\/code>\u00a0(which you\u2019ll have to create with <code>mkdir<\/code>). Once MySQL can export files, you should be able to run the query and output CSV files.<\/p>\n<p>With the <code>ENCLOSED BY<\/code>\u00a0setting, commas will be properly escaped, e.g.,:<\/p>\n<pre>\"3\",\"Escape, this\",\"also, this\"<\/pre>\n<p>Which you can take and import directly into any spreadsheet program or other software.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" alignnone wp-image-625 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2019\/06\/287b6d70.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Spreadsheet of a comma-separated list.\" width=\"700\" height=\"200\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Keep in mind that the exported CSV file doesn\u2019t include column headings, but the columns will be in the same order as the <code>SELECT<\/code>\u00a0statement. Also, null values will be exported as <code>N<\/code>, which is expected behavior, but if you\u2019d like to change this you can modify the selection by wrapping\u00a0<code>ifnull(field, \"\")<\/code>\u00a0around your fields in your <code>SELECT<\/code>\u00a0statement.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"From_The_MySQL_Command_Line\"><\/span>From The MySQL Command Line<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you only have command line access to the MySQL instance, and not access to the server itself (such as when it\u2019s not managed by you, in the case of <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/aws.amazon.com\/rds\/?tag=reviewgeek-20\">Amazon RDS<\/a>), the problem is a little trickier. While you can use <code>FIELDS TERMINATED BY ','<\/code>\u00a0on the server to generate a comma-separated list, the MySQL CLI will separate with tabs by default.<\/p>\n<p>Simply enter a query from the command line, and pipe it to a file:<\/p>\n<pre>mysql -u root -e \"select * from database;\" &gt; output.tsv<\/pre>\n<p>Because MySQL output is separated with tabs, this is called a TSV file, for \u201ctab-separated values,\u201d and may work in place of your CSV file in some programs like spreadsheet imports. But it isn\u2019t a CSV file, and converting it to one is complicated.<\/p>\n<p>You could simply replace each tab with a comma, which would work but would cause it to fail if there are commas in the input data. If you\u2019re entirely 100% certain that there are no commas in your TSV file (check with <code>grep<\/code>), you can replace the tabs with <code>sed<\/code>:<\/p>\n<pre>sed \"s\/t\/,\/g\" output.tsv &gt; output.csv<\/pre>\n<p>But if you have commas in your data, you\u2019ll have to use a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/stackoverflow.com\/a\/5395421\">much longer<\/a> regex:<\/p>\n<pre>sed \"s\/'\/'\/;s\/t\/\",\"\/g;s\/^\/\"\/;s\/$\/\"\/;s\/n\/\/g\"\u00a0output.tsv &gt; output.csv<\/pre>\n<p>This will properly escape fields with quotation marks, which will solve the comma problem.<\/p>\n<p>Note: the tab character\u00a0<code>t<\/code>\u00a0is <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/unix.stackexchange.com\/a\/145385\">not standard<\/a>. On macOS and BSD, it\u2019s not available, which leads to a mess of every lowercase \u201ct\u201d causing <code>sed<\/code> to insert erroneous commas. To solve this, you\u2019ll need to use a literal tab character in place of <code>t<\/code>:<\/p>\n<pre>sed \"s\/  \/,\/g\" output.tsv &gt; output.csv<\/pre>\n<p>If your input data contains tabs, you\u2019re out of luck, and will have to generate a CSV file yourself with a scripting language.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Do_It_Manually_With_a_Real_Programming_Language\"><\/span>Do It Manually With a Real Programming Language<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MySQL (and most databases) are designed to be interacted with, so you likely have some sort of programming language connected to MySQL already. Most languages can also write to disk, so you can create your own CSV output scripts by reading fields from the database directly, escaping them properly, and writing a comma-delimited file.<\/p>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/stackoverflow.com\/a\/41840534\">An example in Python<\/a>.\n<\/div>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.cloudsavvyit.com\/621\/how-to-export-a-csv-file-from-mysql-command-line\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Export a CSV file from MySQL Command Line \u2013 CloudSavvy IT&#8221; Comma Separated Values files (CSV) are a way of transferring data between applications. Databases like MySQL and spreadsheet software like Excel support importing and exporting through CSV, so you can use CSV files to exchange data between the two. CSV files are&#8230;<\/p>\n","protected":false},"author":1,"featured_media":293733,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2019\/06\/5d7b15b7-1.png","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-293732","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/293732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=293732"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/293732\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/293733"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=293732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=293732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=293732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}