A summary of MySQL iODBC MacOS Installation
Tags: macos, mysql, odbc, excel
Published: 2021-01-16 19:33:15

This is a summary mainly for myself _(to find later if lost or need a newly mac setup)_ and also to anyone who want to connect to a mysql database and run queries in Microsoft Excel. More specifically described and main author and content created by **Stefan Schaffner** without whom I would not be able to get this working. [(Content Link)](https://stefan-schaffner.de/posts/using-odbc-driver-on-macos-and-excel-to-access-a-mysql-database/)

* Download and install the latest stable version of iODBC for MacOS. You can't go wrong with the DMG file. [http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads](http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/iew41l1tqwl2ni4uyb61.png)
* Download and install the latest version of ODBC MySQL connector and same as before DMG file works fine. [https://dev.mysql.com/downloads/connector/odbc/](https://dev.mysql.com/downloads/connector/odbc/)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xzfcww4s1r9phpq6od3d.png)
* Now for the main part, as Microsoft office runs in a sandbox in MacOS it can't get access to any ODBC install paths and that's a real pain for us all. What we need is to move the ODBC installation to  /library which was originally in /usr/local. So a simple script made by **samsgit's** [Github Comment](https://github.com/openlink/iODBC/issues/29#issuecomment-426790551) works like a charm.
So what I did,
```
$ sudo nano odbc
```
Copy and paste the whole script. **Created by samsgit**,
````
#!/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"
```
Save the file and run the following command to give executable permission,
```
$ sudo chmod a+x odbc
```
Now just let the magic happen by running the script,
```
$ ./odbc
```
* Finally, just open your iODBC Administrator from applications.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hvjh0k44klmpwn86nkjc.png)
Now `Add` a new `User DSN` > Select a Driver example:`MySQL ODBC 8.0 Unicode Driver` > Give a name and set keys for `user`, `password`, `server`, `database` and `port`.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gge8lh1drldvyvpn0z52.png)
* Now we are good to go. `Open excel` > `Data` > `New Database Query` > Select your saved `DSN`.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fn4yp49miihkjkj641gw.png)
Now you just write your query and enjoy your report in you Microsoft Excel on MacOS.

Reference:
Content instruction followed from **Stefan Schaffner**
[(Content Link)](https://stefan-schaffner.de/posts/using-odbc-driver-on-macos-and-excel-to-access-a-mysql-database/)