Getting data from InterSystems IRIS CloudSQL using xDBC

Recently, the question came up while discussing the access to the data stored in IRIS from different languages with my students if it was possible to initiate the connection and get data from Cloud solution (InterSystems IRIS CloudSQL) from Microsoft Excel, not the other way around. Considering the many varied ways one can get data in Excel (import data from external sources, connecting to databases using ODBC drivers, using power queries and web queries etc.) the obvious choice was to try ODBC driver. The only task left was to try to connect to the database in the cloud using the ODBC driver.

Quite unsurprisingly, it worked!

If you're new to CloudSQL, I would highly suggest you read the documentation. From it you will be able to get access to the InterSystems Cloud Services Portal (or you can go directly to AWS and subscribe with your AWS account) and download necessary drivers. BTW, if you encounter problems with CloudSQL, you may try to look for answers in this document first.

Now that these preliminaries are out of the way, let's try the simplest approach - setting up the ODBC data source with just a password and getting data from it in MS Excel.

Step 0. Set up your InterSystems IRIS CloudSQL. You will see the necessary settings on the Overview page of your Deployment:

Step 00. Download and install ODBC driver for your OS.

Step 1. Open your ODBC Data Sources and switch to System DSN tab. In it, click on Add...

and fill in the settings from your Overview page (first screenshot of the article).

Step 2. Connect Excel to IRIS. Open Excel, navigate to the Data tab, and select "Get Data" or "From Other Sources," depending on your Excel version. Choose "From ODBC" as the data source

select the ODBC data source you configured earlier

enter authentication credentials if asked

and choose the table you wish to load into Excel

Step 3. You're good to go and do whatever with your data

PS. Some other interesting articles/videos that I would suggest you read/watch regarding ODBC and CloudSQL:

• a whole series of videos that show how to connect to CloudSQL from solutions written in different languages
• an interesting article on how to set up roles and give permissions to xDBC connections
• another article on how to use SSL/TLS to establish secure connections over JDBC and other driver technologies
• and the last one about switching between ODBC drivers in an environment with multiple versions installed