Applescript locate and copy images from a spreadsheet list

You're right, exact matching strings is trivial but fuzzy matching (e.g. 'Chicken Sandwich <-> sandwich_with_chicken.png') is hard. PhD's have been written on how to do this, in much lower-level languages than AppleScript.

It might be possible to craft something mid-way - for example a list of keywords ('chicken', 'hot dog', etc.) and look for matches on that but it won't be perfect and will need human oversight. e.g. if you had a 'Turkey Burger', should that match the 'turkey' keyword or the 'burger' keyword? It's not easy for AppleScript to know this.

At the end of the day, the viability of this project (dream? 🙂 ) depends on the similarity between the menu items and the image filenames. If there are many similarities then you could use a script, and even if that only does 60% of the job for you, leaving the rest to be marked by hand, that might be enough.

Hi,

Try this script.

---------------------------

set inagesFolder to (choose folder with prompt "Choose folder of images")

set menuFolder to (choose folder with prompt "Choose the /Menu/ folder")

tell application "Microsoft Excel"

tellactive sheet

setlastIndextofirst row indexoflastcellof (getused range)

repeatwithifrom 2 tolastIndex

settFileNametomyfind_filenames(get valueofrange ("A" & i), inagesFolder)

if tFileName is not "" then -- image founded

setrtomyduplicateImage(tFileName, inagesFolder, menuFolder)

ifrisnot "" then -- no error on duplicate the image file

set value of range ("B" & i) to tFileName

else

set value of range ("B" & i) to ""

end if

end if

end repeat

endtell

endtell

onduplicateImage(tName, iFolder, mFolder)

try

tellapplication "Finder" toreturn (duplicatefiletNameofiFoldertomFolder) asstring

onerror

return ""

endtry

endduplicateImage

onfind_filenames(tVal, thefolder)

setexcludedWordsto {"Mini", "and"} -- words in Menu Item to exclude in the searching name

setotidtotext item delimiters

settext item delimitersto " "

settWordstotext itemsoftVal

settext item delimiterstootid

setthefoldertoquoted formofPOSIX pathofthefolder

repeatwithiintWords

ifcontentsofiis notinexcludedWordsthen

set tPaths to do shell script "cd " & thefolder & " && /usr/bin/find . -iregex '.*[_/]" & i & "[_.].*' "

if tPaths is not "" then

set L to paragraphs of tPaths

if (count L) = 1 then return text 3 thru -1 of tPaths -- one path founded

repeatwithtpathinL -- many path founded, loop each path

set isGood to false

repeatwithtwordintWords -- check each word of this Menu Item in tpath

if contents of tword is not in excludedWords then

if ("/" & tword & "_") is in tpath or ("/" & tword & ".") is in tpath or ("_" & tword & ".") is in tpath or ("_" & tword & "_") is in tpath then

set isGood to true

else

set isGood to false

exit repeat

end if

end if

end repeat

ifisGoodthenreturntext 3 thru -1 oftpath -- each word of this Menu Item is in name of tpath

end repeat

end if

end if

endrepeat

return ""

endfind_filenames

---------------------------

The command "/usr/bin/find" with iregex is able to find a name according to several conditions.

Example with the word Chicken :

-iregex '.*[_/]" & i & "[_.].*' search in the name : "/Chicken_" or "_Chicken_" or "/Chicken." or "_Chicken."

/ is the parent folder of the images

found images = "chicken_nuggets.png", "chicken_wrap.png", "sandwich_with_chicken.png"

if the cell value = "Chicken Sandwich" : the file name will be "sandwich_with_chicken.png"

because the script search a name with these words "Chicken" and "Sandwich".

I presume that the Excel file is opened, and the good sheet is selected.

I presume that the Menu Items are in the first column and the value begin at row 2

mrmakochan wrote:

I do have one question. When I have a menu item that is only one word ("Hamburger"), it doesn't find the image. Maybe its something with the filename function.

Thanks!

No problem here, I tested my script with "Hamburger" in a cell value, and the image "hamburger.png" in the folder of images.

Possible causes :

1- The file name has no extension.

2- The cell value contains a space (at the beginning or at the end).

3- The file name contains a space at the beginning

4- The file already exists in the destination folder, which causes an error, then the cell in column "B" will remain empty.

5- another error in the Finder or another thing I didn't think.

Test on new Excel sheet, put "Hamburger" in the cell "A2"

Click at the tab "Events log" in the window of the application "AppleScript Editor.

Run the script, verify the text in the tab "Events log"

Ok, I test with a cell value " Hamburger " (a space at begin and at end), no problem it found the file "hamburger.png". but if it found two or more files it return "" because of space

I modified the script :

1- to not search an empty string, waste of time and possible cause of error

2- to not search folder name, possible cause of error

3- to not search files in subfolder, possible cause of error

4- to not return invisible file (name wich begin with a dot) , waste of time.

