Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connecting Excel 2016 to Postgres

I am trying to have Postgres show as one of the data connection in Excel 2016. Following MS Office instructions I installed Ngpsql data provider. I used msi for installation so according to the instructions provided in the wizard the dlls were registered in GAC and the provider was registered in machine.config (I checked - the entry is there). Yet Postgres is still not showing as an option in Excel > Data > New Query > From Database list. Searching the web shows old out of date discussions. Developers of of Ngpsql direct questions to Stack Overflow. So please help.

like image 330
Andrzej R Avatar asked Nov 25 '16 06:11

Andrzej R


People also ask

How do I connect Excel to PostgreSQL?

Connecting Excel to PostgreSQL with Microsoft QueryStart Excel, click the Data tab. In the appeared ribbon, click From Other Sources, and then click From Microsoft Query. In the next dialog, choose the data source you want to connect to (e.g., using data source name - Devart ODBC PostgreSQL).


1 Answers

Postgres data can be used in Excel 2016 but it requires the ODBC driver and an external setup of the database server. As far as I can tell the Npgsql drivers do not work with my version of Excel which is 2016 MSO 32-bit a.k.a "Home Premium".

The Postgres ODBC driver is available here: https://www.postgresql.org/ftp/odbc/versions/msi/ Download and install the latest version. It should install without needing a system reboot.

Setup a DSN (I used machine) using the ODBC Data Sources (XX-bit) setup application. The driver version needs to match your Excel version.

After testing the connection in the setup screen, open Excel 2016 and use the Data -> New Query -> From Other Sources -> From ODBC menu to open the query screens. This seems to be the most modern interface available in Excel.

like image 92
Logan Greenlee Avatar answered Oct 23 '22 00:10

Logan Greenlee