Shortcuts/Automator help - Batch rename files from a spreadsheet

[hj8ag]

I've got a bit of a shortcuts/automator question for anyone who might be able to help

At the moment I have a folder of files named after checksums (i.e. 7d835e2e1ddba48c0d135d72ef5dcdd1). I have a spreadsheet that contains a column for the checksum, then a second column with the actual filename and a third with a brief description of the file. (e.g. 7d835e2e1ddba48c0d135d72ef5dcdd1, BuckinghamPalace.JPG, A picture of Buckingham place)

I've been going through the files one after the other and renaming them individually by CMD+F the checksum and then manually copying and pasting the info, but to be honest its going to take me forever to do this.

So I'm just wondering if there is a way to batch automate this with shortcuts or automator? Ideally I'd like to be working smarter, not harder on such a rote task like this

 

[NoBoMac]

With AppleScript, and if the spreadsheet is in csv format, pretty easy to read in. Snippet to read in a csv containing sports team schedule:

set theFile to "myfile.csv"
set AppleScript's text item delimiters to ","

try
        set dataBlob to (every paragraph of (read theFile)) -- suck in the csv
       
        set gitems to (count of dataBlob) - 1
       
        -- Yes, dataBlob - 1 as the count is based on newlines (last line
        -- has a newline even though nothing after it
       
        repeat with i from 1 to gitems
            --
            -- parse each item in the row
            --
            set gentry to (every text item of (item i of dataBlob))
           
            set gtime to item 3 of gentry -- get the EDT of game start
            set gtv to item 6 of gentry -- get the media availability field
            --
            -- do stuff with read values
            --
        end repeat
       
    on error errorMessage number errorNumber
        error errorMessage number errorNumber
end try

Shell script is also a way to do it:

cat foo.csv | awk -F, '{print "mv "$1" "$2}' | /bin/zsh
#
# or
#
awk -F, '{print "mv "$1" "$2}' foo.csv | /bin/zsh

Shortcuts should be doable as well, Shortcuts on Mac has an option to pass an input file to it (or can simply prompt for it). Example attached, adapting for one's needs left as an exercise.

And if doing csv, I'm a fan of tab delimited vs commas, as I find it easier to parse manually and not get false separations if a field has commas in it.

 

[Slartibart]

already solved. see above.

 

[hj8ag]

Thanks so much, appreciate that - will give both options a go today!

 

[Slartibart]

sorry… did it again. 🙃

 

[hj8ag]

With AppleScript, and if the spreadsheet is in csv format, pretty easy to read in. Snippet to read in a csv containing sports team schedule:

set theFile to "myfile.csv"
set AppleScript's text item delimiters to ","

try
        set dataBlob to (every paragraph of (read theFile)) -- suck in the csv
       
        set gitems to (count of dataBlob) - 1
       
        -- Yes, dataBlob - 1 as the count is based on newlines (last line
        -- has a newline even though nothing after it
       
        repeat with i from 1 to gitems
            --
            -- parse each item in the row
            --
            set gentry to (every text item of (item i of dataBlob))
           
            set gtime to item 3 of gentry -- get the EDT of game start
            set gtv to item 6 of gentry -- get the media availability field
            --
            -- do stuff with read values
            --
        end repeat
       
    on error errorMessage number errorNumber
        error errorMessage number errorNumber
end try

Shell script is also a way to do it:

cat foo.csv | awk -F, '{print "mv "$1" "$2}' | /bin/zsh

Shortcuts should be doable as well, Shortcuts on Mac has an option to pass an input file to it (or can simply prompt for it). Example attached, adapting for one's needs left as an exercise.

And if doing csv, I'm a fan of tab delimited vs commas, as I find it easier to parse manually and not get false separations if a field has commas in it.

Just giving the shortcuts version a go and running into a few errors.

Here's a sample of what the CSV looks like:

and my shortcuts 'recipe':

When I run it, I just get the result shown above (a new text box that says 'OldName') but the few test files I've moved are still called the same thing...

Do I need to customise the actions further? I'm quite new to the shortcuts app beyond incredibly simple tasks, so forgive my ignorance here!

 

[Slartibart]

• export your spreadsheet as CSV or ASCII text file. Make sure it is in the same folder than the images.
• Open a terminal window.
• Type in it cd and drag and drop the folder icon in which the files and the CSV/ASCII text file is located. Press Return.
• copy and paste cat ./your-textfile.csv | awk -F"," '{print "mv "$1" "$2}' from @NoBoMac into the terminal window. Press Return.
• Watch the magic happening… 🤓

 

[Slartibart]

Here's a sample of what the CSV looks like:

Remove the first line with the column titles from the CSV file.

 

[NoBoMac]

What they said^^^.

In the case of the Shortcut, will first need code to get the file to move, then do the move.

 

[hj8ag]

Remove the first line with the column titles from the CSV file.

Just given this method a go and for some reason still having no joy!

I took out that first row from the CSV and renamed it to 'rename.csv'

Here was my terminal command after changing the directory to 'LotCheck_Test' which is the folder containing files & CSV:

& Here's a grab of the folder afterwards:

I would have expected the file 'DMFABVE0' to now be called 'RockyMountain.jpg' but it doesn't seem to have worked...

Where am I going wrong?

 

[Slartibart]

It’s the missing path to your files. Quick&dirty 🤪, try:

cat ./rename.csv | awk -F "," '{print "mv ./"$1" ./"$2}'

with your test file.

./ is the current directory - you basically add this to the move command sequence for the input and output.
The output in the terminal when running the above command sequence should be:

mv ./DMGABVE0 ./RockyMountain.jpg

for your example.

A more elegant solution is to use a variable to assign the current working directory, but I am away from my iPad or MB so I am currently unable to really suggest and verify something else.

An even more quick&dirty solution is to add ./ before the names of each column in the CSV-file and run the "original" commands. 🤣

 

[NoBoMac]

Missing " | zsh" at the end of the command. Right now, just printing to the screen vs executing the commands.

 

[Slartibart]

Missing " | zsh" at the end of the command. Right now, just printing to the screen vs executing the commands.

Aahh… if you copy something... I’ll go back thanks my rock… 🤪

 

[hj8ag]

Just tried this out and now getting:

LotCheck_Test % cat ./rename.csv | awk -F, '{print "mv ./"$1" ./"$2}' | zsh mv: rename ./DMGABVE0 to ./RockyMountain.jpg: No such file or directory

have also tried:
cat ./rename.csv | awk -F, '{print "mv /"$1" /"$2}' | zsh

and this doesn't seem to work either

really sorry to keep posting, but just not understanding where I'm going wrong here.

 

[Slartibart]

have also tried:
cat ./rename.csv | awk -F, '{print "mv /"$1" /"$2}' | zsh

this will not work - or better: that would require that the files are located in the root folder, something that you do not want… besides that it might no even easily possible. 🤓

You are in the folder with the DMGABVE0 file - this doesn’t have any extension, does it?

can you kindly try to execute:

mv ./DMGABVE0 ./RockyMountain.jpg

in the terminal?
Does that work?
First you have to change into the folder with the file DMGABVE0:

cd /where/ever/that/folder/is/

You can use

ls -l ./DMGABVE0*

to see more information about the file. Maybe you can provide the output here?

Importantly: which version of macOS are you running?​

 

[hj8ag]

this will not work.

You are in the folder with the DMGABVE0 file - this doesn’t have any extension, does it?

can you kindly try execute:

mv ./DMGABVE0 ./RockyMountain.jpg

in terminal? Does that work? First you have to change into the folder with the file DMGABVE0

Yep, I used CD to change into that folder.

Can confirm the below command worked!

mv ./DMGABVE0 ./RockyMountain.jpg

 

[Slartibart]

and executing

cat ./rename.csv | awk -F "," '{print "mv ./"$1" ./"$2}' | zsh

or

cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh

doesn’t ?

Which macOS version are you running?

EDIT: can you please report the output of echo $0?

 

[hj8ag]

and executing

cat ./rename.csv | awk -F, '{print "mv ./"$1" ./"$2}' | zsh

or

cat ./rename.csv | awk -F, '{print "mv "$1" "$2}' | zsh

doesn’t ?

Which macOS version are you running?

EDIT: can you please report the output of echo $0?

Sorry for the delayed reply, have just sat down at my Mac again and tried these commands.

I'm on an M1 Max MacBook Pro, running 12.5.1

Here's the latest results:

both the cat commands don't seem to work, but the mv one works perfectly.

 

[Slartibart]

if you execute cat ./rename.csv in the terminal, what is the output?

EDIT: if rename.csv for testing purposes only has one line please add another empty one by hitting return.

 

[hj8ag]

if you execute cat ./rename.csv in the terminal, what is the output?

EDIT: if rename.csv for testing purposes only has one line please add another empty one by hitting return.

I just added another line and executed this command and it returns the following:

DMGABVE0,RockyMountain.jpg None,Test.jpg[B]%[/B]


If I execute the original code I get the following:

LotCheck_Test % cat ./rename.csv | awk -F, '{print "mv ./"$1" ./"$2}' | zsh mv: rename ./DMGABVE0 to ./RockyMountain.jpg\r: No such file or directory mv: rename ./None to ./Test.jpg: No such file or directory

 

[Slartibart]

DMGABVE0,RockyMountain.jpg None,Test.jpg[B]%[/B]

Do you have a file called None which you want to rename Test.jpg?
The last line is garbage - please delete it and hit return after the RockyMountain.jpg entry. So rename.csv looks like:

DMGABVE0,RockyMountain.jpg
Then try:

cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh


EDIT: please execute

echo $HOME

in the terminal. You should see some path.
cd into the folder with rename.csv and your image files.
type pwd and press return. You should see the full path to the folder your in, e.g. /Users/YourUserName/what/ever/subfolder/

type
export HOME=/Users/YourUserName/what/ever/subfolder
and press return. You should be able to select and copy and paste the full path from the previous output of pwd in the terminal.

Check via echo $HOME wether the HOME-variable is set to the new path.

Execute
cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh

 

[hj8ag]

Do you have a file called None which you want to rename Test.jpg?
The last line is garbage - please delete it and hit return after the RockyMountain.jpg entry. So rename.csv looks like:

DMGABVE0,RockyMountain.jpg
Then try:

cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh

That seems to have worked!

 

[Slartibart]

It might be easier to do the following:

• open a terminal.
• execute which zsh - it will output something like /bin/zsh Take note of it.
• execute cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' >./renamer
• Open the newly created file renamer in a texteditor - e.g. BBedit (free mode is sufficient). It should contain one mv-sequence per line, e.g.:

mv DMGABVE0,RockyMountain.jpg​

mv AnotherChecksum1,AnotherName1​

mv AnotherChecksum2,AnotherName2​

.​

.​

.​

• Add as the first line: #!/bin/zsh - change the path after #! according to the output of which zsh:

#!/where/ever/is/zsh​

mv DMGABVE0,RockyMountain.jpg​

mv AnotherChecksum1,AnotherName1​

mv AnotherChecksum2,AnotherName2​

.​

.​

.​

• Save renamer.
• execute chmod u+x ./renamer in the terminal.
• type ./renamer and press return. Check the folder, your files should be renamed.

 

[Slartibart]

That seems to have worked!

Well, don’t bother with my additional comments… in medias res… happy renaming! 🤓

nota bene: make sure that the last line with a naming pair in the csv-file ends with a return - and does not contain some garbage.

 

[Slartibart]

TL;DR - What happened here?​

Given the OP’s scenario files can be renamed via the terminal with the following command sequence:

cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh

the cat command outputs the content of a .csv-file with the old and new file names, one pair of names separated by comma per line.

this is passed using a pipe | to awk. -F "," tells awk that the field seperator used in/from the .csv-file is a comma, and to output linewise mv followed by what ever is in the first ($1) and second ($2) column of the .csv-file.

finally this is passed using a pipe to the zshell (zsh) which executes the awk output as a shell command sequence and ultimately moves a file matching the name in the first column of the .csv to a file named according to the second column.

😎