Fetching
data from a database to then get it into an Excel
spreadsheet to do analysis, reporting, transforming, sharing, etc. is a very
common task among users. There are several ways to extract data from a MySQL database to then import it to Excel; for example you can use the MySQL Connector/ODBC to configure an ODBC
connection to a MySQL database, then
in Excel use the Data Connection
Wizard to select the database and table from which you want to extract data
from, then specify what worksheet you want to put the data into. Another way is to somehow dump a comma delimited
text file with the data from a MySQL
table (using the MySQL Command Line
Client, MySQL Workbench, etc.) to
then in Excel open the file using the
Text Import Wizard to attempt to correctly split the data in columns.
These
methods are fine, but involve some degree of technical knowledge to make the
magic happen and involve repeating several steps each time data needs to be imported
from a MySQL table to an Excel spreadsheet. So, can this be done
in an easier and faster way? With MySQL
for Excel you can.
MySQL for Excel features an Import MySQL Data action where you can import
data from a MySQL Table, View or
Stored Procedure literally with a few clicks within Excel. Following is a quick
guide describing how to import data using MySQL
for Excel.
This
guide assumes you already have a working MySQL
Server instance, Microsoft Office
Excel 2007 or 2010 and MySQL for
Excel installed.
1. Opening
MySQL for Excel
Being an Excel
Add-In, MySQL for Excel is opened
from within Excel, so to use it open Excel, go to the Data tab located in the Ribbon and click MySQL for Excel at the far right of the Ribbon.
2. Creating
a MySQL Connection (may be optional)
If you have MySQL
Workbench installed you will automatically see the same connections that
you can see in MySQL Workbench, so
you can use any of those and there may be no need to create a new connection.
If you want to create a new connection (which normally you
will do only once), in the Welcome Panel
click New Connection, which opens
the Setup New Connection dialog.
Here you only need to give your new connection a distinctive Connection Name, specify the Hostname (or IP address) where the MySQL Server instance is running on (if different
than localhost), the Port to connect
to and the Username for the login.
If you wish to test if your setup is good to go, click Test Connection and an information
dialog will pop-up stating if the connection is successful or errors were found.
3.Opening
a connection to a MySQL Server
To open a pre-configured connection to a MySQL Server you just need to double-click it, so the Connection Password dialog is displayed
where you enter the password for the login.
4. Selecting
a MySQL Schema
After opening a connection to a MySQL Server, the Schema
Selection Panel is shown, where you can select the Schema that contains the
Tables, Views and Stored Procedures you want to work with. To do so, you just
need to either double-click the desired Schema or select it and click Next >.
5. Importing
data…
All previous steps were really the basic minimum needed to
drill-down to the DB Object Selection
Panel where you can see the Database
Objects (grouped by type: Tables, Views and Procedures in that order) that you
want to perform actions against; in the case of this guide, the action of
importing data from them.
a. From
a MySQL Table
To import from a Table you just need to select it from the
list of Database Objects’ Tables group, after selecting it you will note
actions below the list become available; then click Import MySQL Data.
The Import Data
dialog is displayed; you can see some basic information here like the name of
the Excel worksheet the data will be
imported to (in the window title), the Table
Name, the total Row Count and a
10 row preview of the data meant for the user to see the columns that the table
contains and to provide a way to select which columns to import.
The Import Data
dialog is designed with defaults in place so all data is imported (all rows and
all columns) by just clicking Import;
this is important to minimize the number of clicks needed to get the job done.
After the import is performed you will have the data in the Excel worksheet formatted automatically.
If you need to override the defaults in the Import Data dialog to change the
columns selected for import or to change the number of imported rows you can
easily do so before clicking Import.
In the screenshot below the defaults are overridden to import only the first 3
columns and rows 10 – 60 (Limit to
50 Rows and Start with Row 10).
If the number of rows to be imported exceeds the maximum
number of rows Excel can hold in its
worksheet, a warning will be displayed in the dialog, meaning the imported
number of rows will be limited by that maximum number (65,535 rows if the worksheet
is in Compatibility Mode). In the
screenshot below you can see the Table contains 80,559 rows, but only 65,534
rows will be imported since the first row is used for the column names if the Include Column Names as Headers
checkbox is checked.
b. From
a MySQL View
Similar to the way of importing from a Table, to import from a
View you just need to select it from the list of Database Objects’ Views group,
then click Import MySQL Data.
The Import Data
dialog is displayed; identically to the way everything looks when importing
from a table, the dialog displays the View
Name, the total Row Count and the
data preview grid.
Since Views are really a filtered way to display data from
Tables, it is actually as if we are extracting data from a Table; so the Import Data dialog is actually
identical for those 2 Database Objects.
After the import is performed, the data in the
Excel spreadsheet looks like the following screenshot.
Note that you can override the defaults in the Import Data dialog in the same way
described above for importing data from Tables. Also the Compatibility Mode
warning will be displayed if data exceeds the maximum number of rows explained before.
c. From
a MySQL Procedure
Too import from a Procedure you just need to select it from
the list of Database Objects’ Procedures group (note you can see Procedures
here but not Functions since these return a single value, so by design they are
filtered out). After the selection is made, click Import MySQL Data.
The Import Data
dialog is displayed, but this time you can see it looks different to the one
used for Tables and Views. Given the
nature of Store Procedures, they require first that values are supplied for its
Parameters and also Procedures can return multiple Result Sets; so the Import Data dialog shows the Procedure Name and the Procedure Parameters in a grid where
their values are input. After you supply the Parameter Values click Call.
After calling the Procedure, the Result Sets returned by it
are displayed at the bottom of the dialog; output parameters and the return value
of the Procedure are appended as the last Result Set of the group. You can see
each Result Set is displayed as a tab so you can see a preview of the returned
data. You can specify if you want to import
the Selected Result Set (default), All Result Sets – Arranged Horizontally
or All Result Sets – Arranged Vertically
using the Import drop-down list;
then click Import.
After the import is performed, the data in the Excel
spreadsheet looks like the following screenshot. Note in this example all Result Sets were
imported and arranged vertically.
As
you can see using MySQL for Excel
importing data from a MySQL database
becomes an easy task that requires very little technical knowledge, so it can
be done by any type of user. Hope you enjoyed this guide!
Remember
that your feedback is very important for us, so drop us a message:
MySQL on Windows (this) Blog - https://blogs.oracle.com/MySqlOnWindows/
Forum - http://forums.mysql.com/list.php?172
Facebook - http://www.facebook.com/mysql
Cheers!
