时间戳

首页 > 解决方案 > 从 ODBC 访问 Excel 数据模型 (Power Query) 表

excel - 从 ODBC 访问 Excel 数据模型 (Power Query) 表

问题描述

我们可以使用 ODBC（Excel ODBC 驱动程序）访问 Excel 数据。我们还可以访问数据模型（即 Power Query 表）中的数据吗？基本上我正在考虑（错误）使用 Excel/Power Query 作为数据库，并让外部应用程序从中检索数据（使用 SQL）。

要从 Sheet1 中读取，我可以这样做：

SELECT ... FROM [Sheet1$]

但

SELECT ... FROM [table in data model]

似乎对我不起作用。这是应该工作还是根本不支持？

有大量关于 Power Query 使用 ODBC 导入数据的信息。在这里，我正在反观。

标签： excelpowerquery

解决方案

You should distinguish for yourself Power Query tables and Data Model (Power Pivot) tables. You can set up some PQ tables as tables, loadable to DM, so data will be "transferred" from PQ to DM only for that particular tables.

I'm pretty sure that it is impossible to get data from "PQ only" tables. You can just get m queries (not their results) via VBA or unpacking Excel.

Regarding PP (DM) tables. Actually, there is Analytical Services (VertiPac) engine inside Excel (just in case - as well inside PowerBI Desktop). So as soon as you start Excel or PBI, you actually start AS engine instance as well. The data in it are reachable via:

  1. Excel VBA (Visual Basic for Applications). You have Thisworkbook.Model.DataModelConnection.* API, and can get to data itself and to model as well. This is the only "official" way to get the data programmatically.

  2. Power Query - as Analytical Services data source. This is unofficial way, but I read, that Microsoft told that they are not going to close it in the future (but you never know :-)). E.g. Dax Studio can do that - https://www.sqlbi.com/tools/dax-studio/. Unfortunatelly, while getting to PBI AS service is quite easy, I don't know how to get to Excel AS service without Dax Studio. As far as I understand, the main problem here is how to get an AS port number, launched by Excel. But I hope that this info will at least help you understand the way for further searching, if you want to go Power Query way. Or may be it is reasonable to use Power BI Desktop for the task.

  3. Excel is just a zip file, so definitely AS files are inside of it. I never went this way, but you can observe what is inside exel zip - possibly the AS files may be in some useful form there.

推荐阅读