Exception in npgsql 3.2.0 connecting to PostgreSQL via Excel PowerQuery

Question

I have a PostgreSQL 9.6.1 database from which I want to extract data for use in Excel with Get & Transform (PowerQuery). I have 64-bit Excel 2016 Pro Plus running on Windows 10 64-bit with .NET framework 4.6 installed.

When I try to use "Data->New Query -> From Database -> From PostgreSQL Database", I put the server name (localhost) and DB name into the dialog box.

I then get the error message,

An error happened while reading data from the provider: 'The type initializer for 'Npgsql.Counters' threw an exception.'

I have the following setup:

• npgsql 3.2.0 installed with nuget. There are 3 versions of the DLL, net45, net451 and netstandard1.3. I used net451 for no other reason that it was closest to 4.6.
• mono.security.dll 4.0.0.0. I added both of the DLLs to the global cache with gacutil.exe

• I updated machine.config as follows for DbProviderFactories

  add description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" name="Npgsql Data Provider" invariant="Npgsql" support="FF"

Could anyone help me understand if I did anything wrong?

Answer 1

It looks like this is an issue with a recent npgsql release? Until that's fixed, if you install version 3.1 or 3.0 Power Query should work :)

One other thing you could try is using net45. We officially support .NET 4.5.1 but sometimes you have more luck with older releases.