For most people, the initial contact with the world of data is made using Microsoft Excel. Be it through a school statistics class or individual exploration of the programs installed on your PC, Excel was probably where you first saw data being stored in tables, transformed using formulas, and presented visually through charts, graphs, etc.
Thanks to its ease of use and wide range of features, Excel has become a staple worldwide. However, in organizations that handle large amounts of data, Excel is used to analyze, create reports, run calculations, and so on, while database management systems like MySQL are used to store and manage that data.
So, how do you export data from MySQL to Excel and make the most out of both tools in your data-related tasks? We’ll introduce you to several ways in the text below!
What are the common ways to export MySQL to Excel?
MySQL to Excel data exports are not a new thing, which means there are several ways of completing them. Here are the most common:
- Automatic (and scheduled) data exports with Coupler.io
- SELECT INTO OUTFILE statement
- Get Data feature in Excel
- MySQL for Excel add-in
- PHP or Java code
What is the easiest and most time-efficient way to export MySQL to Excel?
Getting a task done feels great. But what feels even better is saving time and effort while you are at it.
When it comes to exporting data from MySQL to Excel, Coupler.io is the tool that can make that happen.
You see, Coupler.io not only completes the export but also allows you to set a custom schedule for the automatic refreshing of the data, all without requiring any coding. So, if you have only a couple of minutes to do a data export and are not willing to do more than a couple of clicks, Coupler.io is the solution for you (or anyone wanting to do things the smart way 😉 )
With that said, let’s see Coupler.io in action, shall we?
To kick things off, you need to sign up for Coupler.io using your Google, Xero, or Microsoft account. If you don’t have one, no worries; just an email and password will do.
Once you have signed up, the first page you see will be the Importers page. On the page, click on ADD NEW IMPORTER. This should redirect you to where you can start configuring the importer by selecting a data source and a data destination.
Our selection will be MySQL for the source and Excel for the destination.
For each, we need to set up a connection.
MySQL – the source
In the data source section, under Source account, click on CONNECT.
This should open up a pop-up window where you need to enter the details for connecting to a MySQL account, such as the host, port, user, and password.
With that done, the last thing you need to define for the data source is the name of the database and the table/view you want to export.
Excel – the destination
In the data destination section, you first need to connect to a Microsoft account. Bear in mind that this account needs to have edit rights in the destination file.
Next, you will be prompted to select a workbook and sheet to which the data will be imported. When it comes to the sheet, you can use an existing one or create a new sheet by typing in a new name.
If you wish, you can also specify a cell address and import mode, but this is entirely optional.
Scheduling the refreshing of data
As mentioned earlier, one of the many benefits of Coupler.io is the automatic refreshing of data that is done according to a schedule you set. In other words, if you need the data in your export to be refreshed, let’s say, every hour, on specific days of the week, and at a specific time, Coupler.io will do it for you automatically. All you need to do is turn on the feature and define the interval, days of the week, time, and time zone.
Once you have both of the connections set up and the automatic refreshing of data scheduled (if necessary), the last thing left to do is click on SAVE AND RUN. Coupler.io will then run your importer and show you the results.
What are the standard ways to export MySQL to Excel?
If you like to do things the manual way and have a bit of time to spare, there are alternative ways you can go about exporting MySQL to Excel.
Running the SELECT INTO OUTFILE statement to export MySQL table to Excel
SELECT INTO OUTFILE is a MySQL statement commonly used to export data to different file formats such as .csv, .txt, and more.
To run the statement, you need a software tool that will provide you with a query editor/builder. One such tool is ApexSQL Database Power Tools for VS Code.
When writing your SELECT INTO OUTFILE statement, you will need to specify which data should be exported, the location, and the file format of the export.
Below is an example of the one we would use to export all the data from a table in our database into a .csv file.
You can learn more about writing your SELECT INTO OUTFILE statement in the official MySQL documentation.
Note: Before running the statement in a query editor, ensure you have at least one of the necessary file privileges; otherwise, you will receive an error message.
Upon running SELECT INTO OUTFILE, you will find the exported data in the specified location and file.
Now, to get that data into Excel, you need to go into the tab labeled Data and click on the From Text/CSV feature.
A dialog box should appear through which you need to locate the file with the exported data and then click on Import.
In the next dialog box you see, choose a delimiter and click on Transform Data.
Lastly, in the Power Query Editor dialog box, configure any other things you’d like, such as the column names.
Once you are all set, click on Close & Load to finalize the process and see your data loaded in a new sheet.
Installing the MySQL for Excel add-in to connect MySQL to Excel
If you are someone that likes to do all Excel-related tasks entirely within Excel, you are in luck, as there is an add-in that will enable you to connect MySQL to Excel and import/export data.
The name of the add-in is MySQL for Excel. It is Windows-based, and you can download it at the following link.
Note: To install the MySQL for Excel add-in, you must install Microsoft Visual Studio with .NET Framework and Visual Studio 2010 Tools for Office Runtime beforehand. This is not recommended for non-tech-savvy users.
After fulfilling all the prerequisites and installing the add-in, it will appear in the top right-hand corner of the Data tab.
To start using it, simply click on its icon and then on New Connection to begin establishing the connection between MySQL and Excel. For this, you will need to enter a few details, such as the connection name, method, hostname, port, username, and password.
The connection will be established once you click on OK if all the details you entered are correct.
To start a data import using the connection, click on it and then pick the database and table you want to import from. Next, click on Import MySQL Data to get the add-in working.
The last step is clicking on Import in the dialog box that appears.
And that is it! The Excel sheet in front of you should now be populated with data from your desired MySQL server.
Making use of the Get Data feature in Excel
In terms of built-in features, Excel has plenty and among them is one used to import data from different sources called, you guessed it, Get Data.
Note: For you to use the Get Data feature to import data from a MySQL database, you will need the MySQL Connector/ODBC driver, which itself requires Visual Studio 2019 x64 Redistributable. Along with that, it’s important to note that a number of users have reported issues even after the driver installation. Not recommended for non-tech-savvy users.
This feature, just like the MySQL for Excel add-in, is located in the Data tab, and when clicked on, it displays a dropdown menu with a list of data sources.
The data source we will go with is From Database. Hover over it, and you will be shown another dropdown menu listing different database types. Click on From MySQL Database to proceed.
You will be asked to enter the server and database details first, then the username and password. Once done, click on Connect.
If the connection to MySQL is successful, you will be greeted with a navigator for choosing the data you want to import.
After that, you can transform the data if necessary or load it right away in an Excel sheet.
Taking the coding route
In case a few coding lovers have stumbled upon this article, we have good and bad news. The good news – yes, you can export MySQL to Excel using code in PHP, Java, and other languages. The bad news – the coding route for the majority of people is the most complex one, so we won’t be demonstrating it here as it alone deserves a dedicated article (or two). Instead, we will briefly explain the process and requirements for doing the export in PHP and Java so you can decide if you are up for the challenge.
Sounds good? Let’s jump right into it!
Exporting data from MySQL to Excel using PHP
A PHP script is what you will be using to export MySQL table to Excel or even an entire database using the language.
For those early in their PHP coding journey, a script is a sequence of instructions carried out by a program, not the CPU.
The script needed for this task should define a database connection object to retrieve the data, a SELECT statement to read the data, as well as a variable to display the data, and should create an Excel file to which it needs to import data.
For more information on the process, we suggest you check out this coding tutorial or a different one fit for your needs.
Export MySQL data to Excel in Java
If Java is your language of choice and you want to use it to connect MySQL to Excel, you will need the Apache POI library. Using the library, within the Spring Boot framework, you will be completing steps that include adding dependencies and configurations, creating an entity class, a JPA Repository class, a service class, a web controller, a config class, as well as a custom exception handling class, and enabling JPA auditing features.
Seems like a piece of cake to you? You can find clear instructions on the process in this article.
What is the best way to export MySQL to Excel?
As you have seen, there are a plethora of ways to export MySQL data to Excel. Depending on your technical skills, there could be even more ways for you to explore that just didn’t fall under the scope of this article.
Now, for the million-dollar question – what is the best way to export MySQL data to Excel?
This heavily depends on how much you value your time and effort. Solutions like Coupler.io get things done for you and thus save a significant amount of time and effort you would otherwise have to put in. Without Coupler.io, doing all the work in making the export happen is on you. Plus, you don’t get to enjoy benefits such as automatic data refreshing, an array of different data sources and destinations, and no coding knowledge requirements.
That being said, what you need to do now is examine your needs and competencies to make the right choice. Good luck!Back to Blog