Using Automator to Batch Rename Files from CSV

Here is a revised script that you can read more about in its comments. It does not skip any lines in the CSV and assumes that the CSV is in the same folder as the files to rename. Change the name of the CSV in this script for yours.

Using the following CSV contents, I tested the revised script on macOS Sonoma 14.3.1.

"6BR1L,10033410-410.txt","6BR1L,10033410-a.txt"
"6BR1L,10033410-411.txt","6BR1L,10033410-b.txt"
"6BR1L,10033410-412.txt","6BR1L,10033410-c.txt"
"6BR1L,10033410-413.txt","6BR1L,10033410-d.txt"
"6BR1L,10033410-414.txt","6BR1L,10033410-e.txt"
"6BR1L,10033410-415.txt","6BR1L,10033410-f.txt"

and the revised script itself:

#!/bin/zsh

: <<"COMMENT"
Process a CSV where separator characters may exist in the filename and
by using a regular expression capture on the double-quoted columns in the
CSV, we can ignore the separator character itself. Assumption for this script
is that the CSV file is in the same folder as the files to process.

Usage: ./csvtest.zsh folderpath
COMMENT

CSVFILE="6B.csv"

# drop into selected folder chosen from Ask for Finder Items action
cd "${1}"

let cnt=0

while read -r line
do
    # use a regular expression to capture quoted content on either side of the
    # arbitrary CSV separator character. This will result in a capture array
    #  in the match variable. The dot between the captures is the real
    # separator character which becomes irrelevant to processing.
    [[ ${line} =~ "(\".*\").(\".*\")" ]] || continue

    # now we can remove the double-quotes around the filenames so they
    # will test for existence and rename properly
    old_file="${match[1]:gs/\"//}"
    new_file="${match[2]:gs/\"//}"

    # if old filename exists then rename it
    if [ -e $old_file ]; then
        mv "${old_file}" "${new_file}"
        (( cnt++ ))
    else
        echo "${old_file}" >> ~/Desktop/Errors.txt
        (( cnt-- ))
    fi
    # Only if created on Windows, remove carriage returns from line endings
done < <(perl -lne '$_ =~ s/\015?//g && print;' "${CSVFILE:a:l}")

# if return code from above is 0 then processing was successful and we return
# true with the renamed file count
[[ $? -eq 0 ]] && echo "true $cnt" || echo "false $cnt"

AppleScript Code

on run {input, parameters}
	
	set {TID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, space}
	set temp to text items of (input as text)
	set AppleScript's text item delimiters to TID
	set status to (item 1 of temp) as boolean
	set cnt to (rest of temp) as text
	
	if status then
		display dialog "Processing complete with  " & cnt & "  files renamed." with title "Processing Status"
	else
		display dialog "There was an unknown problem" with title "Processing Status"
	end if	
	return input
end run

The Zsh script solution that I used in the linked post was for industry standard CSV with normalized filenames that do not include a separator character in the filename. That is how this works below, by splitting on the only separator character on the line to create an array entry for each column:

csv=("${(@s/,/)line}")

but to protect those nonstandard punctuatioin characters in a filename, each CSV column must be enclosed in double-quotes with the separator character between them. In order to capture each column and honor the separator character, this now must be done with a regular expression capture of both quoted columns:

for a row (line) that appears like this:

"6BR1L,10033410-410.txt","6BR1L,10033-410.txt"



[[ ${line} =~ "(\".*\").(\".*\")" ]] || continue
old_file=${match[1]}
new_file=${match[2]}

and now the following are formed:

old_file: "6BR1L,10033410-410.txt"
new_file: "6BR1L,10033-410.txt"

and assuming that the filenames above are in the same directory as does that original script, then one can rename them in UNIX via:

/bin/mv ${old_file} ${new_file}

The regular expression approach above doesn't care what separator character is used, only in capturing the individual columns inside the double quotes.



Numbers has an Export To CSV (comma delimiters) or TSV (tab delimiters). It offers no other separator character. The script solution posted here for WalkinDude15 will not work for you because it is expecting quoted columns, and yours is not. There is a one-line change to make it work for your 6 line data example — even if you had semi-colons as delimiters:

Change this single line in the last script example I posted above:

[[ ${line} =~ "(\".*\").(\".*\")" ]] || continue

To:

[[ ${line} =~ "(.*)[,;\t](.*)" ]] || continue

This will use a comma, semi-colon, or tab character as the CSV delimiter and in my tests using your data with Sonoma 14.3.1 and Numbers v13.2 and a CSV with commas, and again, with semi-colons, performed the file renames correctly.

Put your titles.csv file in the same folder as your .wav files and run the script using that folder location as the argument to the script on the command line. In my test:

./test.zsh ~/Desktop/TEST

true 6

You are welcome. Always glad to save someone months of work.

In Excel 16.82, I enclosed each cell in double-quotes in each of two columns, and saved as a Comma Separated Values (.csv) file. Excel did this correctly and also displayed the following warning:

Which I ignored without any need to modify the CSV and here is how Apple's Quick Look displayed that saved CSV:

A colon in a filename will cause the Finder to replace and display it as a / character because the colon is a Finder file path separator character just as the / is a POSIX filesystem separatator in UNIX. This is back to the issue about non-standard filename characters causing issues (in this case, with Finder), and other reserved characters can cause issues with filenames in UNIX.

Hi, I am trying to get this to work with Numbers 13.1. Does the script need modifying for that to work? I can´t see an option to make the csv without semicolons on export.

Here are screenshots of the files:

Hi, I am trying to get this to work with Numbers 13.1. Does the script need modifying for that to work? I can´t see an option to make the csv without semicolons on export.

Here are screenshots of the files:

Excellent, works perfectly. Thanks alot!

You are welcome.