Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

bulk file rename using csv

Hey guys,


I've been trying to use the automator workflow VikingOSX provided but with no luck. I get a process completed message with no errors, but the file name does not change. I'm using this workflow below, my csv is data.cxv and is UTF-8. not sure what im doing wrong here, any help is appreciated.


cd "$1"


# simply exit if CSV file is absent or empty

[[ -s data.csv ]] || exit 1


# Strip quoted CSV fields (if present) before reading the data

# Dynamically adapt IFS to different CSV delimiters

while IFS=$'\t,;:!' read oldfile newfile

do

# Skip entry if old filename in CSV doesn't exist in current folder

[[ -f "./$oldfile" ]] || continue

# printf '%s\t%s\n' "$oldfile" "$newfile"

# rename

mv $PWD/{"$oldfile","$newfile"}

done < <(sed -e 's/"//g;' < ./data.csv)

/usr/bin/osascript -e 'display dialog "Processing complete."' &> /dev/null

exit 0



MacBook Pro with Touch Bar

Posted on Sep 2, 2020 7:28 PM

Reply
Question marked as Best answer

My last post had a syntax error in it which I didn't catch until later this evening. The following script only processes CSV with comma separated values, and as before, fixes trailing commas, or replaces the forward slash in names with an underscore. Again, tested with your old, and new filenames as data:


DIR="$1"
shift
CSV="$1"

# unnecessary, but if the CSV file doesn't exist, exit
# [[ -s "$CSV" ]] || exit 1

cd "${DIR}"

# works with comma delimited CSV
while IFS=',' read oldfile newfile
do
	# do not process headings
	[[ ! "${oldfile}" =~ \.(jpg|jpeg|png)$ ]] && continue

    # Skip entry if old filename in CSV doesn't exist in current folder
    [[ -e "${oldfile}" ]] || continue

	# strip any trailing empty comma delimiters from end of newfile
	newfilex="${newfile//,/}"
	# replace any detected illegal forward slash with an underscore
	newfilex="${newfilex//\//_}"

    # rename files
    /bin/mv "${oldfile}" "${newfilex}"
# remove any double-quoted items and if from Windows, trim the CR from the line
done < <(sed -e 's/"//g;' "${CSV}" | tr -d '\r')
# done < "${CSV}"

sleep 1
/usr/bin/osascript -e 'display dialog "Processing complete."' &> /dev/null
exit 0


I am not for hire, as I participate here voluntarily for no compensation. You never want to post personal contact information in the Apple Support Communities, because if the Apple hosts do not remove it soon enough, it will be forever available in the Google archives for trolling spammers.


There are others that I am helping, and my time is limited to the code that I have already provided.

Posted on Sep 4, 2020 8:03 PM

23 replies

Sep 2, 2020 9:16 PM in response to mnagourney

I have as yet no idea what sort of mass renaming you need to do but those programs allow use of regular expressions or other place makers that can enable some fairly extensive file renaming. However, they are not able to assign arbitrary filenames to an arbitrary collection of files. They would not likely work if you wanted to rename a file such as MyPicture 208.jpg to Rebecca in Sicily.jpg. However, they can be fairly sophisticated. You may want to take a look. They are free.


Sep 3, 2020 7:44 AM in response to Kappy

I appreciate the resources and I did look through the apps. As I mentioned I specifically need to rename using a csv file as the source for renaming the files. There are apps that do so but I don't believe any are free for more than a few trial conversions. Automator is the choice way to do this for most but I'm having trouble getting it to work correctly.

Sep 3, 2020 8:12 AM in response to VikingOSX

the cxv was a typo, my mistake. my file is indeed data.csv


Have tried a number of different scripts from forums posted by you and others but with no luck.


Tried the below version as well but same results. completed but no change in file names....I don't think I understand the Set value of variable, maybe that's where I'm going wrong? is that a value you just enter?


Sorry, I really don't know what I'm doing here, just trying to piece together code others have provided.


Sep 3, 2020 10:09 AM in response to mnagourney

The following works on Mojave 10.14.6 (18G6020) as an Automator application. It prompts twice: 1) image folder, and 2) CSV file. I have enhanced it to automatically deal with Windows line endings in the CSV (if encountered), ignore any CSV header row, provided it finds oldfile in the header, and as previous, trims any double-quotes from the column items.



Here is the code for the Run Shell Script:


DIR="$1"
shift
CSV="$1"

# unnecessary, but if the CSV file doesn't exist, exit
[[ -s "$CSV" ]] || exit 1

cd "${DIR}"

# Dynamically adapt IFS to different CSV delimiters