----------------------------

set inagesFolder to (choose folder with prompt "Choose folder of images")

set menuFolder to (choose folder with prompt "Choose the /Menu/ folder")

tell application "Microsoft Excel"

tellactive sheet

setlastIndextofirst row indexoflastcellof (getused range)

repeatwithifrom 2 tolastIndex

settFileNametomyfind_filenames(getvalueofrange ("A" & i), inagesFolder)

if tFileName is not "" then -- image founded

setrtomyduplicateImage(tFileName, inagesFolder, menuFolder)

ifrisnot "" then -- no error on duplicate the found image

set value of range ("B" & i) to tFileName

else

set value of range ("B" & i) to ""

end if

else

set value of range ("B" & i) to ""

end if

end repeat

endtell

endtell

onduplicateImage(tName, iFolder, mFolder)

try

tellapplication "Finder" toreturn (duplicatefiletNameofiFoldertomFolder) asstring

onerrorerr

log err

log tName

return ""

endtry

endduplicateImage

onfind_filenames(tVal, thefolder)

--if cell begin with space or end with space , text items of tVal return empty string, i add empty string in the list excludedWords

setexcludedWordsto {"Mini", "and", ""} -- words in Menu Item to exclude in the searching name

setotidtotext item delimiters

settext item delimitersto " "

settWordstotext itemsoftVal

settext item delimiterstootid

setthefoldertoquoted formofPOSIX pathofthefolder

repeatwithiintWords

ifcontentsofiis notinexcludedWordsthen

set tPaths to do shell script "cd " & thefolder & " && /usr/bin/find . -type f -maxdepth 1 -iregex '.*[_/]" & i & "[_.].*' \\! -name '.*' "

if tPaths is not "" then

set L to paragraphs of tPaths

if (count L) = 1 then return text 3 thru -1 of tPaths -- one path founded

repeatwithtpathinL -- many path founded, loop each path

set isGood to false

repeatwithtwordintWords -- check each word of this Menu Item in tpath

if contents of tword is not in excludedWords then

if ("/" & tword & "_") is in tpath or ("/" & tword & ".") is in tpath or ("_" & tword & ".") is in tpath or ("_" & tword & "_") is in tpath then

set isGood to true

else

set isGood to false

exit repeat

end if

end if

end repeat

ifisGoodthenreturntext 3 thru -1 oftpath -- each word of this Menu Item is in name of tpath

end repeat

end if

end if

endrepeat

return ""

endfind_filenames

---------------------------

mrmakochan wrote:

But is there a way to make it choose the exact match if it exists?

For example, I have the following images:

cheese_fries.png

fries.png

sweet_potato_fries.png

If I have "Fries" in my menu, it returns "cheese_fries.png" instead of "fries.png". Most likely because it is searching alphabetically?

OK, this is something I hadn't thought.

Here is the modified handler find_filenames(..)

----------------------------

onfind_filenames(tVal, thefolder)

--if cell begin with space or end with space , text items of tVal return empty string, i add empty string in the list excludedWords

setexcludedWordsto {"Mini", "and", ""} -- words in Menu Item to exclude in the searching name

setotidtotext item delimiters

settext item delimitersto " "

settWordstotext itemsoftVal

settext item delimiterstootid

setthefoldertoquoted formofPOSIX pathofthefolder

setmyListOfWordsto {}

repeatwithiintWords -- put in the list all valid word

ifcontentsofiis notinexcludedWordsthensetendofmyListOfWordstocontentsofi

endrepeat

if (countmyListOfWords) = 1 then -- this menu item contains one valid word, search exact match

set i to quoted form of (tVal & ".")

-- search file name (that begin with the name of menu and a dot)

set tpath to do shell script "cd " & thefolder & " && /usr/bin/find . -type f -maxdepth 1 -iname " & i & "*"

iftpathisnot "" thenreturntext 3 thru -1 oftpath --return name of the exact match

end if

repeatwithiinmyListOfWords

set tPaths to do shell script "cd " & thefolder & " && /usr/bin/find . -type f -maxdepth 1 -iregex '.*[_/]" & i & "[_.].*' \\! -name '.*' "

if tPaths is not "" then

set L to paragraphs of tPaths

if (count L) = 1 then return text 3 thru -1 of tPaths -- one path founded

repeatwithtpathinL -- many path founded, loop each path

set isGood to false

repeatwithtwordinmyListOfWords -- check each word of this Menu Item in tpath

if ("/" & tword & "_") is in tpath or ("/" & tword & ".") is in tpath or ("_" & tword & ".") is in tpath or ("_" & tword & "_") is in tpath then

set isGood to true

else

set isGood to false

exit repeat

end if

end repeat

ifisGoodthenreturntext 3 thru -1 oftpath -- each word of this Menu Item is in name of tpath

end repeat

end if

endrepeat

return ""

endfind_filenames

----------------------------