File rename based on excel doc.

Hi,

I was wondering if someone could help me with the below script.

I am attempting to achieve the following:

  1. Select folder where images are located
  2. Select excel file (column A original file name // column B new filename).
  3. File names are changed to new file name.

I have tried to re-appropriate the below script but I am struggling and was hoping somebody could have a look.

File names look like the below:

column A column B
py_1A1A0205-427_a.jpg ak_1A1A0205_a.jpg
py_1A1A0205-427_b.jpg ak_1A1A0205_b.jpg
py_1A1A0205-427_c.jpg ak_1A1A0205_c.jpg


property sourceFolder : (path to desktop as text) & "dossier sans titre - copie 2" -- adjust as appropriate

if sourceFolder does not end with ":" then set sourceFolder to sourceFolder & ":"
-- read in the list of names
# I don't specify the separator because I'm not sure that it will be a return.
# I let the job to AppleScript which is perfectly able to treat the different delimiters in use
set filenameList to paragraphs of (read file ((path to desktop as text) & "filenames.txt"))
-- in order to break out the two fields we need to play
-- with text item delimiters
set {oldDelims, text item delimiters} to {text item delimiters, tab}
if sourceFolder does not end with ":" then set sourceFolder to sourceFolder & ":"

-- we're going to use System Events to do this
tell application "System Events"
   -- loop through the list
   repeat with aFile in filenameList
       if aFile contains tab then
           -- work out the current file name
           set codified_name to (sourceFolder & (text item 1 of aFile)) # no need to coerce, as sourcefolder is a string the result is a string
           -- get the real name of the file
           set real_name to text item 2 of aFile
           -- and rename it
           set name of file codified_name to real_name
       end if
   end repeat
end tell

Any help would be awesome :smiley:

There is no error handling or anything here, in case a file name in the spreadsheet doesn’t actually have a matching file name.

But it seems to work fine as long as your data lines up.

set imageFolder to choose folder with prompt "Please select your folder of images" without multiple selections allowed

set theSpreadsheet to choose file with prompt "Please select your spreadsheet file" without multiple selections allowed


tell application "Microsoft Excel"
	open theSpreadsheet
	tell the active sheet of the active workbook
		-- returns a list of lists, top level rows, second level columns. In this case, each item is a list with item #1 being the original filename and item #2 being the new file name
		set spreadsheetData to the value of the used range
	end tell
	close the active workbook
end tell

tell application "Finder"
	tell folder imageFolder
		repeat with aNamePair in spreadsheetData
			set the name of file (item 1 of aNamePair) to (item 2 of aNamePair)
		end repeat
	end tell
end tell

Thats exactly what I needed thanks t.spoon :smiley:

Hi,

I do work with this script, but it does not work at all for me.

I do have a list of 14.000 images in total. But I have a small list of 6500 images which have to be renamed. The 6500 images are part of the big list.

The Script stops with an error if some jpg files are missing.

Is there a way to make the script skip the files missing and go on with the next file in list?

Best,
Christoph

Wrap the repeat loop above with try block:


try
	repeat with aNamePair in spreadsheetData
		set the name of file (item 1 of aNamePair) to (item 2 of aNamePair)
	end repeat
end try

By the way, theSpreadsheet in the script above indeed is one workbook, not one spreadsheet, so I would name it theWorkbook. As here you select single folder and single workbook, without multiple selections allowed can be omitted, as it is default in the Standard Addition’s choose file and choose folder dialogs.

And, here is a script with multiple folders selection allowed, with multiple workbooks selection allowed, with multiple spreadsheets in each workbook allowed:


set imageFolders to choose folder with prompt "Please select your image folders" with multiple selections allowed
set theWorkbooks to choose file with prompt "Please select your workbook files" with multiple selections allowed
set spreadsheetData to {}

tell application "Microsoft Excel"
	repeat with theWorkbook in theWorkbooks
		open theWorbook
		set theSpreadsheets to sheets of theWorbook
		repeat with theSpreadSheet in theSpreadsheets
			tell theSpreadSheet of theWorbook
				-- Each item is a list with item #1 the original filename and item #2 the new filename
				set spreadsheetData to spreadsheetData & (value of the used range)
			end tell
		end repeat
		close theWorbook
	end repeat
end tell

tell application "Finder"
	repeat with imageFolder in imageFolders
		tell folder imageFolder
			repeat with aNamePair in spreadsheetData
				try
					set the name of file (item 1 of aNamePair) to (item 2 of aNamePair)
				end try
			end repeat
		end tell
	end repeat
end tell

Hi KniazidisR.

Better still, wrap the try block in the repeat. That way the repeat will keep going even after an error due to a file not existing, which is what CWilcke wants.


repeat with aNamePair in spreadsheetData
	try
		set the name of file (item 1 of aNamePair) to (item 2 of aNamePair)
	end try
end repeat

Yes, you are right, Nigel. It was my mistake. I updated my last script too.