Rename Files in the Finder With Sorting From a Spreadsheet

Hi,

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:

0001_Music.wav
0002_Child.wav
0003_Forest.wav
0004_Angel.wav
0005_Oil.wav

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?

Thank for any ideas!

Michael

Hey Michael,

If you haven't read this it's worth a couple of minutes of your time.

Tip: How Do I Get The Best Answer in the Shortest Time?

Rule-of-the-thumb – always tell us what software you're working with.

What spreadsheet?

-Chris

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.

Michael

Thanks for the help!

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.

The filepaths are not in Excel sheet. I can place the files in any location. It doesn't matter.
Or I can just use an active finder window.

Hey Michael,

One row? Or one column?

I ask because you're showing a column in your original post.

If you can manually select the column/row then the task will be relatively easy.

-Chris

The spreadsheet looks like this (I could also remove the .wav suffix, if this would be easier):

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.

Hope this helps!

Thanks

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.

Hi,

that sounds great. I will test it today.
And yes, I will only work on duplicates, because an undo won't be possible.

Ok... I've tested the macro.
I stuggled a little bit, because of a missing "%" after "%Index_".
But now it works like a charm.

Many thanks,

Michael

Please post each request as a separate topic.
This time, if you'd like, I can move this post to a new topic.

Ok, I post the question in a seperate topic!

Thanks,

Michael

Hey Michael,

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 MUST select 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.

-Chris


Rename Finder Files in Front Window from Excel Spreadsheet v1.00.kmmacros (9.7 KB)
Test_Files.zip (25.3 KB)

Wow great. Thanks Chris.
I will check it out on monday and try to adapt it to the dummy files.

I think I have to learn Applescript. It seems to be very powerful in combination with KM.

Michael

Hey Michael,

Unquestionably.

Here's a new macro that will create your dummy wav files.

The orange colored action is where you need to copy the POSIX Path (or Tilde-POSIX Path) to your dummy file.

-Chris


Rename Finder Files in Front Window from Excel Spreadsheet v1.11.kmmacros (10 KB)

Hey Michael,

The macro in post #15 has been bumped to v1.11 to fix an iteration issue.

-Chris

Wow great.

I will test it tomorrow.

Thanks Chris

Michael Luber mobile

Hi Chris,

what can I say!

Legendary! It's working really fantastic!

Thanks a lot for your help!

Michael Luber mobile

1 Like

Hi Chris,

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?

Thanks,

Michael

Or is there a way to create a macro without Apple Script so that I can help me on my own.

Maybe with your Batch Rename Files Macro?

Thanks