connect to PostgreSQL database with Excel or Power BI

How to connect to PostgreSQL database with Excel or Power BI

Here is how to connect to the PostgreSQL database with Excel or Power BI using the ODBC data source.

First of all, you will need:
– Hostname or IP address (server).
– PostgreSQL database name and specified port.
– Database username and password.

Follow these steps to connect to the PostgreSQL database with Excel or Power BI

 

1. Download and install PostgreSQL ODBC driver.

2. Check the bit version of your Excel or Power BI. That is important to configure your ODBC data source properly.

In Excel go to File -> Account -> Click on About Excel icon.

In Power BI go to Help -> About.

3. Click on the Windows start button and type “odbc” and choose the right bit version of ODBC Data sources.

4. In the ODBC Data Source Administration window, click on the Add button and find the previously installed Postgre SQL driver.

PostgreSQL Windows ODBC data sourc

5. Fill down all the necessary information. You can freely choose Data Source and Description. Other information should be given by your administrator.

PostgreSQL Windows ODBC data source setup

6. Click on the Test button. If everything is good, then there is information that the connection is successful. If it is not, then check some of the possible errors below.

Connect to PostgreSQL with Excel or Power BI

In Excel go to the Data tab, click on Get Data -> From Other Sources -> From ODBC and choose your previously created ODBC data source.

PostgreSQL-Excel

PostgreSQL error: no pg_hba.conf entry for host

If the PostgreSQL user is not configured properly, then you might get this error. Please contact your administrator.

PostgreSQL error: pgbouncer cannot connect to server

Some of your connection parameters might be incorrect. For example, check the spelling of your database name. If that doesn’t help, contact your administrator.

 

By the way – did you know this Excel trick? Convert TRUE and FALSE values to 1 and 0 in Excel

Here is some interesting analytics that you can do in Power BI – How to identify outliers in Microsoft Power BI.

 




Posted

in

,

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *