Connecting Excel to MySQL

Devart Excel Add-in for MySQL allows you to connect Excel to MySQL databases, retrieve and load live MySQL data to Excel, and then modify these data and save changes back to MySQL. Here is how you can connect Excel to MySQL and load MySQL data to Excel in few simple steps.

To start linking Excel to MySQL, on the ribbon, click the DEVART tab and then click the Get Data button. This will display the Import Data wizard, where you need to create Excel MySQL connection and configure query for getting data from MySQL to Excel:

1. Specify Connection Parameters

In the Connection Editor dialog box, you need to enter the necessary connection parameters:

Host - the name or IP address of a computer, MySQL server is running on.
Port - the port of MySQL server to connect to.
User Id - your MySQL user name.
Password - your MySQL password.
Database - the name of SQL database to connect to Excel.

If you need to configure your Excel MySQL connector in more details, you can optionally click the Advanced button and configure advanced connection parameters. There you can configure connection encryption, resiliency parameters, etc.

To check whether you have connected Excel to MySQL your connection correctly, click the Test Connection button.

2. Select whether to Store Connection in Excel Workbook

You may optionally change settings how the connection and query data are stored in the Excel workbook and in Excel settings:

Allow saving add-in specific data in Excel worksheet - clear this check box in case you don't want to save any Excel add-in specific data in the Excel worksheet - connections, queries, etc. In this case, if you want to reload data from MySQL to Excel or save modified data back to MySQL, you will need to reenter both the connection settings and query.
Allow saving connection string in Excel worksheet - clear this check box if you want your MySQL connection parameters not to be stored in the Excel. In this case you will need to reenter your connection settings each time you want to reload MySQL data or modify and save them to MySQL. However, you may share the Excel workbook, and nobody will be able to get any connection details from it.
Allow saving password - it is recommended to clear this check box. If you don't clear this check box, all the connection settings, including your MySQL password, will be stored in the Excel workbook. And anyone having our Excel Add-in for SQL Server and the workbook will be able to link Excel to the MySQL, get data from it, and modify them. But in this case you won't need to reenter anything when reloading data from MySQL to Excel or saving them to MySQL.
Allow reuse connection in Excel - select this check box if you want to save this connection on your computer and reuse it in other Excel workbooks. It does not affect saving connection parameters in the workbook itself. You need to specify the connection name, and after this you will be able to simply select this connection from the list

3. Configure Query to Get Data

You may either use Visual Query Builder to configure it visually, or switch to the SQL Query tab and type the SQL Query. To configure query visually, do the following:

In the Object list select the MySQL table to load its data to Excel.
In the tree below clear check boxes for the columns you don't want to import data from.
Optionally expand the relation node and select check boxes for the columns from the tables referenced by the current table's foreign keys to add them to the query.
In the box on the right you may optionally configure the filter conditions and ordering of the imported data and specify the max number of rows to load from MySQL to Excel. For more information on configuring the query you may refer to our documentation, installed with the Excel Add-ins.

After specifying the query, you may optionally click Next and preview some of the first returned rows. Or click Finish and start data loading.

Query for retrieving MySQL data to Excel

Editing Live MySQL Data

After the data is loaded from MySQL to Excel, you can work with these data like with usual Excel worksheet. You can instantly refresh data from MySQL by clicking Refresh on the Devart tab of the ribbon, and thus, always have fresh live data from MySQL in your workbook.

If you want to edit MySQL data in Excel and save them back to MySQL, you need to click Edit Mode on the Devart tab of the ribbon first. Otherwise, the changes you make cannot be saved to MySQL.

After you start the Edit mode, you can edit the data as you usually do it in excel - delete rows, modify their cell values. Columns that cannot be edited in MySQL, will have Italic font, and you cannot edit values in these columns. To add a new row, enter the required values to the last row of the table that is highlighted with green.

To apply the changes to actual data in the database, click the Commit button. Or click Rollback to rollback all the changes. Please note that the changes are not saved to the database till you click Commit, even if you save the workbook.