power-automate-desktop

Power Automate - Copy files from a folder if they appear within an Excel spreadsheet list

I am trying to use Power Automate to retrieve a large list of files (200+) from a data archive folder (71K files). I have a list of the file names I need to retrieve and am attempting to match and copy all files in the spreadsheet. Flow is as follows... enter image description here

However, the flow fails at the point of trying to match filenames in the list with those in the target folder - "Can't retrieve the list of files. Illegal characters in path". Full error below...

"Correlation Id: 390dc828-eb79-4107-9625-4903c8f7b37f Illegal characters in path.: Microsoft.PowerPlatform.PowerAutomate.Desktop.Actions.SDK.ActionException: Can't retrieve list of files in folder E:\2021\POD. ---> System.ArgumentException: Illegal characters in path. at System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional) at System.IO.Path.InternalCombine(String path1, String path2) at System.IO.FileSystemEnumerableIterator1.GetFullSearchString(String fullPath, String searchPattern) at System.IO.FileSystemEnumerableIterator1..ctor(String path, String originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler1 resultHandler, Boolean checkHost) at System.IO.DirectoryInfo.InternalGetFiles(String searchPattern, SearchOption searchOption) at Microsoft.Flow.RPA.Desktop.Modules.Folder.Actions.FolderActions.GetFilesInFolder(Variant folder, Variant fileMask, Variant& listOfFiles, Boolean includeSubfolders, Boolean getResultInDataTable, String resultDataTableColumns, Boolean failOnAccessDenied) --- End of inner exception stack trace --- at Microsoft.Flow.RPA.Desktop.Modules.Folder.Actions.GetFilesInFolder.Execute(ActionContext context) at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary2 inputArguments, Dictionary`2 outputArguments)"

Any ideas where I am going wrong here? Thanks.

Solution

  • The way you are using the ExcelData, List and Get files in folder actions are not quite correct.

    What you are doing is adding the whole of the ExcelData into the first item of the List

    What the Get files in folder then tries to match with is text to the effect of '[200 Rows, 1 column]' and not each file individually.

    You can get rid of the Create List and Add item to list altogether. The ExcelData is already a List object so in your For Each loop you can replace List with ExcelData

    flow

    This assumes the excel data looks like this:

    exceldata