☰

Excel 中增量数据源获取的策略

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 database odbc
Excel 中增量数据源获取的策略

我的情况是，每周有第三个应用程序刷新一个表。我需要使用与数据库的 ODBC 连接，不断在 Excel 中积累所有数据。

我想知道

方法 1：有没有办法强制 Excel附加每次更新的结果（此更新将根据指示周的参数触发）？我尝试使用动态引用定义连接加载的表，但一旦第一次固定，表位置就永远不会重新定义

方法 2：使用 ETL 将所有每周结果累积到临时表中，然后实时将 Excel 连接到它。但是，我需要一种缓存旧数据的机制，因为我无法在 Excel 打开时呈指数增长。想象一下 10 年后，Excel 需要在打开 10 年的数据时更新它才能显示它。有没有办法存储已经获取的数据并通过选择新数据（使用某些查询/过滤器）实时增加它（当打开书时）

谢谢

编辑：也许这样问更好：对于一个不断增长且需要 Excel 实时读取的表，最佳策略是什么？我只是不想在几个月后获取所有数据……

答案1

我会使用 Excel 的 Power Query 插件来解决这个问题。它可以处理将 SQL 查询的结果附加到现有表。

http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872

如果您以 Excel 数据模型格式（而不是 Excel 表格）存储数据，则数据会被高度压缩 - 有报道称，有人以这种格式存储了 1.6 亿行数据（使用 64 位 Excel）。然后，您可以通过数据透视表和/或多维数据集公式将数据重新访问到 Excel 中。

答案2

我发现所选的答案有点欠缺（但是一个很好的开始:)

使用最新的PowerQuery（从2014年11月开始，除了加载到数据模型之外，还可以加载到工作簿）

在这里，来源数据只是 Excel 中的一个表格，但您可以从任何地方提取数据，包括 SQL DB。

第一步。创建初始加载。查询名为 FROM（名称继承自表名：FROM）

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow())
in
    AddCustom

加载到数据模型和工作表。工作表表格称为FROM_2默认情况下。

像这样更新 FROM 查询：

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow()),
    //Load from Table FROM_2  (this is just a copy of what's in the DataModel)
    Custom1 = Excel.CurrentWorkbook(){[Name="FROM_2"]}[Content],
    ChangedType = Table.TransformColumnTypes(Custom1 ,{{"Load Date", type datetimezone}}),
    //Append the two Loads.  The New data and the existing loads in the DataModel
    Append = Table.Combine({ChangedType,AddCustom})
in
    Append

因此，现在您可以添加指向 DataModel 或 FROM_2 表的数据透视表。如果您需要另一个包含 DataModel 中的数据的表，您可以在其中添加其他列，您只需创建另一个指向 FROM_2 表的查询即可。

由于您使用工作表中的表格作为中间步骤，我怀疑这是否可以扩展大量数据。

相关内容