Properties of Excel to remote MySQL connection
1. Table of contents
5. The Excel chart range adapts to the database area dynamically
6. Accessing data of the remote database
7. Copyright © 2013 Switzernet.com
2. Introduction
Several properties of the connection of an Excel worksheet with a remote MySQL server will be explored in this document.
We create a connection to a remote database from within the Excel worksheet (the ODBC driver and the link properties are already defined via the ODBC data source manager).
The properties of the connection are set to the max refresh frequency:
The data is inserted starting from the B3 as shown in the screenshot below
3. Adding new rows
In the following printout we add a new row in the database
$ mysql -h'37.187.52.44' -u'emin' –p****
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 5001 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql> update t set y=6001 where x=10;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 6001 |
| 11 | 21 |
+------+------+
2 rows in set (0.04 sec)
mysql> insert t (x,y) values (12,22);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 6001 |
| 11 | 21 |
| 12 | 22 |
+------+------+
3 rows in set (0.04 sec)
mysql> update t set y=7001 where x=10;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 7001 |
| 11 | 21 |
| 12 | 22 |
+------+------+
3 rows in set (0.03 sec)
mysql>
As we see on the Excel screen, the raw is also added in the Excel sheet, meaning that the area is not limited by the initial space defined at the moment of the importation.
Additional rows
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 7001 |
| 11 | 21 |
| 12 | 22 |
+------+------+
3 rows in set (0.03 sec)
mysql> insert t (x,y) values (12,22);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (13,23);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (14,24);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 7001 |
| 11 | 21 |
| 12 | 22 |
| 12 | 22 |
| 13 | 23 |
| 14 | 24 |
+------+------+
6 rows in set (0.04 sec)
mysql>
Continue to have the same effect
Deleting of rows from the remote MySQL
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 10 | 7001 |
| 11 | 21 |
| 12 | 22 |
| 12 | 22 |
| 13 | 23 |
| 14 | 24 |
+------+------+
6 rows in set (0.04 sec)
mysql> delete from t where x=10;
Query OK, 1 row affected (0.04 sec)
mysql> delete from t where x=11;
Query OK, 1 row affected (0.04 sec)
mysql> delete from t where x=12;
Query OK, 2 rows affected (0.04 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
+------+------+
2 rows in set (0.04 sec)
mysql>
Is also synched with the Excel worksheet (within a delay of 1 min)
4. Creating charts
The values in the remote database can be visualized by an Excel chart in the real time along with the changes of the values in the database.
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
+------+------+
2 rows in set (0.04 sec)
mysql> insert t (x,y) values (13,30);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (12,34);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (9,40);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (8,35);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (7,35);
Query OK, 1 row affected (0.15 sec)
mysql> insert t (x,y) values (7,32);
Query OK, 1 row affected (0.18 sec)
mysql> insert t (x,y) values (9,33);
Query OK, 1 row affected (0.20 sec)
mysql> insert t (x,y) values (10,35);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (12,39);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (14,42);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
| 13 | 30 |
| 12 | 34 |
| 9 | 40 |
| 8 | 35 |
| 7 | 35 |
| 7 | 32 |
| 9 | 33 |
| 10 | 35 |
| 12 | 39 |
| 14 | 42 |
+------+------+
12 rows in set (0.04 sec)
mysql>
5. The Excel chart range adapts to the database area dynamically
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
| 13 | 30 |
| 12 | 34 |
| 9 | 40 |
| 8 | 35 |
| 7 | 35 |
| 7 | 32 |
| 9 | 33 |
| 10 | 35 |
| 12 | 39 |
| 14 | 42 |
+------+------+
12 rows in set (0.04 sec)
mysql> insert t (x,y) values (15,49);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (18,52);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (20,55);
Query OK, 1 row affected (0.04 sec)
mysql> insert t (x,y) values (19,54);
Query OK, 1 row affected (0.05 sec)
mysql> insert t (x,y) values (19,54);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
| 13 | 30 |
| 12 | 34 |
| 9 | 40 |
| 8 | 35 |
| 7 | 35 |
| 7 | 32 |
| 9 | 33 |
| 10 | 35 |
| 12 | 39 |
| 14 | 42 |
| 15 | 49 |
| 18 | 52 |
| 20 | 55 |
| 19 | 54 |
| 19 | 54 |
+------+------+
17 rows in set (0.04 sec)
mysql>
The data range of the above charts is not changed manually.
As soon as the new rows are added the data range of the chart adapts automatically so as to cover the new rows.
The same is valid when removing rows from the database
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
| 13 | 30 |
| 12 | 34 |
| 9 | 40 |
| 8 | 35 |
| 7 | 35 |
| 7 | 32 |
| 9 | 33 |
| 10 | 35 |
| 12 | 39 |
| 14 | 42 |
| 15 | 49 |
| 18 | 52 |
| 20 | 55 |
| 19 | 54 |
| 19 | 54 |
+------+------+
17 rows in set (0.04 sec)
mysql> delete from t where x=19;
Query OK, 2 rows affected (0.04 sec)
mysql> select * from t;
+------+------+
| x | y |
+------+------+
| 13 | 23 |
| 14 | 24 |
| 13 | 30 |
| 12 | 34 |
| 9 | 40 |
| 8 | 35 |
| 7 | 35 |
| 7 | 32 |
| 9 | 33 |
| 10 | 35 |
| 12 | 39 |
| 14 | 42 |
| 15 | 49 |
| 18 | 52 |
| 20 | 55 |
+------+------+
15 rows in set (0.04 sec)
mysql>
Here is the chart adapting its data range dynamically to cover only the rows corresponding to the database’s table.
6. Accessing data of the remote database
The data of the remote table can be accessed with Excel formulas.
Here are several examples.
=SUM(Table_test_t[x])
=SUMproduct(Table_test_t[x],Table_test_t[y])
=max(Table_test_t[x])
=min(Table_test_t[x])
=VLOOKUP(10,Table_test_t,2,FALSE)
Copyright © 2013 Switzernet.com
End of the document