26.2 Edit MySQL Data in Excel

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 example sakila database, but the screen will look the same for any table. Within MySQL for Excel, Open a MySQL Connection, click the sakila schema, Next, select the category table, click Edit MySQL Data, then choose Import to import the data into a new Microsoft Excel worksheet for editing.

Note

For additional information about the importing procedure, see Section 26.3, “Import MySQL Data into Excel”.

Figure 26.3 Editing table data with MySQL for Excel

Editing table data with MySQL for Excel

The background color represents the status of each cell, and there are four distinct colors that are used while editing table data:

Note

The Green and Blue colors were switched in MySQL for Excel 1.2.0.

Table 26.1 Background cell colors

ColorDescription
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.