MySQL ODBC Excel Connection Set up Worked Example



Errors to start with...

Having set up a data source in Windows 10 I found that I got the errors in Excel:

Microsoft Data Link Error
Test connection failed because of an error in initialising provider
Unspecified error

Although the MySQL database was 64 bit, for excel to import it seem to be required to use the 32 bit version of MySQL ODBC connector

(See the sources and links for this information at the bottom of this page).

Below is a worked example from installation of ODBC driver, setting up the Data Source, through to importing data from a MySQL database to Excel.

Step 1 - Reinstall MySQL Connector (32 bit)

Go to Download Connector/ODBC page
Download 32-bit MSI Installer Windows (x86, 32-bit), MSI Installer

http://dev.mysql.com/downloads/connector/odbc/

MySQL download Connector







































Step 2 - Run odbca32 with SysWOW64 path

Run the Windows command (pressing the windows button between Ctrl and Alt and at the same time letter R)

Paste in:
C:\Windows\SysWOW64\odbcad32.exe
And press ok



odbcad32















Step 3 - ODBC Data Source Administrator (32 bit)

Click on the Add button

ODBC data source administrator

























Step 4 - Create New Data Source

Scroll down to
MySQL ODBC 5.3 ANSI Driver
and select this driver and click on Finish

MySQL Create ODBC Source





















Step 5 - MySQL Connector/ODBC Data Source Configuration

Data Source Name: Can be any name you like although having _32 at the end is helpful. It will be the name you see in Excel
Description: Can be anything meaningful.
TCP/IP Server: For a local PC installation the localhost will work
Port: The default is 3306 for MySQL
User: This should be a valid working user already set up in MySQL
Password: This should be a valid working password already set up in MySQL

After adding the password click on Test to check the connection works.
After testing connection select a database and then press ok.
MySQL ODBC Excel

























Step 6 - Excel Data Connection Wizard

In Excel click on
The DATA tab
Then
From Other Sources
Then
From Data Connection Wizard
Excel Data Connection Wizard



































Step 7 - Welcome to the Data Connection Wizard

Choose
ODBC DSN
Then click Next

Data Connection Wizard





















Step 8 - Connect to ODBC Data Source

Select the data source name that you set up previously.
Then click Next.

Data Connection





















Step 9 - Select Database...

At this point you should be able to import from the database and tables..

select database





















Links to web pages that were helpful...

https://glocksoft.net/support/kb/articles/72-error-in-initializing-provider-data-source-name-not-found-and-no-default-driver-specified

https://jchaven.wordpress.com/2013/07/01/beware-32bit-and-64bit-odbc-data-source-administrators/

MySQL and Excel Versions for this illustration

MySQL:
version 5.6.33
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os Win64

Microsoft Excel 2013 running on Windows 10


Disclaimer

This page was created due to few illustrations being available of how to set up an ODBC connection from Excel to a MySQL database.
It is intended for assistance in learning, test or development environments only.

No liability will be accepted as a result of adverse effects of using information on this webpage in commercial environments.

Page created on 26th Sept 2016
Push Symbol. Push Symbol. Door logos