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.
5. Fill down all the necessary information. You can freely choose Data Source and Description. Other information should be given by your administrator.
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 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.