How to import a folder with multiple ".csv" files using Get Data (Power Query) on Microsoft Excel 365 in a Mac?

huangapple go评论96阅读模式
英文:

How to import a folder with multiple ".csv" files using Get Data (Power Query) on Microsoft Excel 365 in a Mac?

问题

我是新手使用Excel。我目前正在处理一个案例研究(Cyclistic),其中我有多个“.csv”文件位于桌面文件夹中,我想将它们导入到Microsoft Excel 365的一个工作簿中。

在网上搜索时,似乎很容易做到(图像1)。然而,在我使用的Macbook上的Microsoft Excel 365中,没有“获取数据”>“从文件”>“从文件夹”的选项,这个选项不显示在我的Excel中(图像2)。

我尝试了选项:Power Query Editor > 获取数据 > 文本/CSV(图像3),但它不允许我导入文件夹,我只能一次导入一个.csv文件,而我想要导入所有文件,以便将它们合并到一个工作表中并清理数据。

谢谢

英文:

I am new to using Excel. I am currently working on a Case Study (Cyclistic) where I have multiple ".csv" files located in a desktop folder, that I want to import to one workbook in Microsoft Excel 365.

While searching online, it seems that it's very easy to do (Image 1). However, in the Microsoft Excel 365 that I am using on my Macbook, there is no Get Data > From File > From Folder. This option doesn't show in my Excel (Image 2).

Image 1
Image 2

I tried the option: Power Query Editor > Get Data > Text/ CSV (Image 3) but it doesn't allow me to import the folder, I can just import one .csv file at a time, while I want to import all files in order to merge them in one worksheet and clean the data.

Image 3

Image 4

Thanks

答案1

得分: 1

在Windows上,代码如下,你可以将它粘贴到PowerQuery环境中的高级编辑器内。在Mac上尝试:

let
    Source = Folder.Files("C:\directory\subdirectory"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
    #"Added CSVdata" = Table.AddColumn(#"Filtered Rows", "CSVdata", each Table.PromoteHeaders(Csv.Document([Content]), type table)),
    #"Added Custom" = Table.AddColumn(#"Added CSVdata", "Custom", each let name = [Name] in Table.AddColumn([CSVdata], "SourceFile", each name)),
    #"Combined CSVdata" = Table.Combine(#"Added Custom"[Custom])
in
    #"Combined CSVdata"

我建议你自己读取一个单个文件,以查看Mac上如何设置Source语法。

英文:

In windows, the code would be as below, and you'd paste it in home ... advanced editor ... within the powerquery environment. You can try on mac

let Source = Folder.Files("C:\directory\subdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
#"Added CSVdata" = Table.AddColumn(#"Filtered Rows", "CSVdata", each Table.PromoteHeaders(Csv.Document([Content])), type table),
#"Added Custom" =  Table.AddColumn(#"Added CSVdata", "Custom", each let name = [Name] in Table.AddColumn([CSVdata],"SourceFile", each name)),
#"Combined CSVdata" = Table.Combine(#"Added Custom"[Custom])
in #"Combined CSVdata"

I'd suggest reading in a single file yourself to see how a mac sets up the Source syntax

huangapple
  • 本文由 发表于 2023年4月11日 01:28:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75979267.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定