如何使用 Power Query 将多个 CSV 文件作为工作表列导入单个 Excel 工作表？

Question

我有最新版本的 Excel（但对 Excel 的使用经验很少）。.csv我想从包含许多文件的文件夹中导入一些文件作为列到单身的Excel 工作表。我弄清楚了如何使用 Power Query 按文件名选择所需的文件，每个文件都有相同的行数。（它们每个也恰好只有一列，但我欢迎多列解决方案。）网络搜索显示，Power Query 可以附加每个文件中的行以在组合表中生成单列，但这不是我想要的。我也不是想要将列合并为一个摘要列。例如，如果我.csv在 Power Query 编辑器中筛选后有三个文件，我想将它们导入到包含三列（每个文件一列）的单个工作表中。理想情况下，结果将是单个表格，但这是次要的。

如何进行？

Answer 1

要在 Power Query 中执行此操作，您将需要一些自定义代码。

我假设您正在从对话框中选择文件Data => From Folders，

下面的代码从该点开始，然后添加在您过滤表格以选择所需文件后运行的代码。

• 将 csv 数据读入表格的一列
• 展开表列，将所有数据放入单个列中。
• 然后，魔术：使用自定义函数通过文件名来透视数据
• 请注意，我遗漏了该函数的各种参数Csv.Document，除了它是一个 csv 文档之外，我对您正在访问的数据一无所知。

阅读代码注释以及自定义函数中的参考以获取更多信息。

M 代码

let
    Source = Folder.Files("C:\Users\ron\Desktop\PQ Test"),

// Select (filter) the particular files you want, then

//Remove all except the "link" and file name columns
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),

//convert each csv to a Table
csvTables = Table.AddColumn(#"Removed Other Columns", "csv", each Csv.Document([Content])),

//remove the column of links
remLinks = Table.RemoveColumns(csvTables,{"Content"}),

//Expand the tables into a single column
#"Expanded csv" = Table.ExpandTableColumn(remLinks, "csv", {"Column1"}, {"Column1"}),

//Pivot on the file name
pivot = fnPivotAll(#"Expanded csv","Name", "Column1")
in
    pivot

自定义功能 M 代码 重命名此查询：fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

结果
请注意，列标题是文件名

Answer 2

对于只有少数文件的情况，最简单的解决方案是手动复制粘贴列。

对于其他情况，解决方案是使用 VBA 宏，使用旧的 Visual Basic 应用程序更多信息请参阅文章 创建或运行宏。

我发现了两个相当相似的用于水平连接 CSV 文件的 VBA 宏，它们都位于同一个文件夹中，并且为可变数量的列编写。

我在下面列出了宏，但我没有测试过其中任何一个。

宏 1

这来自帖子 在一个 Excel 电子表格中导入多个 CSV 文件 - 在第一行包含文件名。此处的宏还会重命名列。

Sub GetFromCSVs()
  Dim WB As Workbook
  Dim R As Range
  Dim bFirst As Boolean
  Dim stFile As String
  Dim stPath As String
  stPath = "C:\Temp\CSVs\" ' change the path to suit
  stPath = "C:\Users\gebruiker\Documents\Excel\"
  stFile = Dir(stPath & "*.csv")
  bFirst = True
  Set R = Workbooks.Add(xlWorksheet).Sheets(1).Range("A1")
  Do Until stFile = ""
    Set WB = Workbooks.Open(stPath & stFile, ReadOnly:=True)
    If bFirst Then
      WB.Sheets(1).Range("A1").CurrentRegion.Columns(1).Copy Destination:=R.Offset(1)
      Set R = R.Offset(, 1)
      bFirst = False
    End If
    R.Value = Left(stFile, Len(stFile) - 4)
    WB.Sheets(1).Range("A1").CurrentRegion.Columns(2).Copy Destination:=R.Offset(1)
    Set R = R.Offset(, 1)
    WB.Close SaveChanges:=False
    stFile = Dir  ' next file
  Loop
End Sub

宏 2

此宏来自文章 如何在一个 Excel 电子表格中导入多个 CSV 文件。

Sub GetFromCSVs()
  Dim WB As Workbook
  Dim R As Range
  Dim bFirst As Boolean
  Dim stFile As String
  Dim stPath As String
  stPath = "C:\Temp\CSVs\" ' change the path to suit
  stFile = Dir(stPath & "*.csv")
  bFirst = True
  Set R = Workbooks.Add(xlWorksheet).Sheets(1).Range("A1")
  Do Until stFile = ""
    Set WB = Workbooks.Open(stPath & stFile, ReadOnly:=True)
    If bFirst Then
      WB.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=R
      Set R = R.Offset(, 2)
      bFirst = False
    Else
      WB.Sheets(1).Range("A1").CurrentRegion.Columns(2).Copy Destination:=R
      Set R = R.Offset(, 1)
    End If
    WB.Close saveChanges:=False
    stFile = Dir()  ' next file
  Loop
End Sub