Need help with Applescript

[KellyNee23]

Hi,

 

We have a large catalogue of product images and are frequently sent Excel spreadsheets listing which images are required to be gathered to be uploaded to our site. 

 

Our image files are located on different servers and have a 13 digit name with an _I1 followed by it, i.e. 9781520560331_I1. The images are usually jpegs or tif. 

 

I wanted to run a script on Auomator to look at the image name within the excel worksheet and look up the image by either using Finder or by me telling it exactly where to look and then copying the images into a folder setup on the desktop.

 

I have looked at some of the scripts that are on the support page, but I was unable to get them to work. 

 

Thank you for your help, 

Kelly Nee

[vitor]

You should probably change the title of the post. As far as I can tell, you do not need help with applescript, you need help with a task; the language does not seem to be important, here, and by referencing applescript in your title (which is not a particularly popular and/or widely liked/used language, many people would argue quite the contrary), you’re limiting your chances of someone actually coming across this and being able to help you. Does it need to use automator, applescript, and the finder, or does it only need to get the work done (serious question)?

Whatever the case, could you please post an example of the spreadsheets you need to use, so we can get a sense for its structure? Something in the csv format would be preferred, since it’s only text and it can be easily parsed.

[KellyNee23]

Hi,

 

I need to use automator and applescript. We need to be able to run this script over and over again with different excel sheets that will contain different image names. We have a collection of over 200,000 images. If you can suggest an easier way that would be appreciated as well. 

 

unit                                             isbn                           title

Kindergarten Theme Collection  9780763559601_I1  Party Hats

Kindergarten Theme Collection  9780763566036_I1  Bobbie & The Parade

Kindergarten Theme Collection  9780763559618_I1  The Parade

Kindergarten Theme Collection  9781419063824_I1  Parades

Edited January 13, 2014 by KellyNee23

[vitor]

If you can suggest an easier way that would be appreciated as well.

I can; bash, calling a few tools that already come with OS X (such as find, and cp), could accomplish this likely way faster and in just a few lines, no problem.

Unfortunately, if you really do need to use Applescript and Automator, I’ll have to pass on this — Applescript and Automator are needlessly verbose and complicated for a task like this. They may be the only choice for some things (controlling OS X GUI apps), but they’re a pain to write, maintain, and find references for, which is why I avoid it as much as I can. This is only my opinion, of course, but I’m definitely not alone on this.

If you change your mind, I’ll take a look at this again. In the meantime, maybe drop a line to Carlos-Sz — he has a lot of good workflows done with Applescript, so he might be able to help you.

[KellyNee23]

Well if you could help me out that would be great, if you don't mind. I'll ask Carlos to look at my issue as well, but I'm open to other methods. Thank you. 

[jdfwarrior]

Well if you could help me out that would be great, if you don't mind. I'll ask Carlos to look at my issue as well, but I'm open to other methods. Thank you. 

 

So, you use the ISBN number to reference a file. How are the files organized on the server? Are they all located in one large folder? Organized into some kind of structure? If they are all in one large folder that could be referenced, it would GREATLY simplify things and make it so much fast. I am however talking about alternate scripts similar to what Vitor was referencing. As he mentioned, AppleScript has its place, but I think there are better ways to accomplish this. The reason if would be so much easier if they are lumped into one folder is because, if they aren't.. it being a network resource, I doubt Spotlight indexes the location. That being said, Alfred isn't going to easily find them. Searching a directory structure would be a manual process in the script to incrementally dive deeper into the file structure searching for the file. This process does work but can be VERY slow deepening on the the depth of the file structure and network connection speed.

[KellyNee23]

Hi David, 

 

The files are mostly organized into one large folder with sub-folders for special images this is located on one of our servers. We can make one large folder with all of our images if it makes things easier, but if there is a way to lookup the images in multiple locations (folders) or servers that would be better for us. 

[rice.shawn]

Here's an untested bash script that assumes that you have one destination for the images and one place where all the images are being pulled from. If the images are in different places, then you'll need to make use of the find command. You can also just use xls or xlsx files if you drop in a script that will automatically convert them for you to csv files. These command line tools exist someone on the internet.

#!/bin/bash
file=/path/to/excel_converted_to_csv_file.csv
image_path=/path/to/image/folder/
destination=/path/to/directory/you/want/the/images/copied/
if [ ! -d "$image_path" ]; then
    echo "$image_path not found."
    exit 99
