27.4 Append Excel Data into MySQL

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 27.7 Appending Excel data to MySQL (Automatic mapping)

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:

Mapping methods

The three mapping methods are described below:

Advanced Options

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

Figure 27.8 Appending Excel data to MySQL (Advanced Options)

Appending Excel data to MySQL (Advanced Options)

The advanced Mapping Options:

The advanced Field Data Options:

The advanced SQL Queries Options:

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.