Table of Contents
MySQL for Excel enables you to work with a MySQL database from within Microsoft Excel. MySQL data can be imported into Excel, Excel data can be exported into MySQL as a new table or appended to a current table, and MySQL for Excel enables you to edit the MySQL data directly from within Excel.
Visit the MySQL for Excel forum for additional MySQL for Excel help and support.
For release notes detailing the changes in each release of MySQL for Excel, see MySQL for Excel Release Notes.
MySQL for Excel is a product for Microsoft Windows, and it is installed with MySQL Installer. And typically you will not be required to install or configure additional tools to use MySQL for Excel.
To install, download and execute the MySQL Installer. Select the MySQL for Excel product and then proceed with the installation. See the MySQL Installer manual for additional details.
The MySQL Installer installation process will check if these requirements are met, or notify you if further action is required before proceeding with the installation.
.NET Framework 4.0 (Client or Full Profile).
Microsoft Office Excel 2007 or greater, for Microsoft Windows.
Visual Studio Tools for Office 4.0, and MySQL Installer may install this for you.
An available MySQL Server connection.
MySQL for Excel is loaded and executed by selecting the
menu tab in Excel, and then choosing the "MySQL for Excel" Database icon. This opens a new Excel sidebar with the available MySQL for Excel options. The navigation bar with the MySQL for Excel icon is shown in the following screenshot:While each action, such as Import MySQL Data, has its own set of options, this section describes the global options that affect the entire plugin.
Connection Options:
Wait [ ] seconds for a connection to the server before timing out. Defaults to 15.
Wait [ ] seconds for a database query to execute before timing out. Defaults to 60.
SQL Queries Options:
[ ] Use optimistic updates on all Edit Data sessions. Enabled by default.
( ) Do not show SQL statements sent to the server. Enabled by default.
( ) Preview SQL statements before they are sent to the server. Disabled by default.
( ) Show executed SQL statements along with their results. Disabled by default.
Edit Session Options:
[ ] Restore saved Edit sessions when opening an Excel workbook. Enabled by default.
( ) Reuse Excel worksheets matching their names with the session table names. Enabled by default.
( ) Create new Excel worksheets for the restored Edit sessions. Disabled by default.
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 .
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, , select the
category
table, click Edit MySQL
Data, then choose to
import the data into a new Microsoft Excel worksheet for editing.
For additional information about the importing procedure, see Section 26.3, “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 26.1 Background cell colors
Color | Description |
---|---|
White | Default color for all cells. This is either the original data, or the data after | 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.
Data can be imported from MySQL into a Microsoft Excel spreadsheet by using the Import MySQL Data option after selecting either a table, view, or procedure to import.
By default, all columns are selected and will be imported.
Specific columns may be selected (or unselected) using the
standard Microsoft Windows method of either
Control + Mouse click
to
toggle the selection of individual columns, or
Shift + Mouse click
to select
a range of columns.
The background color of a column shows the status of each column. The color white means that the column has been selected, and therefore it will be imported. Conversely, a gray background means that the column will not be imported.
Right-clicking anywhere in the preview grid opens a context-menu
with either a Select None
or Select
All
option, depending on the current status.
The dialog while importing a table includes the following options:
Include Column Names as Headers: Enabled by default, this inserts the column names at the top of the Microsoft Excel spreadsheet as a "headers" row.
Limit to ___ Rows and Start with Row ___:
Disabled by default, this limits the range of imported data.
The Limit to
option defaults to
1
, and defines the number of rows to
import. The Start with Row
option defaults
to 1
(the first row), and defines where the
import begins. Each option has a maximum value of COUNT(rows)
in the table.
The
include:General Options:
Use the first [ ] rows to preview the MySQL tables data. Defaults to 10.
[] Escape text values that start with "=" so Excel does not treat them as formulas. Enabled by default.
Excel Table Options:
[] Create an Excel table for the imported MySQL table data. Enabled by default.
Use style [ ] for the new Excel table. Defaults to
MySqlDefault
.
[] Prefix Excel tables with the following text: _______. Disabled by default.
Importing a table displays a dialog similar to the following:
Importing a view or procedure displays a similar dialogue, but with the following options:
Include Column Names as Headers: Enabled by default, this will insert the column names at the top of the Excel spreadsheet as a "headers" row.
Import: Because a procedure might return multiple result sets, the import options include:
: Imports the selected tab sheet. This is the default behavior.
: Imports all result sets into the Excel Worksheet horizontally, and inserts one empty column between each result set.
: Imports all result sets into the Excel Worksheet vertically, and inserts one empty row between each result set.
For example, a dialogue like the following is displayed after importing a procedure and pressing the
button to invoke the stored procedure:Data from a Microsoft Excel spreadsheet can be appended to a MySQL database table by using the Append Excel MySQL Data to Table option.
Mapping the Excel columns to the MySQL columns can be executed automatically (default), manually, or by using a stored mapping routine. An automatic mapping routine is the default, and can be can be tweaked if every column cannot be matched automatically. The following screenshot shows two columns of Excel data, and the preview dialog after choosing Append Excel Data to Table:
It is common to tweak the column mappings. A few notes about the manual mapping process:
Manual mapping is performed by dragging a column from the upper source grid (Excel spreadsheet) and dropping it into the lower target column MySQL table grid. Click anywhere within the column to initiate this dragging routine.
The color of the header field for each column defines the current mapping status of the column. The colors include:
Green: A source column is mapped to a target column.
Red: A target column is not mapped.
Gray: A source column is not mapped.
A source column may be mapped to multiple target columns, although this action generates a warning dialog.
Right-clicking a target column shows a context menu with options to either
for a single column, or to for all columns. Dragging a target column outside of the grid removes the mapping.The three mapping methods are described below:
Automatic: The automatic mapping method attempts to match the Excel source column names with the MySQL target table column names. It is then possible to manually tweak the mapping afterwards.
If the automatic process finds zero columns to match, then a simple 1 to 1 matching routine is attempted. Meaning, SourceColumn #1 to TargetColumn #1, SourceColumn #2 to TargetColumn #2, and so on.
Manual: The source column names are manually dragged (matched) with the target column names. Manual dragging can also be performed after the Automatic method is selected.
Stored: Manual mapping styles may be
saved using the button,
which will also prompt for a name and then save it using a
"name
(dbname.tablename)" naming
scheme. The saved mapping style will then be available
alongside the Automatic and
Manual options.
Stored mappings may be deleted or renamed within the
dialog.There are several advanced options that are configured and stored between sessions for each Excel user. The dialog looks similar to:
The advanced Mapping Options:
Perform an automatic mapping when dialog
opens
: Automatically attempt to map the target and
source when the Append Data dialog is
opened. This feature is enabled by default.
Automatically store the column mapping for the given
table
: Stores each mapping routine after executing
the operation. The mapping
routine is saved using the "tablenameMapping
(dbname.tablename)" format. This may also be performed
manually using the
button. It is enabled by default, and this feature was added
in MySQL for Excel 1.1.0.
Reload stored column mapping for the selected table
automatically
: If a stored mapping routine exists
that matches all column names in the source grid with the
target grid, then it is automatically be loaded. This is
enabled by default, and this feature was added in MySQL for Excel
1.1.0.
The advanced Field Data Options:
Use the first
100
(default) Excel data rows to
preview and calculate data types. This determines the number
of rows that the preview displays, and the values that affect
the automatic mapping feature.
Use formatted values: The data from Excel
is treated as Text
,
Double
, or Date
. This is
enabled by default. When disabled, data is never treated as a
Date
type, so for example, this means that
a date would be represented as a number.
The advanced SQL Queries Options:
Disable table indexes to speed-up rows
insertion
: This option is disabled by default, since
you must make sure that if unique indexes are present, that
the data mapped to that column does not contain duplicate
data. This option was added in MySQL for Excel 1.2.1.
The Stored Column Mappings is a list of saved column mappings that were saved with the "Automatically store the column mapping for the given table" feature, or manually with the option.
Data from a Microsoft Excel spreadsheet can be exported to a new MySQL database table by using the Export Excel Data to New Table option. Exporting data looks like so:
Several advanced options enables you to tweak the exported data. The advanced options dialog looks like so:
Column Datatype Options:
Use the first 100
(default)
Excel data rows to preview and calculate data types: This
determines the number of rows that the preview displays,
and the values that affect the automatic mapping feature.
Analyze and try to detect correct datatype based on column
field contents: Attempts to analyze the data and determine
the data type for the column. The column type is defined
as VARCHAR
if it contains multiple
types.
Add additional buffer to VARCHAR
length
(round up to 12, 25, 45, 125, 255): When the data type is
automatically detected and is set to
VARCHAR
, then it calculates the maximum
length for all rows within the column, and rounds up the
maximum length to one of the defined lengths above.
If disabled, then the VARCHAR
length is
set to the length of the longest entry in the Excel
spreadsheet.
Automatically check the Index checkbox for Integer columns: If enabled (default), columns with an Integer data type will have the Create Index option enabled by default.
Automatically check the Allow Empty checkbox for columns without an index: If enabled (default), columns without the Create Index checkbox checked will automatically enable the Allow Empty configuration option.
Field Data options:
Use formatted values: When enabled (default), the data
from Excel is treated as Text
,
Double
, or Date
.
When disabled, data is never treated as a
Date
type, so for example this means
that a date would be represented as a number.
Other options:
Create table's secondary indexes after data has been exported to speed-up rows insertion: This saves disk I/O for bulk inserts (thousands of rows) since reindexing will not happen every time a row is inserted, but only once at the end of the data insertion. This option is enabled by default, and was added in MySQL for Excel 1.2.1.
Note: This option was Removed
in
MySQL for Excel 1.2.1. Now, the default behavior is to always
remove empty columns from the calculations.
Remove columns that contain no data, otherwise flag them as "Excluded": If enabled, columns without data in Excel are removed and not shown in the preview panel. If disabled (default), these columns will exist but have the Exclude Column option checked. This option was added in MySQL for Excel 1.1.0.
Edit Connections
: MySQL connections can now
be edited from within the MySQL for Excel plugin by right-clicking
and choosing Edit Connection. Before,
these connections could only be edited with MySQL Workbench.
Optimistic Updates
: Previously, only
"Pessimistic Updates" were used, which means that pressing
Commit Changes would overwrite changes
performed outside of MySQL for Excel for the edited cells.
Both options remain available today, and optimistic updates are enabled by default. This update type can be set either as a preference, or toggled per session.
The Append Data dialog will now notify you of incompatible types (with visual warnings) when mapping source Excel columns to target MySQL columns.
If a mismatch is discovered, then the column in the source grid that contains the mapped Excel data turns red, and selecting this column displays a warning with text explaining that the source data is not suitable for the mapped target column's data type.
New preview preferences allow you to enable one of the following three options:
Preview SQL statements before they are sent to the Server: View (and optionally) edit the MySQL UPDATE/INSERT statements before they are committed.
Show executed SQL statements along with the results: View the statements after they are committed, which is the current behavior.
Do not show the MySQL statements: Only show summary information, such as number of affected rows, and not MySQL statements. This is enabled by default.
Create Table: The Data Export feature now has the option to only create the table without inserting the data.
To execute, toggle the
button to , and then click.The selected schema name is now displayed on top of the MySQL for Excel Database Object Selection panel.
The Advanced Options dialogs opened from the Import, Export and Append Data windows now immediately apply the option changes, when before the Advanced Options dialog had to be reopened before the changes could be previewed.
Edit Data sessions can now be saved: Using the new Edit Session preferences, these sessions were automatically closed after closing an Excel workbook. This data, such as the Workbench connection ID, MySQL schema, and MySQL table name, can now be preserved if the Excel workbook is saved to disk, and available when the Excel workbook is reopened.
Excel tables are automatically created for any data imported from MySQL to an Excel worksheet, with a name like "Schema.DB-Object-name". The DB object name can be a MySQL table, view, or stored procedure. Options for this feature are listed under Import Data, Advanced Options. The newly created Excel tables can be referenced for data analysis in Pivot Tables or reports.
Frequently Asked Questions, with Answers.
Questions
27.7.1: I installed the MySQL for Excel plugin, but can't find it in Microsoft Excel. How do I start it?
27.7.2: I click on Edit Data and after importing the table data into Excel, I can't sort or move columns around. How can I do that?
27.7.3: When editing a MySQL table's data, the Excel worksheet where the data is dumped is protected. How can unprotect it?
27.7.4: The MySQL Workbench connections that use SSH tunneling appear grayed out (disabled) in MySQL for Excel. How can I use a SSH connection?
Questions and Answers
27.7.1: I installed the MySQL for Excel plugin, but can't find it in Microsoft Excel. How do I start it?
The MySQL for Excel plugin is automatically added to Microsoft Excel's data menu when it is installed. Look for the MySQL for Excel icon, by default it will be listed on the right side of the main menu.
If it's not there, then you might have to reinstall the plugin. But before doing so, first check if it's listed under "Add/Remove Programs" in Microsoft Windows. If not, then it has not been installed. Next, check the Excel Add-Ins list. For Office 2007 this is found by clicking the Office logo in Excel (top left corner), click Add-Ins. Is MySQL for Excel listed as a COM Add-in? If so, then consider filing a bug report (bugs.mysql.com), or attempt to reinstall the plugin.
, then select27.7.2: I click on Edit Data and after importing the table data into Excel, I can't sort or move columns around. How can I do that?
In order to maintain the mapping of rows and columns in the Excel Worksheet against the rows and columns in the MySQL table, no alteration is permitted on the worksheet (i.e. sorting, deleting rows, deleting columns). If you need to alter the data there you can do that by right-clicking the Edit Data window and selecting Exit Edit Mode.
27.7.3: When editing a MySQL table's data, the Excel worksheet where the data is dumped is protected. How can unprotect it?
The Excel worksheet is protected to not allow alterations to the order of rows and columns. The password used for the protection is a GUID auto-generated at runtime so that the protection is not violated in any way. If you wish to unprotect the worksheet to manipulate your data, you can do that by right-clicking the Edit Data window and selecting Exit Edit Mode.
27.7.4: The MySQL Workbench connections that use SSH tunneling appear grayed out (disabled) in MySQL for Excel. How can I use a SSH connection?
This is a known limitation of MySQL for Excel. MySQL for Excel uses MySQL Connector/NET to connect and communicate with MySQL databases. Connector/NET does not have SSH support, so the behavior will change if Connector/NET supports it in the future.