Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt
PDF (US Ltr) - 2.9Mb
PDF (A4) - 2.9Mb
EPUB - 2.5Mb
HTML Download (TGZ) - 2.4Mb
HTML Download (Zip) - 2.4Mb

MySQL and Windows  /  Export Excel Data into MySQL

Chapter 11 Export Excel Data into MySQL

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:

Figure 11.1 Exporting Excel data to MySQL

Exporting Excel data to MySQL

Advanced Export options

Several advanced options enables you to tweak the exported data. The advanced options dialog looks like so:

Figure 11.2 Exporting Excel data to MySQL (Advanced options)

Exporting Excel data to MySQL (Advanced options)

  • Column Datatype 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 datatype 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 disabled, then the VARCHAR length is set to the length of the longest entry in the Excel spreadsheet.

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

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


      This option was added in MySQL for Excel 1.3.0

  • Field Data options:

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

  • Other options:

    • 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 re-indexing 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.


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

Additional Notes

  • Entering "0" into a date column.

    Entering the value "0" into an Excel date column will convert the value to "12/30/1899" in MySQL. This is because Excel first translates the value to the minimum date in Excel, which is "1/0/1900", because dates are internally stored in Excel as numbers (the days that have passed since "1/0/1900". However, because "1/0/1900" is not a valid date, the committed value to MySQL will change to "12/30/1899" because the .NET MySQL connector automatically converts "1/0/1900" to "12/30/1899", which is the closest valid date.

Download this Excerpt
PDF (US Ltr) - 2.9Mb
PDF (A4) - 2.9Mb
EPUB - 2.5Mb
HTML Download (TGZ) - 2.4Mb
HTML Download (Zip) - 2.4Mb
User Comments
Sign Up Login You must be logged in to post a comment.