PostgreSQL to Microsoft Excel via Npgsql

02.2024 / Category: / Tags:

This article is dedicated to all the Excel heroes out there who want to integrate and use data stored in PostgreSQL. This time we’ll cover all the steps needed to do so by utilizing Npgsql, which is an open source ADO.NET Data Provider for PostgreSQL. To learn more about this versatile driver, check out its main project page or directly jump into the code base.


Let me begin with the needed requirements to follow up on this quick tutorial accordingly. Apart from an accessible PostgreSQL server, an appropriate Excel version or license is required to connect to PostgreSQL via Npgsql and not through the common ODBC protocol. To verify your license, open Excel and go through the following menu entries: Data – Get Data – From Database. The resulting container should include an entry for PostgreSQL. Check out figure 1 to see how this looks in my environment.

excel_Import_Dialog for Npsql
Figure 1 Excel license

What else is needed to continue? Please find below a summary of my environment.

  • Client
    • Operating System: Windows 10 Pro
    • Software: Microsoft Office Professional Plus 2019
  • Server
    • Operating System: Ubuntu 22.04.3 LTS
    • Software: PostgreSQL 16

Let's start our setup for the Npgsql integration

To use Npgsql as a database provider for Microsoft Excel, the driver must be installed in the Global Assembly Cache first. To make this task as smooth as possible, we’ll pick one of the available but older MSI installers of Npgsql to outsource this job.
First, we download the driver from and install the package on Windows. The driver should now be available for Excel too. Let’s verify this:

  1. Open up Excel.
  2. Go through Data > From Database > From PostgreSQL Database (see image 2).

If everything goes well, Excel will continue and ask for connection details and credentials.

PostgreSQL_Connection with Npsql
Figure 2 Connection details

Unsecured Connection

Let’s start easy and connect from Excel to PostgreSQL without using SSL. I recommend testing the unsecured connection via psql first before moving on to Excel. To do so, we hand over the parameter sslmode=disabled on client the side as follows:

Great – this works out. In Excel, we step in, enter connection details and credentials, and finally try to connect. The upcoming image series documents these steps briefly:

Connection_Details of Npsql

Further Connection_Details of Npsql

Connect_Error by Npsql
Figure 3 Connection details, SSL error

Uuups – this does not seem to work. The error message suggests that Excel connects to PostgreSQL with SSLMODE enabled by default. Any chance to change this parameter? We try our luck in Excel’s Data Source Settings (Data Data Source Settings). From here on, we can disable the SSLMODE of our connection by unchecking Encrypt connections. Image 4 visualizes this task in detail.

Data Source Settings
Figure 4 Data source settings

Our next connection attempt (see image 5) succeeds and brings up Excel’s navigator pane designed for further data processing. Mission accomplished.

Navigator Pane by Npsql
Figure 5 Data navigator

Secured Connection

Let’s dig deeper and improve our setup by securing connections between Microsoft Excel and PostgreSQL. I expect some basic knowledge regarding PostgreSQL’s SSL capabilities.
To refresh your knowledge, I highly recommend PostgreSQL’s own documentation and a blog post from my boss, Hans Schönig.
To keep it simple, we will generate a self-signed certificate on the server and use it as a trusted root certificate on the client.

The overall process consists of the following steps:

  1. Server
    1. Generate and deploy self-signed certificates on the server.
    2. Adopt the PostgreSQL configuration.
  2. Client
    1. Deploy the generated certificate to the trusted root certification authorities on the client.
    2. Change the connection mode to secured.

Firstly, we generate our self-signed certificate and store it in PostgreSQL’s data directory, where the system will look after it by default. We generate the certificate and key as user postgres or use chown to change ownership accordingly.

Next, we adopt our PostgreSQL configuration in postgresql.conf, point to our files, and finally restart the database server.

Before deploying our certificate to Windows, let's quickly verify its correctness by connecting via psql.
To use our server certificate as a trusted root certificate, we copy and rename server.crt as root.crt to directory .postgresql, where psql will look after certificates by default.

Finally, we test the connection.

Amazing! The output confirms that we are connecting with SSL mode enabled. By renaming ~/.postgresql/root.crt we can cross-check our configuration.

We are still not tired and copied our certificate root.crt to Windows. The certificate can be added to the trusted root certification store as follows:

  1. Right-click on root.crt and choose install certificate.
  2. Choose store location: User/Local Machine.
  3. Manually choose a cert store: trusted root certification authorities.
  4. Trust our ca.

The upcoming image series documents this task.


Install_Cert_Loc Step 1

Install_Cert_Loc Step 2

Install_Cert_Loc Step 3
Figure 6 Certificate deployment

Now it’s time to revert our PostgreSQL connection settings in Excel and enable “Encrypt connection” again. That’s it. One more time, we open Excel’s data navigator (see chapter on unsecured connection) to verify that everything works as expected.
Still not enough? As homework, force your clients to use SSL by adopting pg_hba .

Conclusion to the integration of Npgsql

Today we connected Microsoft Excel to PostgreSQL via Npgsql. This task sounds easy in the first run, but there are some stumbling blocks you might encounter. This post guides you step-by-step through this process and subsequently enables you to concentrate on your Excel magic instead of dealing with frustrating connectivity issues.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram