Renaming Files Based on Excel Cell Contents?

[KenRyan]

I'm stumped again on something that seems easy. I've tried to figure it out for the last 3 hours. I actually think this one is easier in DOS with REN

I need to rename a set of files in a folder to new names, contained in an Excel file.
For example, my folder has a file named “first.mp3” and it needs to be changed to “voice_099.mp3.” In my Excel sheet, I have a list of the original filenames (“fist.mp3,” etc.) in one column, and the list of the corresponding new filenames (“voice_099.mp3, etc.) in the column next to it. Each original name has the new name in the cell to its right.

I have:
-Open Folder “C\.......”
-Start Loop “Each File in C:\.....

and have experimented with opening the Excel file, getting the contents of the cells, assigning variables to them, comparing them with "Filename.Extension" of each loop, but I keep getting wrapped around the axel.

I’m just having trouble figuring out how to integrate the Excel part.

[forumsupport]

Hello Ken,

Yes, using 'Loop for each file in a folder' command you can perform an action for all files in a folder. You can specify a folder, and the loop will run as many times as there are files in the folder. You can get the name of a file by using the system variable $FileName$. Similarly, to get the extension of the file, you can use the system variable $Extension$; finally, to get the path of the folder where the file is stored use the system variable $CurrentDirectory$.

You can use Excel:Get Multiple cells command to get old and new file names into excel dataset and use 'Loop for each row in excel dataset' command to compare the $Filename$ with old file name i.e. Excel Column(1). If result of comparision is true then inside If-Endif block rename $Filename$ with new file name i.e. Excel Column(2).

Text format of commands would appear something like this,

1 Excel : Open Spreadsheet "C:\Book1.xls". Active Sheet: Default. Session: Default
2 Excel : Get multiple cells from cell "A2" to cell "B4". Session: Default
3 Start Loop " Each File in C:\Temp"
4 Variable Operation : $FileName$.$Extension$ To $Prompt-Assignment$
5 Start Loop " Each row in an Excel dataset of Session = Default"
6 If $Excel Column(1)$ Equal To $Prompt-Assignment$ Then
7 Rename Files "$CurrentDirectory$\$FileName$.$Extension$" to "$Excel Column(2)$"
8 Exit Loop
9 End If
10 End Loop
11 End Loop
12 Open "c:\temp "

For your convenience, we have also attached a sample task. To run this task,

. Please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.
. Copy .xls under c:\

Before running this task, you may need to change the file paths at line #1 and #3. Save the task and run it using RUN button.

Please let us know how it goes.

Hope that helps.

[KenRyan]

Thanks for that! I'm going to try it now. There was a problem with the attachment though. I extracted the task file to "My Tasks" and the .xls file to c:\, but when I try to edit it in AA, it crashes the program (?).

That's OK. I think I can use the text you provided.

Thanks again!

Ken

[KenRyan]

OK, I am getting 2 errors at line 4 that say "Cannot use System Variable 'FileName' in this loop," and "Cannot use System Variable 'Extension' in this loop." I attached my version of the task based on your text example. I used the same xls file you sent, and C:\Temp has 3 files in it (first.mp3, second.mp3, third.mp3).

[forumsupport]

Hello,

Regarding task crashing, the task is build in latest version i.e. version 5.5 that includes several new enhancements and bug fixes, which may not be compatible with older version.

You can download free version of Automation Anywhere 5.5 from our website:
Automation Anywhere - The Leading Automation Software

You can view list of new enhancements and bug fixes in version 5.5 at, Automation Anywhere Enterprise Version History - The Automation Software. Automate & Schedule Business & IT processes easily.

Note: Version 5.5 is a free upgrade for version 5.0 customers.

Regarding the error, can you please try moving line #5 to line #3 and run the task again? Text format of task would appear as follows,

1 Excel : Open Spreadsheet "C:\Book1.xls". Active Sheet: Default. Session: Default
2 Excel : Get multiple cells from cell "A2" to cell "B4". Session: Default
3 Start Loop " Each row in an Excel dataset of Session = Default"
4 Start Loop " Each File in C:\Temp"
5 Variable Operation : $FileName$.$Extension$ To $Prompt-Assignment$
6 If $Excel Column(1)$ Equal To $Prompt-Assignment$ Then
7 Rename Files "$CurrentDirectory$\$FileName$.$Extension$" to "$Excel Column(2)$"
8 Exit Loop
9 End If
10 End Loop
11 End Loop
12 Open "c:\temp "

We have also amended the task you sent for your convenience. Please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.

Please let us know how it goes.

Hope that helps.

[KenRyan]

Yes, that worked! Thanks VERY much.

Ken