Most of the new features added to MySQL for Excel 1.3.x involve improvements to the Data Import functionality.
Starting with MySQL for Excel 1.3.8, support for secure connections was added. Encrypted connections can be in the form of SSL certificates or SSH tunneling, without the requirement of having intermediate proxy software to create the tunnel. Encrypted connections can be configured from the MySQL for Excel add-in directly or they can be configured with MySQL Workbench and then used to open a connection from the add-in.
A new global option,
Tolerance for FLOAT and DOUBLE comparisons in WHERE clause, provides a way to edit data of type
DOUBLEthat enables proper row-matching in the database when it is used together with optimistic updates (see Global Options, Edit Sessions Tab).
The Import Data operation for stored procedures now enables the selection of individual columns to be imported from each returned result set, which is similar to the way imported column data already works for table and view data.
The Import Data operation adds digits to floating-point numbers. For example, instead of rendering a value such as 5.3 precisely from the database, the operation displays 5.0000019073486 after importing the data. This behavior affects
DOUBLEdata types, which adhere to the IEEE-754 standard and are stored as approximate values.
A new option now provides a way to import floating-point numbers using the
DECIMALdata type, which then stores and displays the exact value from the database (see Advanced Import Data Options, Formatting Tab).
Support for MySQL Fabric was removed. Fabric connections created in MySQL Workbench will no longer appear in the MySQL for Excel task pane.
The way MySQL for Excel shares data-editing sessions among users and between computers has been improved to promote maximum portability. A workbook with Excel tables containing MySQL data when saved by one Windows account to a local computer can now be sent to a coworker (different Windows account), or simply moved to a different computer, and the MySQL data can be refreshed and any edit-data sessions can be restored automatically when the workbook is opened.
This new functionality requires the Excel workbook to support XML parts, which is the case for the following file formats:
Excel Workbook (.xlsx)
Excel Macro-Enabled Workbook (*xlsm)
Excel Binary Workbook (*xlsb)
Excel 97-2003 Workbook (*.xls)
Excel Template (*.xltx)
Excel Macro-Enabled Template (*.xltm)
Excel 97-2003 Template (*.xlt)
Microsoft Excel 5.0/95 Workbook (*.xls)
Strict Open XML Spreadsheet (*.xlsx)
Global options were rearranged into tabs for easier navigation. Clickin the MySQL for Excel task pane to open the Global Options dialog.
Added a new global option to format spatial data. Formats are: Well-Known Text, Keyhole Markup Language, Geography Markup Language, or GeoJSON.
Added a new global option (Imported Tables tab) to restore imported MySQL data in Excel tables when either the Excel workbook is opened or the MySQL for Excel task pane is opened.
Added the Data Type field within the Column Options area in the Export Data to MySQL dialog.action to the context menu of the
The Connections Migration dialog now offers a way to postpone the migration of connections to MySQL Workbench by one hour, one day, one week, one month, or indefinitely. For more information about this option, see Connections Tab.
The Append Excel Data to Table operation was updated with new advanced options to manage the behavior of rows containing unique key values that are duplicates of those in the database. The following new options are located within the SQL Queries tab of the Advanced Append Data Options dialog:
Error out and abort the append operation (default)
Ignore rows with duplicate unique key values
Replace the values in the old rows with the ones in new rows
You can now refresh imported data from the source MySQL database by clickingfrom the context-menu, or from the navigation menu. These actions check for changes in the source MySQL database and update your imported MySQL data accordingly.
Use case: A colleague sends you a MySQL Excel worksheet with data exported from a MySQL database. You open the file several days later, and worry that the data is outdated so you click.
A newbutton was added to the options pages. It changes each option to the default value, and you then confirm (or cancel) the application of these changes.
Enabling the new Add Summary Fields for Numeric Columns option adds a summary field to the end of each numeric column in Excel. From here, you choose the desired function for the column, such as total or average.
You may now import data from multiple objects in a single operation. Use Control or Shift to select multiple objects (tables and/or views) from the MySQL for Excel task pane, and click to open the new dialog for selecting additional objects that have direct relationships to the objects you selected. Each object opens in its own Worksheet.
From this new dialog, you may also generate a Relationships model in Excel. This functionality requires Excel 2013 or higher, or Excel 2010 with the PowerPivot add-in.
A new Create a PivotTable with the Imported Data option was added. This creates a Pivot Table in Excel.
All options now have descriptive tooltips. Hover over an option/preference to view helpful information about its use.
You may now specify a collation for created schemas. The collation type defaults to "Server Default." These statements can be reviewed before execution.
All MySQL data types are now available when performing Data Export operations. By default, only the most commonly used data types are listed, which was only behavior in previous versions of MySQL for Excel. You may still type in a type instead of selecting it.