How To - Guide to editing MySQL data within Excel
By Javier Trevi�o-Oracle on Mar 31, 2014
In previous posts we have talked about importing data from MySQL to Excel, exporting data from Excel to a new or existing MySQL table (via the Export Data or Append Data operations) which are common tasks to be performed against MySQL tables. In this post we will talk about possibly the coolest feature in MySQL for Excel: the Edit Data operation. This feature allows the retrieval of a MySQL table's data to an Excel worksheet so the data can be edited, deleted or inserted in new rows and those changes saved back to the MySQL database in a friendly and intuitive way; and more importantly, without the need of writing SQL queries making it accessible to any kind of user.
You will learn in this guide how to do it and all the details about the Edit Data feature in MySQL for Excel.
This guide assumes you already have a working MySQL Server instance, Microsoft Office Excel 2007 or higher and MySQL for Excel installed
If you are already familiar with MySQL for Excel and have a connection to a MySQL server instance you can skip directly to step 4.
1. Opening MySQL for Excel
In Excel, go to the Data tab located in the Ribbon and click MySQL for Excel at the far right of the Ribbon.
2. Creating a MySQL Connection (may be optional)
If you have MySQL Workbench installed you will see the same connections that you have in MySQL Workbench, in that case there may be no need to create a new connection.
If you still want to create a new connection, in the Welcome Panel click New Connection, which opens the MySQL Instance Connection dialog. Here you only need to give your new connection a distinctive Connection Name, specify the Hostname (or IP address) where the MySQL Server instance is running on (if different than localhost), the Port to connect to and the Username and Password for the login.
Optionally if you wish to test if your setup is good to go, click Test Connection and an information dialog will pop-up stating if the connection is successful or errors were found.
3.Opening a connection to a MySQL Server
To open a pre-configured connection to a MySQL Server you just need to double-click it.
4. Selecting a MySQL Schema
After opening a connection to a MySQL Server, the Schema Selection Panel is shown, where you can select the Schema that contains the Tables, Views and Stored Procedures you want to work with. To do so, you just need to either double-click the desired Schema or select it and click Next >.
5. Edit MySQL Data
Data can be edited only from MySQL tables (not views since they are a read-only representation of 1 or more tables, and not procedures since they only return data calculated from 1 or more tables).
a. Select a MySQL table.
From the list of database objects select the desired MySQL table by clicking it then click on the Edit MySQL Data action label.
b. Preview the data in the selected table and import it.
The Import Data dialog will display at this point a preview of the data in the selected table, so you can decide at this point if indeed this is the table you want to edit, if so you can click on Import or on Cancel to abort the operation and select a different table.
c. Edit the data in the Excel worksheet in "Edit Mode".
The MySQL table data will be imported to a new Excel worksheet and a floating, semi-solid, black Edit Data dialog will appear to let you know that the Excel worksheet is now in "Edit Mode", meaning you can start changing the values in the Excel cells and MySQL for Excel will keep a record of the changes which may be later "pushed" back to the MySQL server.
When an Excel worksheet is in "Edit Mode" you will notice the following things:
- The worksheet name is the same as the MySQL table name, if before this new worksheet is created there is another one with the same name as the selected MySQL table, the worksheet name will be named like "Copy n of <table_name>". In the example of the screenshot above if there was already a worksheet named "actor" the new worksheet would have been named "Copy 1 of actor".
- The Edit MySQL Data action label gets disabled for the selected MySQL table, since there can be only 1 active "Edit Session" for that table.
- The Edit Data dialog only appears if the selected cell(s) are within the "Editing Range", if an Excel cell outside that range is selected the Edit Data dialog will disappear.
- Only the Excel cells within the region containing imported data is editable, if you try to change a value outside that range you will get an error stating that changes are not permitted, the Excel worksheet is protected to prevent changes outside the "Editing Range". If for some reason you need to edit cells outside the "Editing Range" you will need to exit the "Edit Session" (later on this post about how to exit the "Edit Session").
- The very first row containing the column names (Excel cells with a gray color) cannot be edited, if you try to change its contents you will get an error stating those cells are protected.
When you change some table's data, the Excel cells will turn blue indicating they contain changes that haven't been committed to the database.
New rows can be inserted using the yellow row just below the last row containing data, when any Excel cell on that yellow row is changed the row will turn blue (indicating uncommitted data) and the row just below will now become yellow and ready to accept data for another new table row.
If you want to delete a whole row from the MySQL table you need to delete the row as you would in Excel, i.e. by right-clicking the row heading and selecting from the pop-up context menu the Delete option.
To commit the changes to the MySQL database just click Commit Changes on the floating Edit Data dialog. The result of the commit will be displayed next, if an error occurred the submitted changes will not be applied and an error dialog will be shown containing the error message. MySQL for Excel creates a transaction for the submitted changes, so it is committed or rolled-back as a whole. Below you can see an example of an error thrown when trying to commit.
Note that the error message is stating the number 140 would be a duplicate entry for the primary key, hinting the problem is at a changed cell with a value of 140. You will see that any cells that produced an error will be colored in red, so you know you must correct the errors before attempting a new commit.
This error can only be corrected by reverting that value (140) back to its original one (142) since changing the value of a primary key is not allowed. If a value is reverted back to its previous value the Excel cell will be reverted back to its original background color (white). After the correction a new attempt to commit the changes to the database can be made. Let's review a scenario where there are further errors in this change set as you can see below.
In this particular example the error message was thrown because one of the changes sent to the database included a deleted row, judging by the error message you can notice there are foreign key constraints preventing the row from being deleted. In this scenario we need to undo the changes and apply them again without the deleted row. To undo the changes click Revert Data on the floating Edit Data dialog, from here there are 2 options to undo changes:
- Refresh Data from DB: undoes any uncommitted changes (blue cells) and pulls from the database a fresh copy of the table, this is useful when several persons may be updating the MySQL table's data at the same time since the contents of the table may have already changed from the time the data was imported to Excel for edition.
- Revert Changed Data: just undoes any uncommitted changes (blue cells) reverting the data back to the way it was at the time the data was imported to Excel for edition.
After the changes are reverted and you think there are no more things that can cause errors, click Commit Changes again. This time if there were no errors during the commit you should see an information dialog containing the results of the applied changes. If warnings were thrown by the server you will see them just below the operation results.
Notice the committed Excel cells turned green indicating they contain changed data that has been successfully committed to the MySQL table.
The floating Edit Data dialog contains an option called Auto-Commit, when that option is enabled all changes will be committed to the MySQL database right after they are done on the Excel cell(s), without any need to click Commit Changes. This can be useful when you are connected to a local MySQL database or a remote one on a fast connection where commits are fast and unnoticeable; otherwise if each commit lags for a period of time or you prefer to change a set of data and review it before committing, you may want to keep the option disabled.
If you want to exit the current "Edit Session" you can do it in 2 different ways:
- Right-clicking on any area of the floating Edit Data dialog, and a context menu will pop-up, clicking Exit Edit Mode will close the Edit Data dialog, remove any colors from uncommitted, committed or errored-out Excel cells, and unprotect the Excel worksheet so any Excel cell can be changed (remember we mentioned the Excel worksheet was protected to disallow changes outside the "Editing Region"?).
- Deleting the Excel worksheet in "Edit Mode".
The Edit Data dialog can display information about the MySQL table being edited and the Excel worksheet in "Edit Mode" for that table via a tooltip, this information may prove useful if you run into errors and wish to report a bug. To display the information tooltip you need to click the Edit Data dialog and then hover over the regions of the dialog without a button or label.
As you can see, this is a very powerful and friendly feature that you can use to change data in a MySQL table without writing any SQL query. In this guide you were able to learn how to create a new "Edit Session", modify data, add new rows, delete existing ones and a couple of ways to end the "Edit Session". Hope you found this guide useful!
Remember that your feedback is very important for us, so drop us a message and follow us:
- MySQL on Windows (this) Blog - https://blogs.oracle.com/MySqlOnWindows/
- MySQL for Excel forum - http://forums.mysql.com/list.php?172
- Facebook - http://www.facebook.com/mysql
- YouTube channel: https://www.youtube.com/user/MySQLChannel