MySQL for Excel enables you to load and edit MySQL data directly from Microsoft Excel. Changes are immediately committed if the Auto-Commit option is enabled, or done manually by pressing Commit Changes.
The example below uses the
category table of the
sakila database, but the screen will look
the same for any table. Within MySQL for Excel, Open a MySQL
Connection, click the
Next, select the
table, click Edit MySQL Data, then choose
Import to import the data into a new
Microsoft Excel worksheet for editing.
For additional information about the importing procedure, see Chapter 5, Import MySQL Data into Excel.
The background color represents the status of each cell, and there are four distinct colors that are used while editing table data:
The Green and Blue colors were switched in MySQL for Excel 1.2.0.
Table 4.1 Background cell colors
|White||Default color for all cells. This is either the original data, or the data after Refresh from DB is 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.|
In our example, the green "Drama" field was changed and then committed first, then the blue "Gaming" field was changed but not committed, and then Auto-Commit was enabled before changing the "9" to a "10" in column 10, which generated an error because this commit would have added a duplicate value as primary key.