Introduction to MySQL Export to CSV
The following article provides an outline for MySQL Export to CSV. In MySQL, we store the data in the database in table format. Many times, there is a necessity to get the data stored in the tables of MySQL database in some other format such as CSV, JSON, or SQL format. This is often the requirement when we do data transfer and sharing between multiple companies and individuals. If we have exported data of the database and its table contents in CSV format then this data can be further used in other platforms such as google docs, open office or Microsoft Excel for further analysis of resultset and concluding the necessary statistics by performing various operations on the exported data to get certain conclusions.
In this article, we will see how we can make the use of the INTO OUTFILE statement to export the data of MySQL database and its contents to any CSV format using the command-line MySQL shell and we can export the data of MySQL to CSV format when we are using the MySQL Workbench editor with the help of an example.
Exporting Using command-line MySQL Queries
Before exporting to CSV format, we need to make sure that no other file with the same name as mentioned in the query exists on the same path as used, and also the server of MySQL has to write permissions on that path or folder specified where our CSV file will be saved.
Consider the following query statement that retrieves the data of developers whose name begins with R.
Code:
select * from developers where name like "R%";
Output:
To export resultset to CSV file we will make the use of INTO OUTFILE clause as shown below.
Code:
select * from developers where name like "R%" INTO OUTFILE '/var/lib/mysql-files/R_developers_cmd.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Output:
Your exported CSV file will look as follows when opened in Microsoft Excel of LibreOffice calc.
4.5 (5,030 ratings)
View Course
You can see that only the data is exported but column headings are not exported. In case, if you want to export the heading names of the column then you can use the following query statement.
Code:
select 'developer_id', 'team_id', 'name', 'position', 'technology', 'salary' UNION select * from developers where name like "R%" INTO OUTFILE '/var/lib/mysql-files/R_developers_cmd_with_headings.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Output:
Your exported CSV file will look as follows when opened in Microsoft Excel of LibreOffice calc.
Exporting Data to any other Format in Workbench
You can export the table contents and structure by going to the schemas tab mentioned in the left-sidebar of the Workbench tool. Then you can right-click on the table of the database you wish to export and then click on “Table Data Export Wizard” which will lead to the opening of the export wizard as shown in the below image. Where you will see the list of all the columns of that table that are auto-selected for exporting. You can further choose the columns that you want to export by changing the selected columns and then click on the Next button in the bottom button as shown below.
If you click on the advanced button below, you can type the equivalent query whose resultant data and the content will be exported. After clicking on next button you can choose the path where the file needs to be exported and type of format in which you want to export either CSV or JSON and in case of CSV, you can select the options associated with data to be exported and specifying different properties that need to be applied while exporting data such as field separator to be considered and NULL word interpretation, etc as shown below.
After clicking on Next, if the same file with same name and format exists on the specified path it asks that whether you want to overwrite the file or else just mentions the steps that will be followed and executed while exporting as shown below.
After clicking on next you will see a message saying that the file has been exported successfully as shown below.
You can observe that the exported file in the desired format will be created in the specified path after running the export process successfully.
Exporting the Query Resultset
There is also the provision to export the resultset of any select query that is executed in Workbench by simply clicking on the export button and then specifying the path and name of the file. Note that in MySQL using the command-line, we can export the results of the query by using the INTO OUTFILE statement. Let us look at how we can export the resultset of the query in workbench with the help of an example.
Consider the following select query that retrieves the names of all developers that begin with letter R as shown below.
Code:
select * from developers where name like "R%";
Output:
Now, we can click on the export button that turns into opening the files and we can choose the path and name of the file by typing in it as shown below.
You can choose the format of the report from CSV, HTML, XML, JSON, SQL, or Excel whichever you want from the choice list provided below in the left-hand corner. We will choose the CSV option as we want to export the data to CSV format. Simply, click on the save and your file will be exported with the retrieved result set.
Conclusion
We can export the files using the command-line using MySQL query statements and even using various options provided in the MySQL Workbench client tool. We can specify the carriage return values, file and line separators, and even the value with which the column value should be enclosed by in both of the above methods.
Recommended Articles
This is a guide to MySQL Export to CSV. Here we discuss exporting using command-line MySQL queries, exporting data to any other format in workbench and exporting the query resultset. You may also have a look at the following articles to learn more –
