As a School Accounts Manager, I often have to automate tasks. One task that sometimes occurs when files are not named properly is that I have to rename hundreds of files based on a spreadsheet.
Our school has multiple programs to manage different functions at the school. We have a program that creates school ID’s. We have another program that tracks behavior to give tardies, absences, and points for good behavior. But these two systems don’t talk to each other and when they were set up, the data on these systems were not standardized.
As a result, our ID program named each student photo with a random string on characters (wegbjksiebj.jpg) our behavior tracking program can integrate photos with each student ID number. You can see the problem here. I have to rename almost a thousand photos with the corresponding student ID number.
Enter an amazing tool for the Mac: A Better Finder Rename
This is such a great name for a Mac Application because it tells you exactly what it does. It’s a powerful and much better renaming application for the Mac.
I won’t go into all the features of this amazing application, but it does one power feature exceptionally well. It can bulk rename multiple files from a spreadsheet.
How to Bulk Rename Multiple Files from a Spreadsheet
It takes a little bit of effort to automate this process with A Better Finder Rename but here are the steps:
First you have to prepare the spreadsheet.
In my example I have a Spreadsheet with a Column of JPG files and a Column of ID Numbers. I want the JPG files to be renamed with the corresponding ID Number. The end result should look like 68937.jpg
Before A Better Finder Rename can find these files, it needs to get the direct path to the files.
These photos live on my Lexar Flash Drive so the path to these files is:
/Volumes/Lexar/Photos/
I have to add that to the spreadsheet so I’ll use the CONCATENATE Function from my previous post of Common Excel Functions.
Once I have that, I’ll save it as a Tab Delimited Text file. In Excel, choose File->Save As…
Now we’ll clean up the file by deleting the first row of File Names and the Header Row which is not necessary. This is what we’re left with as our photoRename.txt file.
Run the Bulk Rename with the Prepared Spreadsheet
We’ll open A Better Finder Rename.
For the Category, choose “Advanced & Special
For Action, choose “Rename from file list”
Choose the file, in our example we named the Tab Delimited Text file photoRename.txt
The default options of Sort (by name)and Change only the file name are what we want.
Finally we just drag and drop all the photos onto the drop area on the right of A Better Finder Rename.
You’ll get a Preview of how the filenames will change and you just have to click “Perform Renames”
You’ll get one last chance to preview the results before you make the changes, but if it looks good, you can “Rename All”
That’s it.