Automator - File rename script

Ok. This script replacement will actually remove carriage returns (\015) characters (if they exist) but not newlines in each row. So it universally handles CSV from Windows or Mac as it was supposed to do before. Again, tested with your data and no "?" in renamed filenames this time. Also, ensure that every row in the CSV has a line ending, which would entail a blank last line, or the last entry in the CSV will not get renamed.

#!/bin/bash

cd "$1"
while read line       
do       
     OldImageName="${line%,*}"
     OldExt="${OldImageName##*.}"
     NewImageName="${line#*,}.$OldExt"
     # printf '%s\t%s\n' "$OldImageName" "$NewImageName"

     if [ -e "$OldImageName" ] ; then
          mv "$OldImageName" "$NewImageName"
     else
          echo "$OldImageName" >> ~/Desktop/Errors.txt
     fi
     # Remove CR but not NL
done < <(perl -lne '$_ =~ s/\015?//g && print;' "$2")
exit 0

The Automator flow has to be changed to the following in order that the folder becomes $1 and the CSVfile becomes $2 in the shell script. You can replace my do shell script content with the Tony T1's script. Assumption: no header row in the CSV and the delimiter is a comma.

I ran the script with your data and did not encounter any trailing '?' on the destination filename. My .csv content looks like this in Quick Look:

In the actual .csv, there is no trailing white-space after the first column, nor after the second column. Similarly, there should only be a single comma between the first and second column data, with no trailing white-space, or other characters following the second column.

Good to hear the script is handling your manual input CSV. I have discovered something in your Second experiment, and why one normally exports Windows Excel content to a UTF-8 CSV without the BOM mark. Character encoding.

You have an invalid hex character (d0) embedded in each Column A filename. Here are the first two entries in your Second Experiment hex dump. The red period preceding the dimensions is the bogus character that has no conversion equivalent in ascii or UTF-8 character sets:

I suspect that this bogus character is interfering with the script processing and the UNIX renaming command.

If you are copying/pasting these filenames from a Windows version of Excel, it is probably using some character from the Windows CP125n character set. If you revisit the particular cells in the Excel spreadsheet, what character is shown in this filename position marked with the red period?

All I did was show you the workflow that gets the selected folder as $1 and the chosen .csv file as $2 into the Run Shell Script.

The script from Tony T1 assumes that your second column has the filename and extension, as the script does not attempt to add the extension to the renamed file. The script will only work if the column delimiter between your first and second column is a comma, and not anything else.

The revised script to automatically handle the extraction of the original extension and append it to the NewImageName is in the following:

#!/bin/bash

cd "$1"
while read line       
do       
     OldImageName=${line%,*}
     OldExt="${OldImageName##*.}"
     NewImageName="${line#*,}.$OldExt"
     # printf '%s\t%s\n' "$OldImageName" "$NewImageName"

     if [ -e "$OldImageName" ] ; then
          mv "$OldImageName" "$NewImageName"
     else
          echo "$OldImageName" >> ~/Desktop/Errors.txt
     fi
done <"$2"



To answer an earlier question, the order of the filenames in your CSV and in the selected directory does not matter.

Is the source of the CSV data from a Windows machine, or originated on the Mac? That '?' might be a carriage return character, or some other non-printing character.

I have done further testing here and have found that if the rows in the CSV end with a Windows carriage return, and not the expected UNIX linefeed, that you indeed inherit the ? mark in the renamed filename.

In the Terminal, run the following to convert the current csv to one that has the carriage returns all converted to line feed endings. Use the fixed.csv with the script:

sed -E 's/\R/\012/g' < ./current.csv > fixed.csv

Here is a replacement script that proactively replaces any carriage return line endings with linefeeds, and then sends that output into the while loop to process the rows normally. No more '?' in the renamed filenames. Tested on Mojave 10.14.6 with your data formats.

#!/bin/bash

cd "$1"
while read line       
do       
     OldImageName="${line%,*}"
     OldExt="${OldImageName##*.}"
     NewImageName="${line#*,}.$OldExt"
     # printf '%s\t%s\n' "$OldImageName" "$NewImageName"

     if [ -e "$OldImageName" ] ; then
          mv "$OldImageName" "$NewImageName"
     else
          echo "$OldImageName" >> ~/Desktop/Errors.txt
     fi
done < <(sed -e 's/\R/\012/g' "$2")
exit 0

I tested this updated script with your original and replacement filenames in a CSV that I confirmed had carriage returns (only) for line endings. After the script ran, there were no renamed filenames that contained a trailing question mark. The sed command will be applied to every line in the CSV, so there is something else going on with your data. It will not fix existing files in your folder that already have the trailing question mark.

Instead of us guessing what this file looks like, I suggest it be printed out in hex, as a debugging measure. Add this line to the top of the script"

hexdump -C "$2" >>"/Users/${USER}/dumped-CSV-Input.txt"

You would then display the file dumped-CSV-Input.txt in your home folder with TextEdit or similar apps. Post here or on pastebin.com. Make sure you delete private information.

That gives:

#!/bin/bash

# new line for debugging
# assuming I guessed correctly and $2 is the name of the input file.
hexdump -C "$2" >>"/Users/${USER}/dumped-CSV-Input.txt"

cd "$1"
while read line       
do       
     OldImageName="${line%,*}"
     OldExt="${OldImageName##*.}"
     NewImageName="${line#*,}.$OldExt"
     # printf '%s\t%s\n' "$OldImageName" "$NewImageName"

     if [ -e "$OldImageName" ] ; then
          mv "$OldImageName" "$NewImageName"
     else
          echo "$OldImageName" >> ~/Desktop/Errors.txt
     fi
     # Remove CR but not NL
done < <(perl -lne '$_ =~ s/\015?//g && print;' "$2")
exit 0