Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  ...  /  Edit Session Overview

6.5.1 Edit Session Overview

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 Revert Data or Commit Changes 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

  1. Start Excel, select the Data menu tab, and then click MySQL for Excel to open the MySQL for Excel task pane.

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

  3. Select a schema from the list and click Next to display all database objects in the schema (tables, views, and procedures).

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

  5. Click OK to move the preview data to the current worksheet and to enable edit mode. See Figure 6.13, “Editing table data with MySQL for Excel” for an example of the edit-mode menu (pop-up).

    In edit mode, edit the data in your worksheet and click Commit Changes to add the changes manually or select Auto-Commit to commit the changes as you edit the data. Click Revert Data 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 Exit Edit Mode.

  • 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 sakila 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.

Figure 6.13 Editing table data with MySQL for Excel

Content is described in the surrounding text.

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 6.2 Key of Cell Colors in Edit Mode

Color Description
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.