Connect to PostgreSQL database from Excel 2013 Power Query with Npgsql

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 "\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 in node . 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

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.