Data from a Microsoft Excel spreadsheet can be exported to a new MySQL database table by using the Export Excel Data to New Table option. Exporting data looks like so:
Several advanced options enables you to tweak the exported data. The advanced options dialog looks like so:
Column Datatype Options:
Use the first
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 datatype based on column
field contents: Attempts to analyze the data and determine
the data type for the column. The column type is defined
VARCHAR if it contains multiple
Add additional buffer to
(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 disabled, then the
VARCHAR length is
set to the length of the longest entry in the Excel
Automatically check the Index checkbox for Integer columns: If enabled (default), columns with an Integer data type will have the Create Index option enabled by default.
Automatically check the Allow Empty checkbox for columns without an index: If enabled (default), columns without the Create Index checkbox checked will automatically enable the Allow Empty configuration option.
Field Data options:
Use formatted values: When enabled (default), the data
from Excel is treated as
When disabled, data is never treated as a
Date type, so for example this means
that a date would be represented as a number.
Create table's secondary indexes after data has been exported to speed-up rows insertion: This saves disk I/O for bulk inserts (thousands of rows) since reindexing will not happen every time a row is inserted, but only once at the end of the data insertion. This option is enabled by default, and was added in MySQL for Excel 1.2.1.
Note: This option was
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 enabled, columns without data in Excel are removed and not shown in the preview panel. If disabled (default), these columns will exist but have the Exclude Column option checked. This option was added in MySQL for Excel 1.1.0.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices