英文:
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).
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.
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论