MySQL ODBC with Excel 2011 on OSX

January 24, 2011

I had a hard time getting the MySQL 5.1.8 ODBC driver to work with Excel 2011 on my work MacBook. I'm running OSX 10.6 (Snow Leopard), but this issue seems to go back some ways.

Apparently, the problem has to do with Microsoft Query being a native PPC 32 bit app, while Excel 2011 is an Intel native 32 bit app.

The solution was to use lipo to combine the distinct Mac Intel 32-bit ODBC driver with the PPC 32 bit driver.

To do this, download and extract the myodbc connector drivers from the MySQL site in tar.gz format. You need both the intel-x86-32bit and the ppc-32bit downloads. Extract them both next to each other in a tmp dir (/Users/drew/tmp, or whatever). Then, from your tmp dir, use lipo to make a multi-arch version. (To widen out the text area so you can see the full command, use my "hide panel" button to hide the right column of this page. Also, remember that backslash means "continue command on next line."):

$ lipo mysql-connector-odbc-5.1.8-osx10.5-powerpc-32bit/lib/libmyodbc5-5.1.8.so \
  mysql-connector-odbc-5.1.8-osx10.6-x86-32bit/lib/libmyodbc5-5.1.8.so \
  -output ./libmyodbc5-5.1.8.so \
  -create

This will create a new libmyodbc5-5.1.8.so in the current directory with both architectures supported. You can test this by running:

$ lipo ./libmyodbc5-5.1.8.so -info

You can then sudo copy this to /usr/local/lib on top of any other libmyodbc5-*.so.

Also, be sure that /usr/local/lib/libmyodbc5.so a symlink to the new file. I had an actual file there rather than a symlink and it caused some additional pain.

tags:excel, mac, mysql, odbc, office 2011, snow leopard

permalink | 7 comments

Comments

posted by Genís on Apr 04, 2011

Thanks a lot! This is really useful!! To create a symlink in the terminal: sudo ln -s /usr/local/lib/myodbc5-5.1.8.so /usr/local/lib/myodbc5.so

posted by Simon Westcott on Jun 11, 2011

Thanks for this. Is either the OpenLink/Actual Technologies driver still required with this?

posted by Drew on Jun 11, 2011

Simon, no, the Actual Technologies and Openlink drivers are not required.

posted by Chris on Jun 27, 2011

Thanks Drew, been looking for a solution to this for a while now, and this is the closest I've gotten to making MySQL Connector ODBC work with Excel 2011 (14.0) for Mac OS X 10.6.7. I must be missing an obvious step here. I followed the steps above to the letter, including adding the symlink, but I'm still getting the "No ODBC driver is installed" message in Excel when I try to create a new database query. Does the driver need to be somehow "registered" with Excel, or is placing it /usr/local/lib/ sufficient. I tried installing Rosetta, thinking that this may be needed, but that didn't help. Also the usual reboot. Any ideas? Thanks in advance!

posted by Drew on Jun 27, 2011

Chris, I don't remember the actual steps wrt Excel's setup, but it seems to me that I did reconfigure odbc for Excel once the new driver was in place. Couldn't hurt to try.

posted by Scott Arbeitman on Jul 04, 2011

Chris, you might need to download and use this: http://support.apple.com/kb/dl895

posted by Spica on Sep 20, 2011

Grateful for this post but doesn't seem to work for me. Am on 'OS X 10.7.1' using 'Excel 2011 for Mac'. was able to get to the following step $ lipo ./libmyodbc5-5.1.8.so -info Architectures in the fat file: ./libmyodbc5-5.1.8.so are: ppc i386 there wasn't lib dir on /usr/local, so created the dir and copied it over. Created the symbolic link as well. $ ls -l /usr/local/lib total 8416 -rwxr-xr-x 1 root staff 4303572 Sep 20 21:53 libmyodbc5-5.1.8.so lrwxr-xr-x 1 root staff 31 Sep 20 21:55 myodbc5.so - /usr/local/lib/myodbc5-5.1.8.s downloaded the odbc administrator for mac from http://support.apple.com/kb/dl895 as scott suggested. still no luck. excel still gives 'no odbc driver is installed' i had earlier registered and created a dsn for the 64-bit mysql odbc driver through the following commands. sudo ./myodbc-installer -d -a -n "MySQL ODBC 5.1 Driver" -t "DRIVER=/usr/lib/myodbc5.so;SETUP=/usr/lib/myodbc3S.so" sudo ./myodbc-installer -s -a -c2 -n "mysql" -t "DRIVER=MySQL ODBC 5.1 Driver;SERVER=xx.xx.xx.xx;DATABASE=DW_PROD;UID=xxxxx;PWD=xxxx" Any pointers? Thanks.
 

Comments are currently closed.

about

feeds

archives

tags

blogroll

Valid XHTML 1.0! Valid CSS!