26.4. Append MySQL Data

Data from a Microsoft Excel spreadsheet can be appended to a MySQL database table by using the Append Excel MySQL Data to Table option.

Column mappings

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:

Figure 26.5. Appending Excel data to MySQL (Automatic mapping)

General mapping information

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.

Mapping methods

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 Manual options.

Stored mappings may be deleted or renamed within the Advanced Options dialog.

Advanced Options

There are several advanced options that are configured and stored between sessions for each Excel user. The dialog looks similar to:

Figure 26.6. Appending Excel data to MySQL (Advanced Options)

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 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 Stored Column Mappings is a list of saved column mappings that were saved with with the "Automatically store the column mapping for the given table" feature, or manually with the Store Mapping option.