Forum Discussion

KimS1231's avatar
KimS1231
Frequent Visitor
4 years ago
Solved

Need help with reading files in a folder

I'm trying to pull specific data from cells in excel workbooks. However, in the folder is a system file called thumbs.db. I cannot delete this as it says it is in use. No matter how I try to filter t...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You should replace this line 

    #"Filter for xlsx files only" = Table.SelectRows(#"Filter out .tmp & .db files", each [Extension] = ".xlsx"),

    with

    #"Filter for xlsx files only" = Table.SelectRows(Source, each [Extension] = ".xlsx"),

  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi KimS1231 ,

    thumbs.db are hidden files created when using thumbnail previews for folders in File Explorer.

     

    If you can confirm that the query has filtered the xlsx files in Power Query, you can try to delete the file by the following articles and check it again:

    How to delete network folder with thumbs.db file on Windows 10 

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

KimS1231's avatar
KimS1231
Frequent Visitor
4 years ago

Hello Vijay

I did the replacement and it is still erroring out. 

 

Here is the new code snip:

Source = Folder.Files("P:\XXXX\XXXXXX"),
#"Filter for xlsx files only" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
#"Added Custom Column" = Table.AddColumn(#"Filter for xlsx files only", "Sheets", each Excel.Workbook(File.Contents([Folder Path]&[Name]))),
#"Expanded Sheets" = Table.ExpandTableColumn(#"Added Custom Column", "Sheets", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Sheets.Name", "Sheets.Data", "Sheets.Item", "Sheets.Kind", "Sheets.Hidden"}),
#"Expanded Sheets.Data" = Table.ExpandTableColumn(#"Expanded Sheets", "Sheets.Data", {"Column2", "Column3"}, {"Sheets.Data.Column2", "Sheets.Data.Column3"}),
#"Filter for Budget Summary Sheet" = Table.SelectRows(#"Expanded Sheets.Data", each [Sheets.Item] = "Budget Summary"),
#"Filter for Outside Sales Comm" = Table.SelectRows(#"Filter for Budget Summary Sheet", each Text.StartsWith([Sheets.Data.Column2], "Outside"))

 

When I close and load, I get this message: [DataFormat.Error] File contains corrupted data

It's referring to thumbs.db as all other files in the folder are excel files of type .xlsx. As soon as it tries to expand the columns, it errors out.