X

MySQL and MySQL Community information

  • September 25, 2014

Importing related MySQL tables into an Excel Data Model using MySQL for Excel

Javier Ignacio Trevino Cobos
Software Development Manager

In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0, you can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the MySQL Developer Zone.

Importing MySQL data into Excel is a common and important operation in MySQL for Excel. There may be times when you need to analyze the data stored in several MySQL tables or views, (possibly in an Excel PivotTable which will be the subject of a future blog post), and to do it you need to dump the data into Excel as the first step. Starting with MySQL for Excel 1.3.0 we introduced a feature that allows you to import the data from multiple MySQL tables or views in a single operation.

If you have used MySQL for Excel before, you should know by now that MySQL data can be imported into an Excel worksheet by selecting any of the listed database objects and clicking Import MySQL Data (if you are not familiar with importing MySQL data into Excel you may want to read the following blog post that covers it: How To - Guide to Importing Data from a MySQL Database to Excel using MySQL for Excel).

In version 1.3.0 we added the support to select multiple tables and views from the database objects list, you will notice the text of the label above the list now states you can use the <CTRL> or <SHIFT> keys to select more than one item.

You can use <CTRL>+Click, <SHIFT>+Click, <SHIFT>+, <SHIFT>+, <SHIFT>+<PgDwn>, <SHIFT>+<PgUp> to select multiple items. The multiple selection is restricted to tables and views. if you try to include a procedure into the multiple selection, only the procedure is going to be selected and the rest of the previously selected items will be automatically deselected. You can also use <CTRL>+<A> to select all tables and views at once (procedures will not be included in that selection).

When a single database object is selected, its available actions will be displayed below the database objects list, and the Import MySQL Data action label is the one used to dump the object's data.

When selecting multiple tables or views you will see the available actions change to reflect the available actions for a multiple selection.

If you click Import Multiple Tables and Views, a new dialog will be displayed where you will see the database objects you selected in a list at the left side. Other tables related to the tables in your original selection via Foreign Keys defined in the database will also be shown in a list at the right side. If we take the example in the screenshot above where the address and city tables were selected, when clicking Import Multiple Tables and Views the Import Data dialog will appear as you can see below.

Related tables are not selected by default (since they were not in your original selection), you can review which ones are needed and select them for import. Both lists show in the Related to column the tables related to the current one, very important information when you need to import related data. The column headers on each of the lists can be clicked to change the sorting of the list from ascending to descending order, by default they are sorted in ascending order by the table or view name.

When clicking Import, the data in each selected table and view will be dumped into individual new Excel worksheets, each worksheet will be named after the table or view it contains data for. If the Create Excel relationships for imported tables option is checked, Excel relationships will be created among the Excel tables created for the imported data. This option is really important towards the creation of an Excel Data Model containing related data that can be further analyzed via Excel PivotTables or Power Pivot.

The Create Excel relationships for imported tables option is only available in Excel 2013 (and higher), since Excel relationships can only be created in the Excel Data Model which was introduced in version 2013. Also that option will only be available if the Create an Excel table for the imported MySQL data advanced option is checked, since Excel relationships can only be created if Excel tables exist. If MySQL for Excel is running on a lower Excel version, the Create Excel relationships for imported tables option will appear disabled and link will be shown to display information as of why the option is disabled.

If the link is clicked the following information message will be displayed.

The Add Summary Fields option will insert a row below the imported data on each created Excel table with options to summarize each column. You may refer to the following blog post that explains that feature in detail: Adding summary fields when importing MySQL data with MySQL for Excel. Note the Add Summary Fields option will only be available if the Create an Excel table for the imported MySQL data advanced option is checked, since the summary row can only be created for Excel tables.

If you need to verify the data of any of the tables or views displayed on any of the lists, you can right-click it and select Preview Data...

and a new dialog will appear showing the data contained in the selected table or view.

As we mentioned before, using <CTRL>+<A> you can select all tables and views in the database objects list. Let's say we did that and clicked Import Multiple Tables and Views, the Import Data dialog would look like the screen below.

When we click on Import, 23 new worksheets will be created containing the data of each selected table or view.  Since the Create Excel relationships for imported tables option is checked, Excel relationships will be created in the Excel Data Model as well. If there is any error upon the Excel relationships creation, an error dialog will be displayed showing the details of the relationships that could not be created and the reason for the error.

Here you can see the list of all worksheets created for the 23 imported tables and views in our example.

The created Excel relationships can be seen in the Excel Manage Relationships dialog, it can be accessed by clicking Relationships within the Data Tools group of the Data ribbon. Below you can see the Excel relationships created in our example.

Let's say you want to import a table with all of its related tables and create the Excel relationships among them, but you do not know beforehand what its related tables are, so you can't do a multiple selection in order to make the Import Multiple Tables and Views action label available. You can also right-click on a table in the database objects selection panel, and from the context menu click Import Selected and Related DB Objects...

It will open the Import Data dialog for a multiple selection showing the selected table and its related ones, all of them selected for import.

If you want to import any indirectly related tables (i.e. tables related to the related tables), you can right-click on any table in the Related Tables list at the right, and from the context menu click Add Related Tables...

it will add to the Related Tables list any table related to the selected one that was not already present, like in the screenshot below.

You can repeat that process as many times needed to include tables indirectly related to the ones in your original selection.

As you can see, importing multiple tables and views and creating an Excel Data Model for them (in Excel 2013 and higher) is very easy. This prepares the road to analyze your MySQL data in Excel using PivotTables in a very powerful and dynamic way. We will talk about creating PivotTables from MySQL data in the next blog post, so stay tuned!

Remember that your feedback is very important for us, so drop us a message and follow us:

Cheers!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha