Rename Files in the Finder With Sorting From a Spreadsheet

Questions & Suggestions
2

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

3

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!

4

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.

5

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.

6

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

7

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

Unbenannt
Unbenannt.JPG831×374 30.2 KB

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

8

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.

9

Hi,

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

10

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

11

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

12

Ok, I post the question in a seperate topic!

Thanks,

Michael

13

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)

Keyboard Maestro Export.png775×1379 121 KB

14

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

15

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)

Keyboard Maestro Export.png891×1305 132 KB

16

Hey Michael,

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

-Chris

17

Wow great.

I will test it tomorrow.

Thanks Chris

Michael Luber mobile

18

Hi Chris,

what can I say!

Legendary! It's working really fantastic!

Thanks a lot for your help!

Michael Luber mobile

1 Like
19

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

20

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

21

If you have a set of mappings from one file name to the other file name then you can write a macro to rename files that does not need any AppleScript.

If I had it in a spread sheet, I would probably just copy it from there and paster it in to a Set Variable to Text action at the start of the macro.

Presuming the copied data was of the form:

sourcename1<tab>destname1
sourcename2<tab>destname2
sourcename3<tab>destname3

…

Then you can do a fairly simple process of:

Note: You really want to have a good backup of your whole Mac before doing anything like this, and typically I would duplicate or archive the folder before starting to ensure I could easily undo any mess the macro makes.

If any file in the folder may have regex special characters, then you will want to use a For Each Lines In collection to iterate through the lines in the Mapping, and then extract the source and dest using a Search action with regex (?m)^(.+)\t(.+) and then compare the resulting source to the file name.