EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Export to CSV
 

MySQL Export to CSV

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 26, 2023

MySQL Export to CSV

 

 

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. You often need to retrieve the data stored in the tables of a MySQL database in some other form, such as CSV, JSON, or SQL format. This is often required when we transfer data and share between multiple companies and individuals. Suppose we have exported data of the database and its table contents in CSV format. In that case, this data can be further used in other platforms such as google docs, open office, or Microsoft Excel for further analysis of the resultset and concluding the necessary statistics by performing various operations on the exported data to get certain conclusions.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will see how we can make 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 MySQL to CSV

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:

MySQL Export to csv 1

To export the resultset to a CSV file, we will use the INTO OUTFILE clause, as shown below.

Code:

select * from developers where name like "R%" INTO OUTFILE 'https://cdn.educba.com/var/lib/mysql-files/R_developers_cmd.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Output:

MySQL Export to csv 2

When opened in Microsoft Excel of LibreOffice calc, your exported CSV file will look as follows.

opened in Microsoft Excel of LibreOffice calc

You can see that only the data is exported, but column headings are not exported. If you want to export the heading names of the column, 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 'https://cdn.educba.com/var/lib/mysql-files/R_developers_cmd_with_headings.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Output:

MySQL Export to csv 4

When opened in Microsoft Excel of LibreOffice calc, your exported CSV file will look as follows.

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 in the Workbench tool’s left sidebar. 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 choose the columns you want to export by changing the selected columns and then clicking on the Next button at the bottom, as shown below.

MySQL Export to csv 6

If you click the advanced button below, you can type the equivalent query whose resultant data and content will be exported. After clicking on the following button, you can choose the path where the file needs to be exported and the type of format in which you want to export, either CSV or JSON and in the 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.

MySQL Export to csv 7

When you click Next, if the same file with the same name and format already exists on the chosen path, a question appears asking if you wish to overwrite the file. If not, it lists the actions that will be taken and done during exporting, as shown below.

overwrite the file

After clicking next, you will see a message that the file has been exported successfully, as shown below.

MySQL Export to csv 9

After successfully running the export process, you can observe that the program will create the exported file in the specified path in the desired format.

Exporting the Query Resultset

There is also the provision to export the resultset of any select query 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 query results by using the INTO OUTFILE statement. Let us look at how we can export the resultset of the query in the workbench with the help of an example.

Consider the following select query that retrieves the names of all developers that begin with the letter R, as shown below.

Code:

select * from developers where name like "R%";

Output:

MySQL Export to csv 10

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.

button that turns into opening the files

You can choose the report format 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 to export the data to CSV format. 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, MySQL query statements, and 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 in both methods.

Recommended Articles

We hope this EDUCBA information on “MySQL Export to CSV” benefited you. You can view EDUCBA’s recommended articles for more information.

  1. Natural Join in MySQL
  2. MySQL ROW_NUMBER()
  3. DATE_SUB() MySQL
  4. MySQL DECODE()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW