MySQL for Excel new features (1.2.0): Auto refresh Import Data, Export Data and Append Data dialogs
By Javier Rivera on Jan 13, 2014
Today we're going to talk about one of the new features included in the latest MySQL for Excel release to date (1.2.0) which can be Installed directly from our MySQL Installer.
Since the first release you were allowed to Import and Append data from Excel to a MySQL database, and also to import data from the MySQL database to an Excel worksheet.
The Import Data, Export Data and Append Data dialogs handle small previews of the data you are working with, and some settings about how the data should be treated can be found by clicking the Advanced Options button on them. In previous versions, the changes on options that have a direct impact on how the data were not reflected upon accepting (saving) those changes and you had to close the aforementioned dialogs and reopen them again to see those option changes.
Now on this version, a smart-refresh has been added to these screens to provide real-time feedback on how the data is being parsed.
Let me present you some brief examples:
Here in the Advanced Options from the Import Data dialog, we have this option highlighted to modify the number of rows to be shown on the preview:
This screen shows you how the preview looks like before accepting the changes and how the rest of the screen looks like before pressing the Accept button:
This screen shows you how the dialogs look like afterwards, without requiring the user to close and reopen it:
This next screen shows what another option from the Import Data dialog does, this time we are looking at what the escape character for values that start with '=' checkbox does:
As you can see at the left of this screen, when it is not checked it allows the Excel formulas to execute normally after the data is imported. This option may not reflect any difference in the preview since it only has effect once the data is imported to the Excel Worksheet.
This is how the data will look after imported when the checkbox is selected, note how the column is treated as text:
This screen is to show you some of the functionality located at the Append Data dialog:
The mapping options will interact over the imported data to produce the above preview of data.
You can see we can modify the number of rows used for the preview just like in the Import Data dialog.
We can also see a checkbox (with a blue balloon) to format dates such (if checked) or treat them as a whole number since is the way Excel stores them to perform date calculations.
In this image we can appreciate the advanced options for the Export Data dialog:
We have an option to modify the number of rows used for the preview just like in the Import Data dialog.
Another option (with the yellow balloon) will help us auto detecting the default type of the column, based on its contents. If consistent data type for all the values within a column is not found, the data type is set to Varchar. Please visit our MySQL for Excel User Manual for more detail on how the Export Data form works.
We have an option which allows to automatically create an index for integer containing columns (with the green balloon).
Also (with the blue balloon) an option to format dates just as explained before in the Append Data dialog.
Finally, there's an option to either remove columns without data or leave them in the preview grid but flagged as "Excluded" highlighted in red.
We have seen today some options we can take advantage of in the Import Data, Export Data and Append Data dialogs within MySQL for Excel, and how they are reflected immediately in the preview grids after accepting the changes we make in the Advanced Options dialogs. Stay tuned on our blogs for more interesting posts of things you can accomplish with MySQL for Excel. Cheers!