Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  Edit MySQL Data in Excel

Chapter 8 Edit MySQL Data in Excel

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 import all rows from the table into a new worksheet with the same name as the MySQL table and to enable edit mode. For additional information about the importing operation, see Chapter 9, Import MySQL Data into Excel.

    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 example shows 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 following this example.

Figure 8.1 Editing table data with MySQL for Excel

Editing table data with MySQL for Excel

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

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.


User Comments
Sign Up Login You must be logged in to post a comment.