Using ODBC driver on MacOS and Excel to access a mySQL database

Ever tried to access a mySQL database within Excel on macOS using the ODBC driver (64bit)? – I tried and failed. Here is my way out of the mess.

Using a Windows environment it is so pretty simple: Download ODBC driver for Windows from the mySQL Webpage, install it, open the system control -> ODBC configuration, add a DSN using input fields, test it. Next start Excel, external datasource, select DSN and voila: full access to the database.

I’ll spare you all the details of failure and just list a few of the error messages on the way to success:

The installation failed. The Installer encountered an error the caused the installation to fail. Contact the software manufacturer for assistance.

Error message if you want to install the MySQL Connector/Odbc

[iODBC][Driver Manager]Specified driver could not be loaded

Error message in Excel, if you want to address the database

And now, how it worked for me

iODBC Environment

First of all you have to install the iODBC (3.52.12 or later) environment on the OS X system before your can install the Connector/ODBC. You’ll find the installation packet here:http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads. Just download and install it. You’ll find the iODBC Administrator in „Programs -> iODBC“

ODBC/Connector mySQL

Before using the iODBC Administrator download an install the ODBC/Connector for MySQL. You’ll find the installation packet here: https://dev.mysql.com/downloads/connector/. Now that the iODBC package is installed, this installation works without the error message „The installation failed“.

Moving the ODBC/Connector

And now a step that makes the use with Excel possible. The Microsoft Office solution under macOS is a Sandboxed application and doesn’t have access to the default installation path of the ODBC/Connector (/usr/local/mysql-connector-odbc …..). You’ll have to move the installation to /Library/ODBC … And you have to do some more modifications. I found the following script (Sam/samsgit on guthub.com) doing this in a perfect way:

#!/bin/bash
 
# https://github.com/openlink/iODBC/issues/29
# https://bugs.mysql.com/bug.php?id=89931
 
base_src_dir="/usr/local"
mysql_odbc_name=$(ls "$base_src_dir" | grep -m 1 "mysql-connector-odbc")
odbc_dir="/Library/ODBC"
 
src="$base_src_dir/$mysql_odbc_name/lib"
dst="$odbc_dir/$mysql_odbc_name/lib"
 
echo "creating '$dst'"
sudo mkdir -p "$dst"
 
echo "copying '$src' to '$dst'"
sudo cp -af "$src/." "$dst"
 
 
odbc_ini_path="$odbc_dir/odbc.ini"
odbc_ini_bak_path="$odbc_ini_path.bak"
 
odbcinst_ini_path="$odbc_dir/odbcinst.ini"
odbcinst_ini_bak_path="$odbcinst_ini_path.bak"
 
echo "backing up '$odbc_ini_path' to '$odbc_ini_bak_path'"
sudo cp -f "$odbc_ini_path" "$odbc_ini_bak_path"
 
echo "backing up '$odbcinst_ini_path' to '$odbcinst_ini_bak_path'"
sudo cp -f "$odbcinst_ini_path" "$odbcinst_ini_bak_path"
 
# https://stackoverflow.com/a/29626460
function replace {
  sudo sed -i '' "s/$(sed 's/[^^]/[&]/g; s/\^/\\^/g' <<< "$1")/$(sed 's/[&/\]/\\&/g' <<< "$2")/g" "$3"
}
 
ansi_driver=$(ls "$dst" | grep -m 1 "^lib.*a\.so$")
unicode_driver=$(ls "$dst" | grep -m 1 "^lib.*w\.so$")
 
old_ansi_path="$src/$ansi_driver"
new_ansi_path="$dst/$ansi_driver"
 
old_unicode_path="$src/$unicode_driver"
new_unicode_path="$dst/$unicode_driver"
 
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbc_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbc_ini_path"
 
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbcinst_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbcinst_ini_path"
 
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbc_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbc_ini_path"
 
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbcinst_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbcinst_ini_path"

Just open a terminal window, create a new file with an editor (eg. vi <script name>) and paste the script above. After saving the file make the script executable for your OS (eg. chmod a+x <script name>). Last but not least, run the script (eg. „./<script name>).

Configure the ODBC/Connector

Now you can configure the DSN in the iODBC Administrator. Just add an User DSN:

And it works

Now you can start Excel and navigate to „data“ -> „new query“ -> „Database“ and select your DSN entry 🙂

Update 6/2021

I have reproduced this setup today (June 2021) with the current drivers and Microsoft program versions. Unfortunately, Microsoft has changed the way the ODBC drivers are integrated in the current versions. All tests on the ODBC driver run positively, but unfortunately, it does not work in Excel. 

Microsoft now refers to a paid ODBC plugin from a third party. Apparently they want to push this and have in this course prevented the alternative 🙁