MySQL for Excel enables you to load and edit MySQL table data directly from a Microsoft Excel worksheet. Changes are committed immediately if the Auto-Commit option is enabled in the pop-up menu; otherwise, you can use the or operations in the same pop-up menu to revert or commit data changes in MySQL tables.
To edit MySQL table data from an Excel worksheet
Start Excel, select themenu tab, and then click to open the MySQL for Excel task pane.
From the Open a MySQL Connection area in the task pane, double-click an existing local or remote connection to display the available database schemas.
Select a schema from the list and clickto display all database objects in the schema (tables, views, and procedures).
Select the table with data to edit and then click Edit MySQL Data. A preview window displays the selected data and provides an option to specify the number of rows to preview.
Clickto move the preview data to the current worksheet and to enable edit mode. The figure that follows shows an example of the edit-mode menu.
In edit mode, edit the data in your worksheet and clickto add the changes manually or select to commit the changes as you edit the data. Click to either refresh data from the database or to revert the changed data.
Additional edit-mode actions:
To stop edit mode, right-click the pop-up menu and select.
To resume edit mode, click a cell with data.
To move the pop-up menu, hold down the right mouse button and drag the menu.
The following figure shows an example of the
category table of the
schema sample in edit mode. The background color represents the
status of each cell. There are four distinct colors that are used
while editing table data and two helper colors. For details, see the
key of cell colors in edit mode.
In the previous example:
The green Drama cell was changed and then committed.
The blue Gaming cell was changed but not committed.
Finally, Auto-Commit was enabled before changing the value 9 to 10 in row 10 of the category_id column, which generated an error because this commit would have added a duplicate value as primary key.
Table 8.1 Key of Cell Colors in Edit Mode
|White||Default color for all cells. This is either the original data, or the data afteris clicked.|
|Green||Cells that were committed with success.|
|Blue||Cells that were modified but have not yet been committed.|
|Red||Cells that generated an error when a commit was attempted. An error dialog is also displayed while the commit is attempted.|
|Orange||Cells that had a commit attempted, but the commit failed due to detected changes from external sources. For example, a different user made a change to a field after it was imported into Excel. This is a feature of Optimistic Updates.|
|Yellow||Cells that accept new data. Data entered here is inserted into the MySQL table.|