fi
if [ ! -d "$destination" ]; then
    echo "$destination not found."
    exit 99
fi
OLDIFS=$IFS
IFS=,   # the delimiter of your csv file, using a comma here
[ ! -f $file ] && { echo "$file file not found"; exit 99; }
while read unit isbn title
do
	cp "$image_path$isbn.* $destination"
done < $file
echo "Finished copying images."
IFS=$OLDIFS

Vítor and David could probably make this script much, much better.

[KellyNee23]

Hi Shawn,

 

I ran the script with some modifications as you suggested, but I could not get it to work. Can you take a look at it and tell what I'm doing wrong please? 

 

#!/bin/bash

file=/Users/admin/Desktop/ISBNS.xlsx/

image_path=/Users/admin/Desktop/Test_New/

destination=/Users/admin/Desktop/

if [ ! -d "$image_path" ]; then

    echo "$image_path not found."

    exit 90

fi

if [ ! -d "$destination" ]; then

    echo "$destination not found."

    exit 91

fi

OLDIFS=$IFS

IFS=   # unit, isbn, title, H1

[ ! -f $file ] && { echo "$file file not found"; exit 92; }

while read unit isbn 

do

cp "$image_path$isbn.* $destination"

done < $file

echo "Finished copying images."

IFS=$OLDIFS

 

It keeps telling me it failed (92). I changed the 99s to different numbers so that I could see where it was bugging out. Thank you for your help. It is very much appreciated. 

[rice.shawn]

Gid rid of the last "/" on file=/Users/admin/Desktop/ISBNS.xlsx/

 

The script is asking for a csv file, not an xlsx file. So, open the xlsx file and export it to "csv"; then use that file.

 

If you want to make the thing work automatically, then do this from the command line:

sudo easy_install xlsx2csv

After that, put this in the script:

xlsxfile=/Users/admin/Desktop/ISBNS.xlsx
file=/Users/admin/Desktop/ISBNS.csv
xlsx2csv $xslxfile $file

Just copy/paste that over

file=/Users/admin/Desktop/ISBNS.xlsx/

in the script. That might work.

 

For testing purposes, change

cp "$image_path$isbn.* $destination"

to

echo "$image_path$isbn.* $destination"

And then run that from the command line. Ideally, put only a couple of rows in the xlsx file so that you don't get a million output lines. Then, look at the output. Copy/paste one of the lines and see if it works. If it does, then change the "echo" back to "cp".

[KellyNee23]

Hi Shawn,

 

The Script did not have any errors when I ran it this time, but it did not do what I needed it to do. It didn't copy the images from the Test Images folder onto my desktop. Below is the script with the modifications you suggested. Can you please tell me what I'm doing wrong? Thank you so much for your help. It is greatly appreciated. 

 

#!/bin/bash

file=/Users/admin/Desktop/ISBN_Test.csv

image_path=/Users/admin/Desktop/Test_New/

destination=/Users/admin/Desktop/

if [ ! -d "$image_path" ]; then

    echo "$image_path not found."

    exit 90

fi

if [ ! -d "$destination" ]; then

    echo "$destination not found."

    exit 91

fi

OLDIFS=$IFS

IFS=   # unit, isbn, title, H1

[ ! -f $file ] && { echo "$file file not found"; exit 92; }

while read unit isbn 

do

cp "$image_path$isbn.* $destination"

done < $file

echo "Finished copying images."

IFS=$OLDIFS

[rice.shawn]

Copy/paste this into a file named script.sh. Then, from the command line, just execute "bash script.sh" and let me know what the output is.

#!/bin/bash
file=/Users/admin/Desktop/ISBN_Test.csv
image_path=/Users/admin/Desktop/Test_New/
destination=/Users/admin/Desktop/
if [ ! -d "$image_path" ]; then
    echo "$image_path not found."
    exit 90
fi
if [ ! -d "$destination" ]; then
    echo "$destination not found."
    exit 91
fi
OLDIFS=$IFS
IFS=   # unit, isbn, title, H1
[ ! -f $file ] && { echo "$file file not found"; exit 92; }
while read unit isbn 
do
echo "$image_path$isbn.* $destination"
done < $file
echo "Finished copying images."
IFS=$OLDIFS