使用Excel中的power查询连接到Postgresql数据库

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:

我正在努力克服上面的问题——我已经安装了PowerQuery(64位Excel 2013设置)，并且在数据库连接选项下，尽管按照这里的指示下载了PostgreSQL的Ngpsql数据提供程序:

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

https://support.office.com/en ie/article/connect - - postgresql数据库-功率-查询- bf941e52 - 066 f - 4911 a41f - 2493 - c39e69e4?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:

我仍然只能在数据库列表下看到一组有限的选项，其中不包括PostgreSQL数据库。在网上搜索之后，我发现了这条线:

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

https://superuser.com/questions/950100/connect - - postgresql数据库——从excel - 2013——查询————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).

这似乎表明，我看不到Postgresql选项的原因是我没有使用OfficePro安装(认为它是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

有人有什么好的建议吗?或者，我真的必须使用不同版本的office才能从Postgresql db获取数据吗?谢谢

1 个解决方案

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

我有2013年的办公室。除了安装“PowerQuery_2.44.4675.281(64位)[en-us]”之外，我还必须这么做。msi(在安装对话框中勾选GAC安装)和npgsql -3.2.3 msi。同时,重启机器。

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.

但是，当使用Powerquery将大量数据返回到Excel时，有时会出现“类型转换”错误——我无法将其映射为NULLs或任何容易确定的内容。Powerquery似乎是一种很好的工具，可以使用数据转换步骤修复这个错误。

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

如果您只是想获取postgres数据—您可以使用VBA + ADO。我刚把它安装好，它就能用了。

Install "psqlodbc_x64.msi".
安装“psqlodbc_x64.msi”。
Add references to your vba project.
添加对vba项目的引用。
Finally I created the connection with this connecton string (no windows DSN setup required - modify the string below as per your setup):

最后，我使用这个connecton字符串创建了连接(不需要windows DSN设置——根据您的设置修改下面的字符串):

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

cnn。开放”司机= { PostgreSQL Unicode(x64)};服务器= 127.0.0.1;港口= xxxx;UID = postgres;PWD = postgres;数据库= db_name;”

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

我还可以使用相同的连接字符串成功地将其添加为数据源。

#1