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 🙂

You might also like

More Similar Posts

2 Kommentare. Hinterlasse eine Antwort

Hi Stefan, thank you very much for your post! I’ve been trying to make this work for 3 days until I found your post. Works like a charm

Antworten

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.

Menü

Die auf der Webseite eingesetzten Cookies haben wichtige Funktion für unsere Webseiten inne und erleichtern die Bereitstellung unserer Dienste. Mit der Nutzung unserer Dienste erklären Sie sich damit einverstanden, dass wir Cookies einsetzen. Klicken Sie auf "Zustimmen" um den Einsatz der Cookies zu akzeptieren. Weitere Informationen können Sie unserer Datenschutzerklärung entnehmen. Datenschutzerklärung

Die Cookie-Einstellungen auf dieser Website sind auf "Cookies zulassen" eingestellt, um das beste Surferlebnis zu ermöglichen. Wenn du diese Website ohne Änderung der Cookie-Einstellungen verwendest oder auf "Akzeptieren" klickst, erklärst du sich damit einverstanden.

Schließen