Batch renaming files from an Excel conversion Table

Hi!
First, I have to confess that I don’t know much about Applescript, so I don’t have even the beginning of a code to solve my problem. Nevertheless, I was told that Applescript could do the job:
I often have to do computer-assisted image analysis. This has to be done blindly, ie the investigator should not know the origin of the images and thus cannot influence (consciously or not) the result of the study. In order to do this, the names of the images are codified during the aquisition. The codified name and the ‘real name’ are kept in an Excel file until the end of the study. Now comes my question: is there any way to reaffect their real name to the files automatically with an Applescript that would read the content of the Excel table of conversion ? It is really tedious to do it by hand since I regularly have to convert the names of 300-500 files!!!

Tkx for your suggestions !

This is certainly possible using AppleScript.

While it is possible to get the data out of Excel, it’s actually much easier if you can just save the two columns into a tab-delimited file since AppleScript can read the file directly which saves having to get the cell values from Excel.

I’ll also make some assumptions here that all the files are in a specific folder, and that the text file containing the names is in the form <codified_name><real_name>:

property sourceFolder : "Path:to:folder:" -- adjust as appropriate

-- read in the list of names
set filenameList to read file "path:to:filenames.txt" using delimiter {return}

-- 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}

-- we're going to use the Finder to do this
tell application "Finder"
	-- loop through the list
	repeat with aFile in filenameList
		-- work out the current file name
		set codified_name to (sourceFolder & (text item 1 of aFile)) as 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 repeat
end tell

Many, many thanks Camelot. I tried to make it work but was unsucessful.
Here is the code that I used.

I just have changed the pathway to the image files (within a folder naled ‘toto’) and named 1, 2…8, 9, and the pathway to the text file containing the codified name (1 to 9) and the real name (100, 200…900)


property sourceFolder : "HD 160:Users:tony:Desktop:toto" -- adjust as appropriate 

-- read in the list of names 
set filenameList to read file "HD 160:Users:tony:Desktop:toto:conv.txt" using delimiter {return}

-- 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}

-- we're going to use the Finder to do this 
tell application "Finder"
	-- loop through the list 
	repeat with aFile in filenameList
		-- work out the current file name 
		set codified_name to (sourceFolder & (text item 1 of aFile)) as 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 repeat
end tell

When I run this script, I get the following error message (translated from French):
[ Finder has generated an error: Impossible to set the name of file “HD 160:Users:tony:Desktop:toto1” to “100” ]

As far as I understand, the name of the file to be changed is not correct since the pathway to the file is appended to its name ‘1’.

How can I correct it?

Once again, many thanks for your help. If it finally works, Camelot, I owe you a beer if you come to Paris!! OK, OK, even if it does not work :wink:

I found the small mistake and found even the way to fix it, and now it is working:

The error was in this line:


set codified_name to (sourceFolder & (text item 1 of aFile)) as string

It has to be changed into:


set codified_name to (sourceFolder & ":" & (text item 1 of aFile)) as string

Camelot, once again, many thanks! You cannot imagine how much time these few lines of script will save!!!

Regards!

PS: Don’t forget for the beer :slight_smile:

Ahh, if you look at my original script, I included the trailing colon in my sourceFolder property. In your script you omitted it, so you need to include it each time you build the file path.

Both ways are equally effective, as you can see.

Happy to help :slight_smile:

OK, OK, I’m guilty. But as you said, both solutions are working. Though, I was so proud to have found a solution. It seems that there was no problem…

Take care.

Hello, I am trying to achieve a similar goal. I ** think** I have replicated these steps correctly but I am getting this error:

Result:
error “Finder got an error: Can’t set file "Macintosh HD:testing:testdocs:codified_name" to "Macintosh HD:testing:testdocs:real_name".” number -10006 from file “Macintosh HD:testing:testdocs:codified_name”

any ideas? :frowning:

Hi,

I just did try this script and get an error message (shown bellow)

any idea why?

error “Finder got an error: Can’t set file "Users:fcengarle:Desktop:TestRename:1.6C-1A.mp4" to "test1.mp4
1.6C-2A.mp4".” number -10006 from file “Users:fcengarle:Desktop:TestRename:1.6C-1A.mp4”

Thanks
Frederico

Hello

When you work with HFS pathnames (the ones using colon as delimiter) you must use complete ones starting with the volume name.

Replace :
file “Users:fcengarle:Desktop:TestRename:1.6C-1A.mp4”
by
file “:Users:fcengarle:Desktop:TestRename:1.6C-1A.mp4”

Yvan KOENIG (VALLAURIS, France) lundi 29 septembre 2014 20:50:28

Thanks a lot Yvan,

so I did change the Path adding the volume name and it did work :slight_smile: Well almost. It seem to have only change the first clip of my list. After the first file was rename, I got this error

error “Finder got an error: Can’t set file "MacHD:Users:fcengarle:Desktop:TestRename:
1.6C-2A.mp4" to "test2.mp4".” number -10006 from file “MacHD:Users:fcengarle:Desktop:TestRename:
1.6C-2A.mp4”

Any idea why?

Thanks
Fred

Given what you posted, it seems that the original filename starts with a linefeed or a return.
Without seeing your text file I can’t give a guaranteed explanation.
I just assumes that in the text file, the couples of names are not separated by return characters but by couples return+linefeed.
In such cas, with your code, the first value of aFile would be :

oldFile1.mp4 newFile1.mp4
the second one would be :
oldFile2.mp4 newFile2.mp4
And for sure the Finder will not find a file whose name is oldFile2.mp4
.

As I am lazy, I never instruct AppleScript to use a specific line delimiter.
It’s fair enough to be able to deal by itself with returns, linefeeds or/and even couples return+linefeed.

Here this version did the job flawlessly.

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 & ":" # As you see, I play safety.
-- 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 the Finder to do this
tell application "Finder"
	-- 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

As many readers already know, I hate the Finder, so my own choice would be :

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

Yvan KOENIG (VALLAURIS, France) mardi 30 septembre 2014 17:15:57