Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  Append Excel Data into MySQL  /  Append — Advanced Options

10.2 Append — Advanced Options

Advanced options enable you to manage the way MySQL for Excel appends data from an Excel worksheet to MySQL tables. Use Reset to Defaults to restore the original option selections and values.

A set of advanced options affect the current append-data operation, as described in the sections that follow.

Columns Mapping Tab

The following options apply to append-data operations.

Figure 10.2 Appending table data with MySQL for Excel: Column Mapping Options

Appending table data with MySQL for Excel: Column mapping options

Column 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 selected by default.

  • Automatically store the column mapping for the given table: Stores each mapping routine after clicking Append in the Append Data dialog. The mapping routine is saved using the "tablenameMapping (dbname.tablename)" format. This may also be performed manually by clicking Store Mapping. It is selected 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 feature is selected by default and it was added in MySQL for Excel 1.1.0.

  • Confirm column mapping overwriting: Notifies you before overwriting previously saved column mappings and enables you to accept or decline the action. This feature is deselected by default and the option was added for MySQL for Excel 1.3.7.

Stored Mappings Tab

The following options apply to append-data operations.

Figure 10.3 Appending table data with MySQL for Excel: Stored Mapping Options

Appending table data with MySQL for Excel: Stored mapping options

The Stored Mappings tab shows a list of saved column mappings that were saved with the Automatically store the column mapping for the given table option (in the Column Mapping tab) or manually by clicking Store Mapping in the Append Data dialog.

Field Data Tab

The following options apply to append-data operations.

Figure 10.4 Appending table data with MySQL for Excel: Field Data Options

Appending table data with MySQL for Excel: Field data options

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 selected by default. When deselected, data is never treated as a Date type, so for example, this means that a date would be represented as a number.

  • Show column data types above column names: When selected, the data type of each column appears above the column name in both the source and target grids. This option is deselected by default.

SQL Queries Tab

The following options apply to append-data operations.

Figure 10.5 Appending table data with MySQL for Excel: SQL Queries Options

Appending table data with MySQL for Excel: SQL queries options

SQL Queries Options

  • Generate an INSERT statement for each data row: When selected, an INSERT statement for each data row being appended is generated in the resulting SQL query sent to the MySQL server. Otherwise, a single INSERT statement is generated as a bulk data operation, which performs better than multiple INSERT statements. This option is deselected by default.

    • Disable table indexes to speed-up rows insertion: This option is deselected by default, because 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.

  • When new rows contain unique key values that duplicate old rows:

    • Error out and abort the append operation: (default) Retains the behavior of previous versions of MySQL for Excel in which the server returns an error if any duplicate unique key values are found. This option was added in MySQL for Excel 1.3.7.

    • Ignore rows with duplicate unique key values: Rows containing duplicate values for unique keys are ignored or skipped. Only the rows without duplicate values are inserted into the database. This option was added in MySQL for Excel 1.3.7.

    • Replace the values in the old rows with the ones in new rows: Rows containing duplicate values for unique keys replace the values of the corresponding rows in the database. This option was added in MySQL for Excel 1.3.7.


User Comments
Sign Up Login You must be logged in to post a comment.