Hello. I have a list of files in a folder - I am hoping to: ‘if’ the existing file name contains the word in column A (of an Excel sheet) then rename the file to the text in column B (of the same Excel sheet). Any tips on what activities and input to create this workflow? I have added a snip, below, of the table from which I am looking up. Note, I am a beginner user of StudioX so detailed suggestions are very much appreciated.
You can follow these steps:
Excel Application Scope to open the Excel file.Read Range activity to read the data from the Excel sheet into a DataTable. Store the result in a variable, say dtFileNames.Assign activity to get the list of files in the folder:files = Directory.GetFiles("your_folder_path")
files is an array of strings containing the full paths of the files.
For Each activity to loop through each file in the files array.String.For Each loop, use another For Each Row activity to loop through each row in the dtFileNames DataTable.For Each Row loop, use If activity to check if the file name contains the word in Column A:Path.GetFileName(file).Contains(row("ColumnA").ToString)
Move File activity to rename the file.Path should be set to the current file path (file).Destination should be set to the new file name:Path.Combine(Path.GetDirectoryName(file), row("ColumnB").ToString)
LLM Helped me to write this but it’s verified by me.
Thanks,
Ashok 
Hey I would give it a try on this:
Use Excel File
For Each Excel Row
Sheet1).If
For Each Excel Row loop.CurrentFile.Name.Contains(CurrentRow.ByField("Column A").ToString)
Move File
CurrentDirectory + "\" + CurrentRow.ByField("Column B").ToString
For Each File in Folder
For Each Excel Row loop to iterate over all files in a specified folder.Assign
CurrentDirectory = System.IO.Path.GetDirectoryName(CurrentFile)
This is out of order.
Anyway, there’s a more efficient way to do it.
New System.IO.DirectoryInfo(pathVar).GetFiles("*" + CurrentRow("Property").ToString + "*")Thank you for this. I’m working through your instructions. Can you please tell me what the “assign” activity is? I am not seeing that as an option.
You don’t have to do that. It’s an extra, unnecessary variable. Just put the Directory.GetFiles expression directly into the For Each.
But I gave you a much simpler solution…
Thank you, Paul - this worked!!! There is only one glitch, so far — if the property name is at the end of the existing file name, it will not rename - see below for example existing file names that will not complete. Any ideas on what to do to have these ones rename also?
Did you set the filter to "*" + CurrentRow("Property").ToString + "*"
* has to be at the beginning and end.
Are there any spaces after the Property values in the spreadsheet? Try CurrentRow("Property").ToString.Trim
There are not any spaces. When you put the * at the end, is it going to pick up the file names where the ‘property’ is the last word? Doesn’t the * indicate that there are more words after the specified text? I could be wrong
* can be anything including nothing. And there are characters after your value - the period and extension. It’s working fine for me. I filter with recording and it pulls the correct files, even when recording is the end of the filename before the extension:
Can you post a screenshot of what you have?
I have 115 files - 89 go through the process and the remaining gets an error - see the bottom of screenshot for error output
The error is pretty clear, you’re trying to rename a file to a filename that already exists.
Do you happen to know if there is a way to bypass that one file if it already exists?
Use an If with NOT File.Exists(filename)
Put the rename into the Then.
You can also do something in the Else like updating CurrentRow to show that file already exists or just a Log Message that says “file already exists”.
I did this but now I am getting a “access to the path is denied” error message. It is not indicating which file(s) is having the issue. I went into the files that did not complete the process and they are opening for me fine. Sorry to bother you again, but any ideas?
Are all the files in the same folder?
Yes they are. About 3/4 of the files renamed properly and 1/4 is still in the original filename.
You need to run in Debug mode so you know which activity resulted in the error.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
