Advanced options enable you to manage the way MySQL for Excel appends data from an Excel worksheet to MySQL tables. Useto 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.
The following figure shows the columns mapping options that apply to append-data operations.
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 in the Append Data dialog. The mapping routine is saved using the "tablenameMapping (dbname.tablename)" format. This may also be performed manually by clicking . 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.
The following figure shows the stored mappings options that apply to append-data operations.
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 in the Append Data dialog.
The following figure shows the field data options that apply to append-data operations.
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 selected by default. When deselected, data is never treated as a
Datetype, 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.
The following figure shows the SQL queries options that apply to append-data operations.
SQL Queries Options:
Generate an INSERT statement for each data row: When selected, an
INSERTstatement for each data row being appended is generated in the resulting SQL query sent to the MySQL server. Otherwise, a single
INSERTstatement is generated as a bulk data operation, which performs better than multiple
INSERTstatements. 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.