{"id":3178,"date":"2014-02-15T08:38:46","date_gmt":"2014-02-15T16:38:46","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3178"},"modified":"2023-12-28T02:19:59","modified_gmt":"2023-12-28T09:19:59","slug":"mysql-export-table-to-csv","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-export-table-to-csv\/","title":{"rendered":"MySQL Export Table to CSV"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various techniques for exporting a MySQL table to a CSV file.<\/p>\n\n\n\n

The CSV stands for comma-separated values.\u00a0The CSV file format is often used to exchange data between applications such as Microsoft Excel, Open Office, Google Docs, and so on.<\/p>\n\n\n\n

Having data from the MySQL database in CSV file format will be useful because you can analyze and format the data in the way you want.<\/p>\n\n\n\n

MySQL provides an easy way to export the query\u2019s result into a CSV file that resides on the database server.<\/p>\n\n\n\n

1) Exporting a table to a CSV file using SELECT … INTO OUTFILE statement<\/h2>\n\n\n\n

We’ll illustrate how to export the orders table from the classicmodels<\/code> sample database<\/a> into a CSV file located on the MySQL Server.<\/p>\n\n\n\n

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, change the current database to classicmodels<\/code>:<\/p>\n\n\n
USE<\/span> classicmodels;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, show the value of the secure_file_priv<\/code> variable:<\/p>\n\n\n
SHOW<\/span> VARIABLES<\/span> LIKE<\/span> \"secure_file_priv\"<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
On Windows server, the output is like the following:<\/p>\n\n\n
+------------------+------------------------------------------------+\r\n| Variable_name    | Value                                          |\r\n+------------------+------------------------------------------------+\r\n| secure_file_priv | C:\\ProgramData\\MySQL\\MySQL Server 8.0<\/span>\\Uploads\\ |\r\n+------------------+------------------------------------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.02 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
On Ubuntu, the output will look like:<\/p>\n\n\n
\r+------------------+-----------------------+\r\n| Variable_name    | Value                 |\r\n+------------------+-----------------------+\r\n| secure_file_priv | \/var\/<\/span>lib\/mysql-files\/ |\r\n+------------------+-----------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The secure_file_priv<\/code> setting indicates the directory where you are allowed to store the output file.<\/p>\n\n\n\n
Finally, retrieve data from the orders<\/code> table and export the result set into the orders.csv<\/code> file:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> orders\nINTO<\/span> OUTFILE<\/span> 'C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/orders.csv'<\/span> \nFIELDS<\/span> ENCLOSED<\/span> BY<\/span> '\"'<\/span> \nTERMINATED<\/span> BY<\/span> ','<\/span> \nESCAPED<\/span> BY<\/span> '\"'<\/span> \nLINES<\/span> TERMINATED<\/span> BY<\/span> '\\r\\n'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the orders.csv<\/code> file must not exist in the directory C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/. Otherwise, the statement will issue an error:<\/p>\n\n\n
ERROR 1086<\/span> (HY000): File 'C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/orders.csv'<\/span> already exists<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, retrieve data from the orders<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> orders<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, specify the CSV output file (orders.csv) stored in the allowed directory (C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads) in the INTO OUTFILE clause:<\/p>\n\n\n
INTO OUTFILE 'C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/orders.csv' <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice you have to replace the backslash \\<\/code> by the forward slash \/<\/code> in the directory on Windows to make it work.<\/p>\n\n\n\n
Third, define the format of the output file as CSV by specifying how fields and lines in the output are formatted:<\/p>\n\n\n
FIELDS ENCLOSED BY '\"' \nTERMINATED BY ',' \nESCAPED BY '\"' \nLINES TERMINATED BY '\\r\\n';<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the detail of each option:<\/p>\n\n\n\n