Connect Excel to Redshift on macOS

Step-by-step guide to connecting to Redshift via Excel using an ODBC connection.

Published in

Go City Engineering

4 min readAug 24, 2022

Excel is still widely used by businesses and BI teams in order to have a look at the data, perform analysis and checks. You can connect a workbook to a database of your choice via ODBC and retrieve data from the source. Being a product developed by Microsoft, it runs very smoothly on Windows, and it is pretty easy to use and to connect to a database, such as Redshift. But the same can not be said when it comes to running Excel on macOS.

In this guide I will walk you through the setup and by the end of it you should be able to query Redshift data from Excel.

Prerequisites

We will need 4 things:

ODBC Redshift driver
iODBC Driver Manager
sudo permissions
Redshift is accessible from your machine

Configuring ODBC files

The following configuration files can be set up at the system and/or the user level. We will create a user level configuration because we’re going to do something hacky, and it’s better to do so at the user level.

We need to write two configuration files odbc.ini and odbcinst.ini and place them under /Users/<user_name>/Library/ODBC/ folder (you may need sudo permissions to do that). Write the following files providing the relevant information for host, port and database. We will cover the Driver field shortly.

odbc.ini

sudo nano /Users/<user_name>/Library/ODBC/odbc.ini

Write the following into the file

[ODBC Data Sources]
Redshift_datasource=Amazon Redshift ODBC Driver[Redshift_datasource]
Driver=<driver>
Host=<host>
Description=Redshift datasource
Port=<port>
Database=<database>
locale=en-US

odbcinst.ini

sudo nano /Users/<user_name>/Library/ODBC/odbcinst.ini

Write the following into the file

[ODBC Drivers]
Amazon Redshift ODBC Driver=Installed[Amazon Redshift ODBC Driver]
Description=Amazon Redshift ODBC Driver for macOS X
Driver=<driver>

Moving Amazon Driver

After you installed the driver (see prerequisites) you should have a folder /opt/amazon/redshift/lib/ with libamazonredshiftodbc.dylib file inside it. Due to macOS nature (sandbox) Excel can not use drivers in any location other than in its frameworks folder, hence we will copy the driver there.

To do that open the terminal and run the following command (this assumes Excel is installed in the default location):

sudo cp -R /opt/amazon/redshift/lib/ /Applications/Microsoft\ Excel.app/Contents/Frameworks/amazon_lib

After it is done, set the Driver field in odbc.ini and odbcinst.ini files we’ve written earlier to be the absolute path (don’t escape spaces) to the libamazonredshiftodbc.dylib file, i.e.

Driver=/Applications/Microsoft Excel.app/Contents/Frameworks/amazon_lib/libamazonredshiftodbc.dylib

Testing Connection

Before we try connecting from Excel, we need to test the connection via ODBC Manager (see prerequisites). Open search bar and start typing ODBC.

Under User DSN you should see the Data Source we just added in our config files Redshift_datasource. Click on it and then click Test.

Connecting from Excel

If you’re on M1/M2 mac you have to run Excel under Rosetta 2. More on that here

You now can open Excel and click on Data tab and select Get Data.

Then under User DSN you should see the DSN we’ve just set up

Select it, provide credentials, and you should be able to connect to Redshift.
Now you should be able to run SQL queries and extract data to an Excel workbook. Just write the query and hit Return Data.