Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  ...  /  Column Mappings

6.7.1 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 figure shows several columns of Excel data selected in the original worksheet and the Append Data (preview) dialog after clicking Append Excel Data to Table.

Figure 6.33 Appending Excel data to MySQL (Automatic mapping)

Content is described in the surrounding text.

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 worksheet) 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 as follows:

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

    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.