Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

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

11.2 Export — Advanced Options

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

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

Column Tab

The following figure shows the column options that apply to export-data operations.

Figure 11.3 Exporting Excel data to MySQL: Column Options

Content is described in the surrounding text.

Column 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.

  • Analyze and try to detect correct data type based on column field contents: Attempts to analyze the data and determine the data type for the column. The column type is defined as VARCHAR if it contains multiple types.

    • Add additional buffer to Varchar length (round up to 12, 25, 45, 125, 255): When the data type is automatically detected and is set to VARCHAR, then it calculates the maximum length for all rows within the column, and rounds up the maximum length to one of the defined lengths above.

      If deselected, then the Varchar length is set to the length of the longest entry in the Excel worksheet.

  • Automatically check the Index checkbox for Integer columns: If selected (default), columns with an Integer data type will have the Create Index option selected by default.

  • Automatically check the Allow Empty checkbox for columns without an index: If selected (default), columns without the Create Index check box selected will automatically enable the Allow Empty configuration option.

  • Show all available MySQL data types in the Data Type drop-down list: By default, only the most commonly used data types are displayed. Select (deselected by default) this setting to see a list of all MySQL data types.

    Note

    This option was added in MySQL for Excel 1.3.0

Field Data Tab

The following figure shows the field data options that apply to export-data operations.

Figure 11.4 Exporting Excel data to MySQL: Field Data Options

Content is described in the surrounding text.

Field Data Options

  • Use formatted values: When selected (default), the data from Excel is treated as Text, Double, or Date. When deselected, data is never treated as a Date type, so for example this means that a date would be represented as a number.

SQL Queries Tab

The following figure shows the SQL query options apply to export-data operations.

Figure 11.5 Exporting Excel data to MySQL: SQL Queries Options

Content is described in the surrounding text.

SQL Query Options:

  • Generate an INSERT statement for each data row: When selected, an INSERT statement for each data row being exported 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.

    • Create table's indexes after data has been exported to speed-up rows insertion: This saves disk I/O for bulk inserts (thousands of rows) because re-indexing will not happen every time a row is inserted, but only once at the end of the data insertion. This option is selected by default.

      Note

      This option was added in MySQL for Excel 1.2.1.

Note

The following option was Removed in MySQL for Excel 1.2.1. Now, the default behavior is to always remove empty columns from the calculations.

Remove columns that contain no data, otherwise flag them as "Excluded": If selected, columns without data in Excel are removed and not shown in the preview window. If deselected (default), these columns will exist but have the Exclude Column option checked.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.