Shortcuts/Automator help - Batch rename files from a spreadsheet

[hj8ag]

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.

😎

Thanks so much for all your help with this, this makes a lot of sense.

I've just tried to run the command again with my final CSV. I have a few files listed as things like 'File 1 (EU) [SOMETHING].jpg' in the 'final' name column (i.e. column 1). Now when I run the command I'm getting the following:

zsh: unknown file attribute: zsh: unknown file attribute: zsh: no matches found: (EU) zsh: no matches found: (EU) zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unmatched "

Do I have to pass something to terminal to tell it to ignore characters such as '('?

 

[Slartibart]

Thanks so much for all your help with this, this makes a lot of sense.

I've just tried to run the command again with my final CSV. I have a few files listed as things like 'File 1 (EU) [SOMETHING].jpg' in the 'final' name column (i.e. column 1). Now when I run the command I'm getting the following:

zsh: unknown file attribute: zsh: unknown file attribute: zsh: no matches found: (EU) zsh: no matches found: (EU) zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unmatched "

Do I have to pass something to terminal to tell it to ignore characters such as '('?

The problem are the spaces in these file names. If these are just a few files you could rename these deleting the spaces.

It might possible to deal with that quickly in awk by setting the internal field separator variable to a new line -> IFS=$'\n' - but that’s just from the top of my head 🤣

Open a terminal window and execute

export IFS="\n"

in the same terminal window then try to run again:

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

and report. 🤓

 

[hj8ag]

The problem are the spaces in these file names. If these are just a few files you could rename these deleting the spaces.

It might possible to deal with that quickly in awk by setting the internal field separator variable to a new line -> IFS=$'\n' - but that’s just from the top of my head 🤣

Open a terminal window and execute

export IFS="\n"

in the same terminal window then try to run again:

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

and report. 🤓

Annoyingly I'd say 80% of the files (267 items) have spaces or spacing in the names 'Paris (EU) [DMGVBEO].jpg' etc.

Just tried to run those commands and got the following:

~ % cd /Users/USERNAME/Desktop/LotCheck_Test User@User-MBP LotCheck_Test % export IFS="\n" User@User-MBP LotCheck_Test % cat ./rename.csv | awk -F "," '{print "mv "$1" "$2}' | zsh zsh: unknown file attribute: zsh: unknown file attribute: zsh: no matches found: (EU) zsh: no matches found: (EU) zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unknown file attribute: zsh: unmatched "

 

[Slartibart]

Before diving into additional scripting: in your spreadsheet simply put the the file names in quotes, e.g. "Paris (EU) [DMGVBEO].jpg". Export the .csv file and run the shell command sequence.

Alternatively it might be sufficient to replace within the spreadsheet the spaces in the names with a backslash+space: Paris\ (EU)\ [DMGVBEO].jpg.

 

[hj8ag]

Before diving into additional scripting: in your spreadsheet simply put the the file names in quotes, e.g. "Paris (EU) [DMGVBEO].jpg". Export the .csv file and run the shell command sequence.

Alternatively it might be sufficient to replace within the spreadsheet the spaces in the names with a backslash+space: Paris\ (EU)\ [DMGVBEO].jpg.

Will give this a go now. In the first option, should I also put the current filenames (i.e. DMGVBEO) in quotes too? Or just the 'rename' column?

 

[Slartibart]

Will give this a go now. In the first option, should I also put the current filenames (i.e. DMGVBEO) in quotes too? Or just the 'rename' column?

fields which contain names with spaces, wherever - for sure you can set a condition for »when there is a space between alphanumerical content in a field of a column, put the whole field in quotes« … or at least escape the spaces with a backslash.

If there is a space in the content of either variables in the awk-command it will throw an error.

Maybe there is way to configure the CSV-output?

 

[hj8ag]

fields which contain names with spaces, wherever - for sure you can set a condition for »when there is a space between alphanumerical content in a field of a column, put the whole field in quotes« … or at least escape the spaces with a backslash.

If there is a space in the content of either variables in the awk-command it will throw an error.

Maybe there is way to configure the CSV-output?

Almost there I think!

So I've just tried again after adding Quote marks to my dataset. I also had a few other errant characters like ' and :, so I've removed all those too and replaced them with '_'s or spaces. I'm now getting the following:

DMGL4X-0 to Paris Sunset (EU) [DMGL4X-0].jpg\r: No such file or directory zsh: unmatched " user@User-MBP LotCheck_Test %

DMGL4X-0 is my first row in the CSV. I've tried swapping this row out for others and I get the same error (just with the substituted file's details instead of this one)

Most of the files are being renamed, but there are about 100 or so that have stayed the original name (220 total rows)

 

[Slartibart]

in relation to »DMGL4X-0 to Paris Sunset (EU) [DMGL4X-0].jpg\r:«

double points : are used as folder seperators on macOS.

the \r represent a return. Line endings differ depending on the platform - macOS use new line/linefeed \n, others carriage return, or even new line + carriage return.

As far as I can see the remaining problems are rooted within the spreadsheet data entries and the file export from there.

I recommend open the exported CSV-file in BBEdit or any true ASCII-editor and configure it to display invisible characters (spaces, tabs, line endings, and more) and work from there:
The CSV has to contain just 2 columns. \n should be visible only after the name in the second column.
Any occurence of space, %20, %, \r, or whatever will probably produce an error if not probably escaped or converted - so you have to make sure that your exported file and any name of an actual file does not contain these.

There isn’t much more to do here from “outside” - yes, you could write a nicer shell script which e.g. incorporates the renaming step, automatically finds and uses the .csv-file in a folder, switches folders, etc. - but IMHO this is beyond the scope of this thread. 🤓

You have to fix your data set - spreadsheet and/or CSV-export.

 

[hj8ag]

Just want to say thanks for all your help with this! Opened up the file in BB Edit and all looked ok. I enclosed each row of the first column in quote marks and that did the trick. For some reason I had a '?' at the end of every renamed file, but easily removed that with automator!

Thanks so much for all your help, much appreciated.