Need To Batch Rename From .xls Using Automator

DirtyCzech

Registered
HELLO! I've done an extensive search for help on this topic and thought it would be better to ask the Pros. I need to rename close to 5000 images. I need to import file names from an Excel doc because the original file names are nothing close to the new file names (I'm dealing with a very unorganized workflow). A Better Find Rename costs $20 but I thought for sure there is a way to do this using Automator (or whatever other method) at no cost.

Example:

old file name: 773602222483

new file name: 5900090_MJ4G_A_020

I'm not savvy with scripting so whatever drag-and-drop method that gets the job done is preferred.

Thanks!
 

DirtyCzech

Registered
can you get to a flat file (not a spreadsheet) two columns of names: from to
If the names never have spaces, you can separate the names with a space. If they do, find a character that does not exist in the file names. This link might help with this first step: http://www.howtogeek.com/79991/convert-an-excel-spreadsheet-to-a-tab-delimited-text-file/

One you have such a file, I can help with a trivial script to convert the two names into a "mv" command.


I do have a couple MASSIVE Excel spreadsheets that I can create tab delimited text files from. Whatever you recommend would be a huge help.
 
Last edited:

pedz

Registered
If you have a tab delimited file, then this will be a good first step. Lets assume the file is called renames.txt. It has two columns separated by a tab with the from names on the left and the "to" names on the right.

Select the text between the dotted lines and copy it to your clipboard.

-----
#!/bin/bash

awk "-F\t" "{ printf(\"mv '%s' '%s'\n\", \$1, \$2); }" $1 > $2
-----

Open the terminal and do:

cat > move.sh

Now hit command-V to paste, hit return, then hit control-D and you should get back to a prompt.

cat the file with: cat move.sh

and inspect that the local copy matches the original.

Give it execute permissions with: chmod +x ./move.sh

Now, do:

./move.sh renames.txt output.txt

It should run without any messages. This is not doing anything but creating output.txt

Now, carefully review output.txt. Each line should be an mv command with the original file name wrapped in single quotes followed by the destination file name also wrapped in single quotes. Now, here is the first hiccup.

If any of the file names have single quotes in them, those lines will fail. Usually what I do is just do those by hand since it is just a few. So, if you have file names with single quotes in them, I would just go through the original renames.txt file and remove them and just do those by hand.

Once you are sure that the output.txt file is correct, then you can execute it with:

sh output.txt

But... the more I sit and think about this, the more issues I keep thinking of. It would be far better to get someone local to help you out.

For example, are all of the files in the same directory? If not, then the path names in renames.txt must include the full directory path. Are you moving files between directories and the new directories do not exist? If so, then you need to create those directories (folders) ahead of time. etc.

One thing that is definitely wise is to copy your original spreadsheet and delete all but the first 10 lines. Then test and trial with those 10 renames until you have a sequence of steps that work.

Perry
 
Top