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:
Npgsql-2.2.5-net35.zipfrom here, this seems to be the newest release of Npgsql for .Net 3.5. Unzipped to a temporary folder.
gacutil.exe.configfrom another computer with Visual Studio to another temporary folder on this computer (found in
c:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\x64\).
gacutil.exe /i "<tempfolder>\Npgsql.dll"and same with
Mono.Security.dll. Got message
Assembly successfully added to cacheon both.
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG\machine.configXML 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=184.108.40.206, 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 Npgsqlthat the version number and public key match what I installed.
- Add line
- This is the end of the instructions on the help page. Restart Excel, no change in database options in menu. Restart computer, same.
- Decide to try again with the .Net 4.0 version. Download new version of Npgsql from releases page, unzip to temp folder.
- The version of
gacutil.exeI 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
-hswitch and got
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.18020. Copied over to this machine (had to include the
1033subdirectory with some other dll's in it for it to work).
- Repeat #3 with new dll's and version of
gacutil. Same success message.
- Repeat #4 with
machine.configfile, this time in
- Also commented out the node I had added in the other file.
- 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.