I'm looking for a solution to batch rename files according to a spreasheet sorting.
For example the audio files are named:
Angel.wav
Child.wav
Forest.wav
Music.wav
Oil.wavs
The spreadsheet contains the filenames in a different order, e.g:
Music.wav
Child.wav
Forest.wav
Angel.wav
Oil.wavs
So I want Keyboard Maestro to add numbers at the beginning of the files, that the sorting order of the files are the same like in the spreadsheet. The result should be:
Keyboard Maestro should check the first row of the spreadsheet, then find the file in a folder and add 0001_ at the beginning. Then jump to the next row and add 0002_ and so on.
Maybe it's possible with a combination of Automator (what I'm not really familiar with) and Keyboard Maestro?
It's a Excel .xlsx spreadsheet. All filenames will be in one row.
I can also export the .xlsx to a .csv file, if this would help.
In the meanwhile I've figured out, that Applescript will be needed to select a file in an active finder window. Maybe I can just select and copy the first cell in Excel to clipboard and set this as a variable with that I can find, select and rename the file in finder.
Maybe this solutions will be quiet slow. We are talking about thousands of files.
That's a good start, but where are the file paths? In Excel?
I would be best if you can upload a real-world Excel example, and provide complete details for locating the file paths.
And yes. I just want to select the first row in the column (A1), then KM should find the file in any folder, rename it (add 0001_ at the start of the filename) and then jump to the next row (A2), and so on. A prompt at the beginning how often KM should repeat the script would be helpful (I did this sometime before).
The reason is that I have to sort the audio files in an audio program (Avid ProTools) in the same order like in the spreadsheet. And the only way to achive this is to set index numbers at front. Till now I did this with Quickkeys, but jumping through the windows with copy and paste actions is very slow.
It would generally be easier if you select the whole range (so A1…A5) and then you can just Copy, and then Keyboard Maestro can iterate through the lines in the clipboard and perform the replacement.
If the folder is open in the Finder, then you should be able to just use the FinderInsertionLocation token to get the path.
So, something like this (from the selection range in Excel)
Note that any time you are going to do something like this you really need to ensure you have good solid up to date backups. Personally I would also duplicate the target folder as well for extra safety. And I would double check the value of % FinderInsertionLocation% or just use a fixed path.
Generally it's better to get data directly from Excel with AppleScript than to depend upon the clipboard. Excel has a proprietary clipboard, and it can cause a variety of glitches when automating copy and paste operations.
This macro does what you want and produces an error-report if any files in the spreadsheet are not found in the Finder.
NOTE -- You MUSTselect the column of file names in Excel before running the macro.
NOTE -- The macro acts on the FRONTMOST window in the Finder.
The AppleScript can be easily adapted to produce your dummy .wav files for missing files, but you'll need to provide the dummy file.
I've run in some trouble when renaming 600+ files.
I get the error message (translated from german): "An object with the same name exists at the location" Num: -15267
Could it be that the script is renaming too fast for the finder? Or the copy process of the dummy.wav file takes too long?
Maybe it would help to slow down the script a little bit.
Sometimes the script also renames the dummy.wav file on the desktop by a filename of the spreadsheet.
Edit: I got another problem. The macro generates only new files instead of renaming them. I updated from Mavericks to Sierra. Could that be a problem?
