ODBC MySQL Connection for Excel on MacOS

I have numerous Excel workbooks that I use to keep track of HR-related information. In order to minimize data duplication, I have consolidated these records into as few files as possible, but some of the core information such as Names, Employee ID numbers, and Job Titles are necessarily duplicated between files.

Wouldn’t it be great if the main information could just be updated in one place and then propagate across various systems and files? I’m working on porting all of my Excel files into a web-based system, but it’s really a side-project, so in the meantime I wanted a solution to reduce this duplication, and I think I may have found a realistic solution.

Why not keep the main employee information, which is currently duplicated across several Excel files, in a MySQL database and then link this into each of my Excel workbooks so that any update to these data will automatically update in all the files. As an added bonus, this database could later form the core of the web-based system, allowing me to implement features incrementally.

I already have a MySQL server on the local network that synchronizes attendance data from our fingerprint attendance device. So I’m going to use the employees table in that database as the source for my Excel files to get their employee data.

Here are my notes of how I got it working on Excel 16.32 running on Mac OS 10.15.1 connecting via LAN to MySQL 5.7.28 on Ubuntu 18.04 LTS.

Setup

On MySQL server

By default, MySQL will only accept connections from the local machine, and the default recommended privileges for a new user also include @localhost.

Side note:
You can check what ports and addresses your MySQL is currently listening on with:

$ netstat -tulpen

To make sure you will be able to connect (leaving any firewall configuration changes up to you):

1. Change the default bind-address:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

On some versions you may need to use /etc/mysql/conf.d/mysql.cnf or /etc/mysql/my.cnf.

Different versions appear to work differently as well, so either comment out the existing bind-address:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

or change the existing bind-address line to:

bind-address = 0.0.0.0

Then restart mysqld

$ sudo /etc/init.d/mysql restart

2. Add a new user with the @ set to your computer’s IP address if you have a static IP, or else use a wildcard for computers on your local subnet:

GRANT ALL PRIVILEGES ON *.* to 'remoteuser’@’10.1.1.%' IDENTIFIED BY 'remoteuserspassword' WITH GRANT OPTION;

Obviously, adjust as necessary, and if copying and pasting make sure to double-check that quote marks and dashes etc haven’t been autocorrected to pretty versions.

On MacOS

1. Install ODBC Manager (Freeware alternative to ODBC Administrator)
   http://www.odbcmanager.net/
2. Install the MySQL connector
   https://dev.mysql.com/downloads/connector/odbc/
3. Move the installed connector from local to global library
   https://stackoverflow.com/questions/52896893/macos-connector-mysql-odbc-driver-could-not-be-loaded-in-excel-for-mac-2016 quoted below:
   

   I was able to connect to MySQL with Microsoft Excel Version 16.19 by making the following adjustments:
   1. I copied the driver’s whole directory /mysql-connector-odbc-8.0.12-macos10.13-x86-64bit to /Library/ODBC (why? see https://bugs.mysql.com/bug.php?id=89931, and Is there a way to get ADODB to work with Excel for Mac 2011?) ; basically the reason for that is the driver gets downloaded to /usr/local and Excel, being sandboxed, cannot access that location.
   2. Updated the file odbc.ini, also located in /Library/ODBC, with the driver’s new location.

4. Run ODBC manager and add your remote MySQL database with options SERVER and PORT set appropriately (see the default entries for syntax). Click on Test to make sure the connection works.

Making the Connection

Open Excel and navigate to the Data tab, select New Database Query, From Database.

In the iODBC Data Source Chooser window that opens, select the new ODBC Data Source you just added in ODBC manager and click OK. Enter your new MySQL username and password when prompted.

Enter a single-line query and click on Run. If no errors occur (and reasonable results appear in the preview pane) then send the results back to Excel by clicking on Return Data. Choose whether to add the results to the current worksheet, a new worksheet, or a pivot table. You can also click on Properties to choose when and how frequently the data will be refreshed. Note that if the database is on an intranet server and you open the Excel sheet from home, for example, the file will still open but it will just use the version of the data from when it was last opened.