# while IFS=$'\t,;:!' read oldfile newfile
while IFS=, read oldfile newfile
do
	# do not process headings
	[[ oldfile = "oldfile" ]] || continue

    # Skip entry if old filename in CSV doesn't exist in current folder
    [[ -e $oldfile ]] || continue
  
    #printf '%s\t%s\n' "$oldfile" "$newfile" > ~/Desktop/mycsv.txt

    # rename.
    mv "${oldfile}" "${newfile}"
# remove any double-quoted items and if from Windows, trim the CR from the line
done < <(sed -e 's/"//g;' "${CSV}" | tr -d '\r')

sleep 1
/usr/bin/osascript -e 'display dialog "Processing complete."' &> /dev/null
exit 0


Sep 3, 2020 6:43 PM in response to mnagourney

Replace the Do Shell Script code with the following content. It is tested using your updated CSV data items, and works fine here.


DIR="$1"
shift
CSV="$1"

# unnecessary, but if the CSV file doesn't exist, exit
# [[ -s "$CSV" ]] || exit 1

cd "${DIR}"

# Dynamically adapt IFS to different CSV delimiters
while IFS=$'\t,;:!' read oldfile newfile
do
	# do not process headings
	[[ ! "${oldfile}" =~ \.(jpg|jpeg|png)$ ]] && continue

    # Skip entry if old filename in CSV doesn't exist in current folder
    [[ -e "${oldfile}" ]] || continue
  
    # printf '%s\t%s\n' "$oldfile" "$newfile" > ~/Desktop/mycsv.txt

    # rename.
    /bin/mv "${oldfile}" "${newfile}"
# remove any double-quoted items and if from Windows, trim the CR from the line
done < <(sed -e 's/"//g;' "${CSV}" | tr -d '\r')
# done < "${CSV}"

sleep 1
/usr/bin/osascript -e 'display dialog "Processing complete."' &> /dev/null
exit 0


Sep 4, 2020 8:38 AM in response to VikingOSX

So I have three different groups of files with these formats. Sorry, I should have mentioned this before but didn't think the file name mattered.


When I ran the last script, the files with "Lines" or "plain" at the end all converted but also have 5 commas after name e.g. Plate - Skull - Radiographs - Lines,,,,


The third group, the one that just ends with a number (e.g. book41_sec2_fig12.jpg) did nothing.


Why is the file name influencing whether it works or not?


Also, just tried to run it again on a duplicate folder...this time it didn't change anything. No worries if you'd rather not spend too much time on this. I appreciate your help but can always pay a few bucks for an app to do this.






Sep 4, 2020 10:47 AM in response to mnagourney

A proper CSV does not have multiple, trailing empty fields in it, which your five commas represent. I have altered the script to automatically trim those commas from the newfile variable if present.


In the third category, the forward slash in the output filename, whether unescaped, or escaped, is invalid, and I have added code to replace that forward slash with an underscore, which does allow the script to process the third category.


DIR="$1"
shift
CSV="$1"

# unnecessary, but if the CSV file doesn't exist, exit
# [[ -s "$CSV" ]] || exit 1

cd "${DIR}"

# Dynamically adapt IFS to different CSV delimiters
while IFS=$'\t,;:!' read oldfile newfile
do
	# do not process headings
	[[ ! "${oldfile}" =~ \.(jpg|jpeg|png)$ ]] && continue

    # Skip entry if old filename in CSV doesn't exist in current folder
    [[ -e "${oldfile}" ]] || continue

	# strip any trailing empty column delimiters from end of newfile
	newfilex="${newfile//[,;:!\t]/}"
	# replace any detected illegal forward slash with an underscore
	newfilex="${newfilex//\//_}"

    # rename files
    /bin/mv "${oldfile}" "${newfilex}"
# remove any double-quoted items and if from Windows, trim the CR from the line
done < <(sed -e 's/"//g;' "${CSV}" | tr -d '\r')
# done < "${CSV}"

sleep 1
/usr/bin/osascript -e 'display dialog "Processing complete."' &> /dev/null
exit 0


Sep 4, 2020 7:08 PM in response to VikingOSX

I see, that all makes sense and sounds like it should fix the last two issues. However, now when I run it nothing happens at all. Automator completes the script but no changes in any files...


Also, I have another project I'd like to do with these files. Are you interested in writing the script for some money? I appreciate all your help so far. Here's what I'm trying to do:


  • Once they are all labeled correctly, I will have 3 versions of each jpg. The are organized like so (picture 1)
  • I want to create a pdf from each batch of 3 files and use a csv to name each pdf


Is this doable with the "New PDF from Images" function in automator? Works great to do them manually one batch at a time but I have about 650 batches. Let me know if this is possible and if you are interested. If so, I'll drop you my email to arrange.


bulk file rename using csv

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.