Connect to PostgreSQL database from Excel 2013 Power Query with Npgsql

Question

I want to pull data from a PostgreSQL database on my local network with Power Query. By default my version of Excel lists three options under the "Get External Data/From Database" menu on the Power Query ribbon - "From SQL Server Database," "From Access Database," and "From SQL Server Analysis Services Database". PostgreSQL is not a default option.

Luckily (or so I thought) there is this article on the official Microsoft Office support page on how to install/enable the Ngpsql data provider for PostgreSQL for this purpose. I followed these instructions, but I still only see those three options showing up. I haven't been able to find any other instructions on how to do this through Google. Here's what I did exactly:

1. Download Npgsql-2.2.5-net35.zip from here, this seems to be the newest release of Npgsql for .Net 3.5. Unzipped to a temporary folder.
2. Copy gacutil.exe and gacutil.exe.config from another computer with Visual Studio to another temporary folder on this computer (found in c:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\x64\).
3. Run gacutil.exe /i "<tempfolder>\Npgsql.dll" and same with Mono.Security.dll. Got message Assembly successfully added to cache on both.
4. Edit C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG\machine.config XML file (v2.0.50727 is for .Net 3.5).
   • Add line <add description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.2.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" name="Npgsql Data Provider" invariant="Npgsql" support="FF"/> in node <DbProviderFactories>. This is verbatim from the help page except for version number
   • Verified by running gacutil.exe -l | findstr Npgsql that the version number and public key match what I installed.
5. This is the end of the instructions on the help page. Restart Excel, no change in database options in menu. Restart computer, same.
6. Decide to try again with the .Net 4.0 version. Download new version of Npgsql from releases page, unzip to temp folder.
7. The version of gacutil.exe I copied over was for .Net 3.5. Found the alternate one in a different directory (c:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\x64\), verified it is for 4.0 by running with the -h switch and got Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.18020. Copied over to this machine (had to include the 1033 subdirectory with some other dll's in it for it to work).
8. Repeat #3 with new dll's and version of gacutil. Same success message.
9. Repeat #4 with machine.config file, this time in v4.0.30319 directory.
   • Also commented out the node I had added in the other file.
10. Restart, still no option in menu.

At this point I'm out of ideas. I have this working on a couple of other computers because I had installed Labkey Server, which uses a PostgreSQL database and apparently installs the right DLL's alongside it. It shows up in the menu as simply "From PostgreSQL database". Of course I could just install Labkey Server on this computer but that seems like an ugly solution I'd like to avoid.

Answer 1

Finally got this working. It looks like for some reason you need the Professional Plus edition of Microsoft Office to enable PostgreSQL connections. With this version freshly installed on a new computer I had the PostgreSQL option under Power Query -> From Database available by default. Once I selected this option, gave the host and database name, then username and password, I got an error telling me I the Ngpsql data provider could not be found. So, the option appearing in the menu is simply due to MS Office version and not whether the Ngpsql provider is installed. I went to this page mentioned above, downloaded the Setup_Npgsql-2.2.5.0-r3-net40.exe installer, ran, restarted Excel and everything worked.

Answer 2

I've no specific experience with PostgreSQL, but in general with database drivers it's critical to start from an understanding of whether the program you are starting from has a 32-bit (aka x86) or 64-bit (aka x64) architecture. As you can start to see from the muddled names for these, Microsoft have made a total hash of this IMO.

For Excel 2013, go to File / Account / About Excel, then look at the end of the first line of text. Excel (like all other programs) will only work with drivers of the same architecture.

The Power Query documentation page you linked to hints at this, but it is a bit misleading when they say "Select the driver that matches your Power Query installation" - really it's your Excel installation that sets the architecture. The architecture for Power Query is also totally dependent on the architecture for Excel.

I downloaded the Ngpsql 2.2.5 R3 file from the link on the Power Query page - it's not really clear but judging from the install wizard this is 32-bit - it suggests installing into folder C:\Program Files (x86)\The Npgsql Development Team\Npgsql

If that is the only source, then I suggest you need a 32-bit Excel install to get this working.