我正在使用Office 2010。我有一个查询,它从几个Excel文件中组合数据。".xlsx"文件可以正常加载,但是当文件夹中存在".xls"文件时,查询将无法运行(会出现错误消息:"无法从数据库检索数据"。在查询编辑器中,当我点击具有错误的文件行时,我看到此处的消息:错误消息)。将文件重新保存为".xlsx"可行,但我更愿意使用它们原样。
我已在此处安装了MS Access数据库引擎:http://www.microsoft.com/en-us/download/details.aspx?id=13255,但它似乎没有帮助。
还有其他想法吗?谢谢!编辑:添加了两个查询。第一个是应用于每个文件的查询,第二个是组合它们的查询。
查询“Transform Sample File from Supplier CMRTs”:
let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),
#"Smelter List_Sheet" = Source{[Item="Smelter List",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"Smelter List_Sheet",3),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Smelter Identification Number Input Column", "Metal (*)", "Smelter Look-up (*)", "Comments"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [#"Metal (*)"] <> null and [#"Metal (*)"] <> "")
in
#"Filtered Rows"
查询"供应商CMRT":
let
Source = Folder.Files("O:\Supplier CMRTs"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Supplier CMRTs", each #"Transform File from Supplier CMRTs"([Content])),
#"Filtered Rows" = Table.SelectRows(#"Invoke Custom Function1", each [Extension] <> ".txt"),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Supplier CMRTs"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Supplier CMRTs", Table.ColumnNames(#"Transform File from Supplier CMRTs"(#"Sample File")))
in
#"Expanded Table Column1"