1

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

Improve this question

1 Answer 1

Reset to default
1

[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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.