{"id":13271,"date":"2014-04-29T14:40:57","date_gmt":"2014-04-29T09:10:57","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=13271"},"modified":"2014-04-29T19:52:55","modified_gmt":"2014-04-29T14:22:55","slug":"how-to-save-mysql-query-results-into-a-text-or-csv-file-in-your-local-machine","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/how-to-save-mysql-query-results-into-a-text-or-csv-file-in-your-local-machine\/","title":{"rendered":"How to Save MySQL query results into a text or CSV file in your local machine"},"content":{"rendered":"<p style=\"text-align: justify;\">MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the <strong>INTO OUTFILE<\/strong> nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other application which accepts data in CSV format.<\/p>\n<p>In your terminal<\/p>\n<p>Given a query such as<\/p>\n<p><strong>SELECT * FROM pet;<\/strong><\/p>\n<p style=\"text-align: justify;\">which returns all columns of data, the results can be placed into the file \/tmp\/pet.txt using the query:<strong><\/strong><\/p>\n<p style=\"text-align: justify;\"><strong>SELECT * FROM pet<\/strong><br \/>\n<strong>INTO OUTFILE &#8216;\/tmp\/pet.txt&#8217;<\/strong><\/p>\n<p>This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:<\/p>\n<p><strong>SELECT * FROM pet<\/strong><br \/>\n<strong>INTO OUTFILE &#8216;\/tmp\/pet.csv&#8217;<\/strong><br \/>\n<strong>FIELDS TERMINATED BY &#8216;,&#8217;<\/strong><br \/>\n<strong>LINES TERMINATED BY &#8216;\\n&#8217;<\/strong><\/p>\n<p>In this example,the fields will be separated by commas, and each row will be output on a new line separated by a newline (\\n). Sample output of this command would look like:<\/p>\n<p><strong>Fluffy,Harold,cat,f,1993-02-04,0000-00-00<\/strong><br \/>\n<strong>Claws,Gwen,cat,m,1994-03-17,0000-00-00<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other application [&hellip;]<\/p>\n","protected":false},"author":114,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":7},"categories":[1395],"tags":[1197,1398],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/13271"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/114"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=13271"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/13271\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=13271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=13271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=13271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}