Renaming file using a table in Excel

[audra.swope]

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.

[ashokkarale]

@audra.swope,

You can follow these steps:

Step 1: Read the Excel Sheet

1. Use Excel Application Scope to open the Excel file.
2. Use Read Range activity to read the data from the Excel sheet into a DataTable. Store the result in a variable, say dtFileNames.

Step 2: Get the List of Files in the Folder

1. Use 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.

Step 3: Loop Through the Files

1. Use For Each activity to loop through each file in the files array.

• Set the type argument to String.

Step 4: Loop Through the Excel Data

1. Inside the For Each loop, use another For Each Row activity to loop through each row in the dtFileNames DataTable.

Step 5: Check if the File Name Contains the Word in Column A

1. Inside the 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)

• If true, proceed to rename the file.

Step 6: Rename the File

1. Use 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

[LimaCodes (Lima)]

Hey I would give it a try on this:

1. Read the Excel File

• Activity: Use Excel File
  • Select the Excel file you want to work with.
• Activity: For Each Excel Row
  • This will loop through each row in your Excel file.
  • In the Range property, select the relevant worksheet and define the range (e.g., Sheet1).

2. Check If the File Name Contains the Word in Column A

• Activity: If
  • Place this inside the For Each Excel Row loop.
  • In the Condition field, use the expression:

    CurrentFile.Name.Contains(CurrentRow.ByField("Column A").ToString)
    

  • This checks if the current file name contains the word in Column A.

3. Rename the File

• Activity: Move File
  • If the condition is true, this activity will rename the file.
  • In the Path field, specify the full path of the file you want to rename.
  • In the Destination field, specify the new path including the new file name:

    CurrentDirectory + "\" + CurrentRow.ByField("Column B").ToString
    

4. Set Up the Loop and File Path

• Activity: For Each File in Folder
  • Place this before the For Each Excel Row loop to iterate over all files in a specified folder.
  • In the Folder field, specify the folder path containing the files.
• Activity: Assign
  • Set a variable to hold the current directory path:

    CurrentDirectory = System.IO.Path.GetDirectoryName(CurrentFile)
    

[postwick (Paul)]

This is out of order.

Anyway, there’s a more efficient way to do it.

• For Each Row in Excel
• • For Each in New System.IO.DirectoryInfo(pathVar).GetFiles("*" + CurrentRow("Property").ToString + "*")
• • • Rename file CurrentItem.FullName

[audra.swope]

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.

[postwick (Paul)]

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…

Renaming file using a table in Excel

This is out of order. Anyway, there’s a more efficient way to do it. For Each Row in Excel For Each in New System.IO.DirectoryInfo(pathVar).GetFiles("*" + CurrentRow("Property").ToString + "*") Rename file CurrentItem.FullName

[postwick (Paul)]

[audra.swope]

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?

[postwick (Paul)]

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

[audra.swope]

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

[postwick (Paul)]

@audra.swope

Doesn’t the * indicate that there are more words after the specified text?

* 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?

[audra.swope]

I have 115 files - 89 go through the process and the remaining gets an error - see the bottom of screenshot for error output

[postwick (Paul)]

The error is pretty clear, you’re trying to rename a file to a filename that already exists.

[audra.swope]

Do you happen to know if there is a way to bypass that one file if it already exists?

[postwick (Paul)]

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”.

[audra.swope]

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?

[postwick (Paul)]

Are all the files in the same folder?

[audra.swope]

Yes they are. About 3/4 of the files renamed properly and 1/4 is still in the original filename.

[postwick (Paul)]

You need to run in Debug mode so you know which activity resulted in the error.