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 🙁

You might also like

More Similar Posts

11 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

Hi Stefan, Does this work for Excel Office 365 version?

Antworten

Dear Stefan,
many thanks for sharing your knowledge.
I managed to:
– install everything as described by you
– create the excetuable script and run it and got:

Last login: Sat Nov 14 13:50:07 on ttys001
/Users/[…]/connection_script_excel_mysql ; exit;

The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.
(base) […]MacBook2:~ […]/connection_script_excel_mysql ; exit;
logout
Saving session…
…copying shared history…
…saving history…truncating history files…
…completed.

[Prozess beendet]

I could:
– start the iODBC Data Source Administrator (64-bit Edition)
– add a user
– and test itsuccessfully.

When I start Excel and add a new data base query (german: “Neue Datenbankabfrage”, I choose:
– SQL Server ODBC

Servername: 127.0.0.1
Database name: xxx
Method: username / password

and finally get:
“[SqlServerODBC] (15) Fehler beim Herstellen einer Verbindung mit der SQL Server-Instanz / Error while connecting to the SQL Server instance: [HY000]: Connection broken unexpectedly”

Do you have an idea, what the problem could be?

Many thanks!
Gernot

Antworten

Hi Stefan,

Many thanks for such exhaustive guide. However, I got stuck at the very last step – testing the DSN User setup. When using all know credentials, the Test button returns “[MySQL][OBDC 8.0(w) Driver]Lost connection to MySQL server at “reading initial communication packet”… Would you have any idea of what may be wrong?

Thanks,
Daniel

Antworten
Jeff McCarty
15. Oktober 2022 22:44

I just tested this on an M1 system with the most current version of Excel and MacOS 12.6. I was eventually able to get it to work. First off, I had to use ODBC Manager from http://www.odbcmanager.net/ since iODBC Administrator didn’t play nice with the ARM ODBC drivers.

I also discovered that Excel can’t see the UserDSN’s and SystemDSN’s that are set up in ODBC Manager, and it crashes if you try to set up a UserDSN or SystemDSN from inside of Excel. FileDSN’s however, will work just fine.

Maybe a little more tinkering will uncover the reason why UserDSN’s and SystemDSN’s don’t work.

Antworten

Kommentar verfassen

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

Menü