How to Build Excel reports on PostgreSQL database using DataDirect PostgreSQL driver for ODBC

In this blog we will walk you through the steps on how to make an ODBC connection from MS Excel to PostgreSQL and followed by importing the data from PostgreSQL to MS Excel. 

With the DataDirect suite of ODBC drivers, you can connect to a wide variety of high-value data sources in the market today. Whether you are using a traditional Relational Database Management System or a Big Data, Cloud or REST based sources, DataDirect has you covered.  

Progress DataDirect ODBC drivers are the industry standard for ODBC drivers, providing the advanced functionality, performance, and reliability developers need to quickly deliver high-quality, database-independent applications. 

Connecting to a Progress DataDirect ODBC driver from MS Excel is very straightforward. In this tutorial, we walk you through connecting MS Excel to PostgreSQL. These same steps can be used to connect MS Excel to any DataDirect ODBC driver. 

This tutorial assumes that you have ODBC drivers installed and a data source configured. If you have not yet done these steps, please follow these instructions. Ensure that Microsoft Excel and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Excel installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed.  

1. Download and install the Progress DataDirect ODBC driver using the 15-day trial software. In this tutorial, we are connecting to PostgreSQL Database.  

2. Configure a data source for your database. See the DataDirect documentation for assistance with setting up your data source. You will learn how to configure a PostgreSQL Data source in the following steps.

1. After the DataDirect PostgreSQL ODBC driver has been installed, open ODBC Administrator to configure the connection and click on Add.   

2. Choose the “DataDirect X.X PostgreSQL Wire Protocol” and click on “Finish.”

3. The ODBC PostgreSQL Wire Protocol Driver Setup window will pop up.   

Enter your credentials, click on “Apply” and then click on “Test Connect.”   

4. The PostgreSQL Logon screen will pop up. Provide logon credentials and click on “OK.”   

5. A dialog box will confirm that the connection is established.   

When the driver installation and configuration is completed, you can import data from PostgreSQL using MS Excel following the mentioned steps. 

1. Open MS Excel’s new workbook and navigate to the “Data” tab 
 
2. To import data, click on “Get Data” and navigate to “From Other Sources”-> “From ODBC” 

3. Select the PostgreSQL data source name (DSN) you want to connect to. Just in case if the ODBC DSN has not been set up, you can leverage the Advanced Options dialog box. Additionally, you can enter an SQL statement which will be executed immediately after the successful connection with PostgreSQL. 

 4. A dialogue box will appear asking for username and password. In the Database, enter your credentials and click on “Connect” 

 
5. A navigator will open. Choose the data you want to import and load the data into the workbook.  

 
6. Once the import of the data completes the data will be available in the Workbook for you to work on.  

7. Once you have the data in the workbook, you can start analyzing that with various analytical tools available as part of Microsoft excel e.g., below image shows recommended Pivot table reports that can be build up on the populated data. 
 
 
 

Get started today with a free 15-day trial of Progress DataDirect PostgreSQL ODBC driversand connect Microsoft Excel to all your data!   

Contact Us for assistance with any questions you may have, and we will be happy to help!