Connect Raspberry Pi IOT Database With MS Excel - Set Up

In the world of IOT data capture, one creates a lot of data which is invariably saved in a database system such as Mysql or Oracle. In order to get access to, and manipulate this data, one of the most efficient methods is using Microsoft Office products. The purpose of this instructable is to show how to connect a Raspberry Pi hosted mysql database with MS Excel on a windows laptop.

BOM

1. Raspberry Pi (RPi) - which is wi-fi enabled, has a Linux O/S installed and is connected to the Internet (I used a RPi zero in server mode). I am assuming that you know how to install an operating system on Raspberry Pi. Check this link to find the latest image(s) https://www.raspberrypi.org/downloads/. Noobs or Raspbian are both Linux images which will work.

Here is the version of the RPi Operating system I used. I elicited this by running this command in Putty. lsb_release -a
No LSB modules are available. Distributor ID: Raspbian Description: Raspbian GNU/Linux 8.0 (jessie) Release: 8.0 Codename: jessie

2. Windows laptop with MS Excel installed (will also work on Apple devices and Linux desktops)

3. Putty - This is a terminal emulator which permits you to access the RPi Linux environment from your Windows Desktop.

Log onto your RPi using Putty and then do the following:

1. Set up Apache - (Not strictly necessary but you might want to use a webform later)

Type the following commands -

sudo apt-get update
sudo apt-get install apache2

sudo a2dismod mpm_event

sudo a2enmod mpm_prefork

sudo systemctl restart apache2 (this restarts apache) .

2. Set up MySql

type - sudo apt-get install mysql-server

(Input a secure password when prompted by the installation). (Run mysql_secure_installation to remove the test database and any extraneous user permissions added during the initial installation process:

type - sudo mysql_secure_installation

(It is recommended that you select yes (y) for all questions. If you already have a secure root password, you do not need to change it.)

3. Install sample database - We are going to use Sakila for this - see https://dev.mysql.com/doc/sakila/en/

On the RpI command line , type the following

cd /tmp

sudo wget http://downloads.mysql.com/docs/sakila-db.tar.gz

sudo tar -xvzf sakila-db.tar.gz

THIS SHOULD DISPLAY

sakila-db/

sakila-db/sakila-data.sql

sakila-db/sakila-schema.sql

sakila-db/sakila.mwb

now type ,

cd sakila-db

sudo chmod 755 *.*

NOW LOG ON TO THE MYSQL SHELL IN PUTTY

mysql -u root -p (you will be prompted for the password you set up during the mysql installation.

now type,

mysql> SOURCE /tmp/sakila-db/sakila-schema.sql;
mysql> SOURCE /tmp/sakila-db/sakila-data.sql;

mysql> USE sakila; Database changed mysql> SHOW TABLES; (YOU SHOULD SEE)

+----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 22 rows in set (0.01 sec)

You can verify that the tables contained data by saying typing select * from payment;

NEXT CREATE A MYSQL USER type

mysql> CREATE USER 'sakila_test'@'%' IDENTIFIED BY 'your_password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sakila_test'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

Type exit to leave the Mysql shell to return to the Putty shell

Next, type sudo nano /etc/mysql/my.cnf

and commnet out the line to show #bind-address = 127.0.0.1

Now exit the Putty shell.

Goto - https://dev.mysql.com/downloads/connector/odbc/5.3...

Download the relevant file as per the image above.

Next configure it on Windows. On Windows 10 - click windows icon - bottom left - then click on the cog (second icon from bottom) and type odbc into the search box and select odbc data sources (32 bit) then follow the instructions as shown in the image ODBC SET UP 1 - Next follow the steps shown in image ODBC SET UP 2 - using the settings which you used previously , including your RPi hostname + your Mysql credentials

Follow the steps in the image Excel 1 - Open a worksheet in Excel and then select the Data menu, then Get Data,Other sources, ODBC - and select your data source. Next, enter the Mysql user name and password and then connect to your data source, eg sakila from the drop down and click ok. . If you click the arrow on the data source names then the tables in the sakila database will appear. When you select a table and click on the load button, the table will appear in MS Excel.

That's it, good luck.