Connecting Excel to a remote MySQL server
Emin Gabrielyan
1. Table of contents
5. Linking an area in the Excel with the remote MySQL table.
8. Installation of the ODBC connector
2. Introduction
This document describes the connection of an Excel file with a remote MySQL server. The document is organized in the inverse order. I start first by the demonstration of the result then I go backward by showing how the connection is configured and installed.
3. Result
Here is an Excel file where an area starting from the cell $J$4 is connected to a remote MySQL server.
Here is a session of a mysql connection to the server, where I change a value in the table:
$ mysql -h'37.187.52.44' -u'emin' -p
Enter password:
mysql> use test
Database changed
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 201 |
| 11 | 21 |
+------+------+
2 rows in set (0.03 sec)
mysql> update t set y=202 where x=10;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 202 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql>
After a minute the Excel file updates its values:
4. Connection properties
In the data / connections menu of the Excel application you have the connection corresponding to the link to the table on the remote server
In the properties pane you have to set the maximal refresh frequency and check the box for refreshing the file when opening the file.
5. Linking an area in the Excel with the remote MySQL table
You have to select a cell in your Excel sheet, for Example C5
Then in Data / Existing Connections pane, you have to choose the connection which was already defined (see the following sections for the definition of the connection).
For our case the name of the connection is “test t”
Click on Open and you will have the following confirmation box
Click on OK, and your Excel file will be updated with the values of the table taken from the remote server:
6. Establishing a connection
Open the Data / Connections tab which shall look as follows. You normally will have no connection yet listed in the window.
Click on Add and you will have the following window
Browse for more connections by clicking on [Browse for More…]. You will have another window that looks as follows:
Here you must click on New Source… and you will have this:
Select ODBC DSN
Click on Next and you will see in the list the ODBC driver that you have installed for the MySQL connection. For installation of the ODBC driver refer to the following section. As described in the introduction this document is organized in the reverse chronological order.
Click on Next, and you will see the table which is behind the Database Connection
Leave the data as suggested in the next window
Click on Finish
I got the above message, as the connection was already created. I am clicking on Yes to replace the file.
Now on the list of connections I have a new one, called “test t2”. It corresponds to the link to the exactly the same remote table.
Go into its properties
And change the refresh frequency and force the refresh when opening the file.
Close the connection pane
Now use the Data / existing connection button to use the new link “test t2”
And to sync the remote table values with the area of your Excel sheet starting from the O4 corner
Click on OK, and you will have the values of the remote mysql server in your Excel sheet
You can now connect to the mysql server with the command line prompt, and change a value in the table.
mysql> update t set y=215 where x=10;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 215 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql>
The Excel file will update its values soon
Note that an early linked table is not updated yet. The reason is that the auto-refresh time of its connection (to the same table) is different.
7. Configuring the ODBC
Type ODBC in the Windows’ Start menu and select the Data Sources (ODBC) application
In the User DSN pane add a new name. The ODBC entries added in the User DSN are limited by the current Windows User only (System DSN covers all users on the computer, and the File DSN creates a file that can be ported to another computer). In the example shown below you see already mysql103. This is the one created by me before. We will now create a new one.
Click on Add button. You will see the MySQL ODBC 5.2 drivers. These drivers were installed previously. Refer to the next section for the instructions on how-to install the MySQL ODBC driver.
Let us use the Unicode version
Click on Finish and you will have the following form to fulfill
We give a new connection name “mysql104” now.
As soon as you fulfill the correct information, including the password, the drop-down menu of databases allows you to select the database on the remote server. It means that the entered connection parameters are valid.
In our example we select the database “test”
Click also on the Test button to check the connection.
Now a new User DSN is created
In an Excel file, we can see the new DSN. The detailed sequence of screenshots was already shown previously. For a reminder, to reach the following window you have to follow in the Excel file the following path [Data] / [Connections] / [Add…] / [Browse for More…] / [New Source…] / [ODBC DSN] / [Next].
Select now the new data source “mysql104”
Click on next
Give a different ODC file name
And finish. The first line corresponds to the new connection.
We remove all other connections to not create a mess
And we make sure that this is the new connection
The properties need an update of the refresh time
The pane looks as follows after the update
Now we can use the existing connections to link an area with the remote table
Confirming the location in the Excel sheet where to we wish to link the remote table.
And here is the result
And after a little update on the server side:
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 2001 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql> update t set y=3001 where x=10;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 3001 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql>
We have the Excel sheet updated as well
8. Installation of the ODBC connector
The driver is installed from here
http://dev.mysql.com/downloads/connector/odbc/
The 32 and 64 bit drivers are installed due to an error that occurred when attempting to use them the first time
Now we will uninstall the 32 bit version to make sure that it’s the 64 bit version which is working
The DSN still show the two names
If removing is successful
It means that the right driver is operational
Finally we can open the Excel file
And change the value on the server
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 3001 |
| 11 | 21 |
+------+------+
2 rows in set (0.03 sec)
mysql> update t set y=4001 where x=10;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 4001 |
| 11 | 21 |
+------+------+
2 rows in set (0.03 sec)
mysql>
then we wait until the values are updated on the Excel
And as the values are not updated, with the following error, therefore we need also the 32 bit version
Installing the 32 bit version
Removing the previous DSN
Removing the 64 bit version of the MySQL Connector/ODBC
We are left with the 32 bit version only
Creating a new ODBC DSN with the 32 bit version of the connector
But we face the following problem
Reinstalling again the 64 bit version
Attempting to add a new data source
This time it works
Adding the link in Excel
Overwriting
Can remove from the folder of links the old files
Only the current file is kept
Changing the properties to refresh faster
Inserting an existing connection
And the result is correct
Checking whether it is ok after the update on the database side, as if you remember the lack of the 32 bit version caused an error message at the automatic refresh time.
mysql>
mysql>
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 4001 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql> update t set y=5001 where x=10;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 5001 |
| 11 | 21 |
+------+------+
2 rows in set (0.03 sec)
mysql>
No error this time
9. Conclusion
Both 32bit and 64bit drivers of MySQL ODBC are needed to be installed for the proper operation.
Copyright © 2013 Switzernet
END of the file