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 Remove Column Mapping for a single column, or to Clear All Mappings 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 Store Mapping 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
Stored mappings may be deleted or renamed within the Advanced Options dialog.
There are several advanced options that are configured and stored between sessions for each Excel user. The advanced options are:
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 Append operation. The mapping
routine is saved using the "tablenameMapping
(dbname.tablename)" format. This may also be performed
manually using the Store Mapping
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
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
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 Store Mapping option.