Move named files to subfolders that contain a portion of file name

[jotty]

Rookie user and I searched the forum and came close to a solution but not hitting the target.

I have a folder with old scanned patient charts. It has 75k files in it all named based off the patient ID, for example "12345".

I need to move these into the new systems patient folders, but they are all named "LastName-FirstName-12345".

I'd like to find a way for Power Automate to look at the folder with the files and the folder with all the new sub-folders and find that "12345" match and move the file to the matched subfolder.

[mmonline]

I need a bit more information. The 75k files.. you say the name is based on the Patient ID.

However, I suspect the patient ID is part of the file name, not the entire file name. However, if the files are, in fact, based solely on a numeric patient ID, the flow below will work.

Flow overview (or is that, Floverview*:

¯\_(ツ)_/¯

1. Create a table with patient IDs folder paths
   1. Get subfolders in folder (where your new patient folders are)
   2. Create a data table named, PatientFolderTable
   3. For each folder
      1. Get the folder name
      2. Split using the "-" (based on your above naming convention)
         This will create a list with 3 values
         Value [0] - last name
         Value [1] - first name
         Value [2] - patient ID
      3. Create variables for those values (patient id is the important one)
      4. Insert these values into the above created data table
   4. Get all the files to copy
   5. For Each file
      1. Set Filename to %CurrentFile.NameWithoutExtension%
         Note: that would be the patient ID if the files are only named patient ID.
         See my caveats below the flow image 
      2. Search for the file name (patient ID) in the PatientFolderTable
      3. Using the row for any found values, get the FolderPath as CopyToPath variable
      4. Move the file

FLOW IMAGE

CAVEATS

I assume the 75k files are some variation of file names that contain the patient ID. This will require that you do a bit more parsing to arrive at the proper patient ID. If you give me more information (sample file names - as long as no HIPAA issues exist. You can dummy names but a sampling of what the files name contain.

From this we can modify the flow accordingly. There are times that such operations are more easily done via code - Run Javascript, Python, or VBScript, with a file name, in order to deal with all the file name variations.

In any case, I hope this is helpful. If you can provide more filename information, I'll try to provide a more complete answer.

[Agnius]

The above solution by @mmonline seems like it would do the trick, but it is overly complicated in my opinion.

All you really need to do is get all files, build a loop that iterates through those files, find the target folder for each specific file and move the file.

Here's a sample of such flow, which is much more simple:

Here's a snippet you can copy and paste directly into your PAD designer to have the actions automatically created for you:

Folder.GetFiles Folder: $'''C:\\RPA\\Input''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH File IN Files
    Folder.GetSubfolders Folder: $'''C:\\RPA\\Output''' FolderFilter: $'''*-%File.NameWithoutExtension%''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Subfolders=> Folders
    File.Move Files: File Destination: Folders[0] IfFileExists: File.IfExists.DoNothing MovedFiles=> MovedFiles
END

Note you will need to change the directories from "C:\RPA\Input" to your actual directory where the files are stored and from "C:\RPA\Output" to the directory with the sub-folders. 

Please note the caveats regarding file naming that were mentioned by @mmonline do apply here, too.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[jotty]

Yes, the files are in fact just the patient id. I've attached a screenshot. That is just how they were exported out of the old system. I won't provide a screenshot of the new system folder for those HIPAA reasons, but the subfolder naming structure is exactly what I originally put, "LastName-FirstName-12345".

[Agnius]

Well then, both solutions above will work for your case. Mine is a bit shorter and easier to implement, but they both do the trick.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[jotty]

Using your method in a test environment. Worked perfectly. However implementing in live I get the screenshotted error on the move files step.

[Agnius]

An index out of range error indicates that you are trying to access an item in an empty list. I don't use any indexes in my proposed solution above. Can you share a screenshot of the first 4 actions of your flow?

[jotty]

Only thing I've done is change the folder directories. Is it possible the Y: directory is too generic and needs a subdirectory?

FUNCTION Main_copy GLOBAL
Folder.GetFiles Folder: $'''C:\\Users\\jott\\Documents\\Fortis''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH File IN Files
Folder.GetSubfolders Folder: $'''Y:\\''' FolderFilter: $'''*-%File.NameWithoutExtension%''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Subfolders=> Folders
File.Move Files: File Destination: Folders[0] IfFileExists: File.IfExists.DoNothing MovedFiles=> MovedFiles
END
END FUNCTION

[Agnius]

It's failing because of the %Folders[0]% part, indicating the folder does not exist (nothing returned by the Get subfolders in folder action). Try checking if %Folders% is not empty before doing the move.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[jotty]

That is it. No folders are pulling in. There are just over 200k subfolders in that directory. Wondering if there are just too many folders to load and go through before it errors out.

In my test I just took 20 files and made 20 subfolders to imitate the scenario and it worked great. So with only changing the directories to the real directories is it failing.

[Agnius]

I don't think that it errors out. Is the directory a local one, or is it a network directory? Perhaps there's some sort of a sync issue?

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[jotty]

I initially thought that as it was a network directory when I started. Then I installed PA on the server itself so it would be local. Still run into the issue. I know when I open the directory specifically it does take a minute or two to load everything that is inside it, so a sync issue still could be the cause.

[Agnius]

I guess that could be the issue, yes. I would suggest trying to force the machine to load the directory before trying to get the sub-folders. You can try to use the Run application action to launch explorer.exe with the directory as the argument like this:

System.RunApplication.RunApplicationAndWaitToLoad ApplicationPath: $'''explorer.exe''' CommandLineArguments: $'''C:\\RPA''' WindowStyle: System.ProcessWindowStyle.Normal Timeout: 600 ProcessId=> AppProcessId WindowHandle=> WindowHandle

This will open the folder in Windows Explorer, which should force Windows to load all those sub-folders. You can then use Close window to close it after you're done.

I don't have a folder at hand with so many sub-folders that my machine would take a while to load it all, so I cannot reproduce the exact situation, but you might need to also use Wait for window content and wait for some kind of an element appearing or disappearing to make sure the sub-folders have in fact loaded. Or, at the very least, add some fixed waiting time with a Wait action. And then try to use the Get sub-folders in folder action.

I think you only need to do this once and not for each item in your loop. Open the explorer and then run the entire loop. This should do the trick.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[mmonline]

I wonder, given that it is a one-time file move, whether using something like PowerShell might be more efficient. I haven't had to work with that many folders but PowerShell may provide a more robust tool in this case.

[jotty]

I messed with PowerShell but the logic was having issues differentiating between like numerical numbers for example: 51455, 151455, 251455. It would never find the 5 digit numbered folder, but always would find the 6 digit match and throw the file in. That days of frustration led me here. Still unsuccessful though.

[mmonline]

Another idea. I just tested this for retrieving the path to copy to.

There are limits on reading Excel data and/or creating data tables. In effect, reading a large folder listing (or file listing) is creating an array or data table. This may be the source of the challenge.

What I just tested was creating an Excel file with the folder listing in question:

• From a command prompt, in the network folder, run:
  "Dir *. /b >allFolders.txt"
  
  This will get you a directory listing with just folder names (none of the meta data)
  
  
• Copy this into an Excel file, starting at row two.
• Create headings on the first row (freeze this row)
  • Col A: Foldername
  • Col B: PatientID
  • Col C: FullPath
• In Column B put the following formula:
  =RIGHT(A2,LEN(A2)-FIND("_",A2,FIND("_",A2)+1))
  
  This will extract the patientID from the folder name.
  
  
• In Column C put the following formula:
  ="P:\DataFolder\" & B2
  Where "P:\DataFolder\" is the root folder of the destination folders.
  
  You could create this folder at runtime from column A but I just included it for simplicity.
• Copy the formulas to all the subsequent rows of the Excel file.
  This gives you a "database" of the patient IDs and paths.

You can now:

• Create a SQL connection to the Excel file
• Get your files and get the file name (without extension)
• Use that name in a SQL query, searching on Patient ID.
• The resulting row, if found, will have your path. 
• Use the file and the returned path to move the file.

The Excel connection string s/b:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Dev\PowerAutomate\PatientidsToFolder.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';

Where: C:\Data\Dev\PowerAutomate\PatientidsToFolder.xlsx is the path to your Excel file/database.

Here is a flow reading my Excel sheet with 200,000 rows.

Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Data\\Dev\\PowerAutomate\\PatientidsToFolder.xlsx;Extended Properties=\'Excel 12.0 Xml;HDR=YES\'''' Connection=> SQLConnection
SET PatientID TO 54343114808
Database.ExecuteSqlStatement.Execute Connection: SQLConnection Statement: $'''Select Foldername, PatientID, FullPath FROM [PatientFolders$]
WHERE PatientID = \'%PatientID%\'''' Timeout: 30 Result=> FolderData
LOOP FOREACH CurrentItem IN FolderData
SET FullPath TO CurrentItem[2]
END
Database.Close Connection: SQLConnection

NOTES

• It takes about 1 second to return the SQL query. Moving the data to Access to SQL would result in a faster query... but more work and the real performance improvement is probably nominal. Moving files will take that much time anyway.
  
  
• Yes.. it takes a little pre-work. But really, it only took about 10 minutes to set this sheet up.
   
• @Agnius is a pretty smart guy. He may have an improvement/optimization on this. But, it should work.

The result of a query - looking at the variable FolderData in my flow.

The Excel File: I didn't have 200,000 names handy so I simply added the row number to the end of an earlier row and copied it across 200,000 rows.

Let us know if you are able to craft a solution.

[Agnius]

This is a pretty good idea. I would just add that running SQL queries to Excel requires the following driver to be installed on the machine: https://www.microsoft.com/en-us/download/details.aspx?id=54920