CODEWITHSUNDEEP
databaseexcelpostgresql
Mehness
Mehness

Reputation: 135

Connect to a Postgresql database using power query in Excel

I am struggling with the above - I've installed PowerQuery (64 bit Excel 2013 setup) and under the database connection options, despite following the instructions here to download the Ngpsql data provider for PostgreSQL:

https://support.office.com/en-ie/article/Connect-to-a-PostgreSQL-database-Power-Query-bf941e52-066f-4911-a41f-2493c39e69e4?ui=en-US&rs=en-IE&ad=IE

I can still only see a limited set of options under under the database list, which do not include a PostgreSQL database. Now having hunted around on the web I found this thread:

https://superuser.com/questions/950100/connect-to-postgresql-database-from-excel-2013-power-query-with-npgsql

Which seems to suggest that the reason I cannot see the Postgresql option is that I am not using an OfficePro installation (think it was home edition).

Does anyone have any pointers - any workaround for this? Or do I really have to get a different version of office to get data from a Postgresql db, short of converting the database into Access or something? Thanks

Upvotes: 1

Views: 3788

Answers (1)

lucid_dreamer
lucid_dreamer

Reputation: 362

[I have Office 2013 Pro.]
I had to do this in addition to installing "PowerQuery_2.44.4675.281 (64-bit) [en-us].msi" (I ticked GAC installation on the installation dialog) and "Npgsql-3.2.3.msi". Also, rebooted the machine.

Everything then started to work connection-wise.

But when returning large amounts of data into Excel with Powerquery at times I would get "type cast" errors - I could not map it down to NULLs or anything easy to determine. Powerquery seems a fine tool for some usages and I am sure this error can be fixed with data transformation steps.

If you just want to get the postgres data - you can use VBA + ADO. I have just finished setting it up and it works.

  1. Install "psqlodbc_x64.msi".
  2. Add references to your vba project.

  3. Finally I created the connection with this connecton string (no windows DSN setup required - modify the string below as per your setup):

    cnn.Open "Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=xxxx;UID=postgres;PWD=postgres; Database=db_name;"

I could also successfully add it as a data source using the same connection string.

Upvotes: 1

Related Questions