Connect Microsoft Excel to InterSystems IRIS via ODBC (Windows)

How to create an ODBC connection on your native Windows laptop to IRIS running on a Windows VM on the same computer, test the connection, and pull data from IRIS into Excel.

Recently I learned that Excel can connect to external databases via ODBC. This includes basically any ODBC data source. Since IRIS speaks ODBC via the ODBC API, we can take advantage of the InterSystems ODBC Driver to establish an ODBC connection to IRIS on Windows that Excel can utilize.

Before getting started, make sure you have installed the latest InterSystems ODBC driver on your native Windows, which can either be found on WRC or on GitHub. I assume that you are running IRIS on a Windows VM using VMWare hosted on your Windows machine. I also assume that you have Microsoft Excel installed on your native Windows as well. You’ll also need some data pre-loaded into your namespace to access. For this post, I am using data from the Python Quickstart. The data can be found on GitHub.

First you need to find the IP address for the VM. (Keep in mind that you may need to repeat this step if you restart the VM, connect to a different network or connect/disconnect a VPN connection.)

To find your IP address in the Windows VM, go to the Windows Command Prompt (cmd):

1. In the VM, hold the Windows key and the R key. This opens the Run dialog (as seen below). Type in “cmd” and click <OK>:

2. On the Command Prompt, type in “ipconfig -all” to find your IP address. You want to locate the line that says “IPv4 Address….” And make note of the numeric IP address given on that line:

3. Also make note of the Web Server Port of your VM IRIS, because you’ll need that later.

Now, return to the Windows host system and open the ODBC Data Source Administrator (ODBC DSA) and configure the connection:

1. In the ODBC DSA, open the “System DSN” tab and click “Add”:

2. You will be prompted to select a driver for the data source. If you installed the InterSystems ODBC driver on your native Windows machine, you should select “InterSystems IRIS ODBC…” as I have done below and click “Finish”:

3. This will open a new dialog where you need to name the data source, provide a description, input the IP address you copied previously and the Web Server Port. Input the namespace you want to connect to, the User Name and Password that you use for the Management Portal as well. Then click <Test Connection> as shown below:

4. If all went well, you should see that the test completed successfully as shown below. This means that we now have a connection via our native Windows to IRIS on the VM:

Now we can open Microsoft Excel and access data via the ODBC data source. We do this by selecting the “Data” tab (1), then clicking “Get Data” (2). Click “From Other Sources” (3), and then “From ODBC” (4).

From the drop down, select the data source you created previously and click OK:

In the Navigator, select the table you want to connect to and click “Load”:

From here, we can see that we successfully connected to the data on IRIS on a VM and are able to access it in Microsoft Excel running on native Windows:

Wrapping up, we might wonder why we would want to access IRIS data in Excel. Many people have years of Excel expertise. They can use Excel to explore data, create pivot tables, visualizations, and dashboards.