Introduction

Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from Open Group and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.

The designers of ODBC aimed to make it independent of database systems and operating systems.

ODBC accomplishes independence by using an ODBC driver as a translation layer between the application and the database systems.

Pilot Things is based on PostgreSQL database. PostgreSQL provides standard drivers for ODBC access (see https://odbc.postgresql.org/)

The following chapter provides an example with Excel.

You must ask Pilot Things support to have this feature enable for your account.

Setup ODBC with Excel on Windows

Step 1: Check Microsoft Excel version

Start Excel and click the File/Help menu. Check the version under ‘About Microsoft Excel’ on the right. (This version is 32bit)

Step 2: Download PostgreSQL ODBC driver

Find the download that matches your version from the PostgresSQL site and install it. https://www.postgresql.org/ftp/odbc/versions/msi/

Since the Excel version is 32bit, install psqlodbc_13_01_0000-x86.zip. (If the Excel version is 64bit, install psqlodbc_13_01_0000-x64.zip.)

The zip file contains the following two files.

Step 3: Install PostgreSQL ODBC driver

Install the driver by executing the installation file (psqlodbc_x86.msi).

Step 4: Setup ODBC data source

Enter ‘odbc’ in the Windows search bar to find the “ODBC Data Sources (32-bit)” App and run it.

Then, click the System DSN tab.

Click the Add button, scroll down and select “PostgreSQL Unicode” and click the Finish button.

Enter “PTH_DS” in the Data Source field, enter the connection information provided by Pilot-Things in the other columns, and click the Test button.

A message indicating that the connection is complete is displayed on the screen.

Click the OK button, and then click the Save button to save the connection information.

Step 4: Add Data Source in Excel

In Excel 2010, select the “From Other Sources” tab from the Data menu of Excel. Select “From Microsoft Query” from the drop down menu.

In Office 365 it looks like this:

Select PTH_DS

Select the table you want to access.

Data is displayed on the screen.