JennyMc84

Q: Can automator gather files listed in an excel spreadsheet?

Hi there,

 

We have a large catalogue of product images and are frequently sent Excel spreadsheets listing which images are required to be gathered and burnt to disc for a customer, for example.

 

Most of our image files are in folders by range and labelled with the item number ie 06897 (always 5 digits), but will quite often be within the filenames ie "HR 06897 Description.jpg".

 

Is there a way that we can use Automater to look at the item number column in Excel, search for the code in a filename within our 'Images' folder and copy the matching files into a new folder on the desktop?

 

It would then be even better if there was a way to output a report which showed which item codes it couldn't find so we can do the search manually.

 

Automator is so clever, but I'm not a clever driver of it so hoping that someone can help! This would save days of time, even if it could cut out half the searching!

 

Thanks,

Jenny

iMac, OS X Mavericks (10.9)

Posted on Nov 5, 2013 9:21 AM

Close

Q: Can automator gather files listed in an excel spreadsheet?

  • All replies
  • Helpful answers

Previous Page 2 of 3 last Next
  • by munchi112,

    munchi112 munchi112 Sep 14, 2015 3:57 PM in response to munchi112
    Level 1 (0 points)
    Sep 14, 2015 3:57 PM in response to munchi112

    I just thought of this.

     

    A queries on a volume which contains the following: 

     

     

    Searches......                                                        Primary folder

    Then looks into a folder with the following:           Item + Part Number + (contains the word guide)

    Then looks into the sub-folder name:                   4_Final_Files                

    Identifies any PDF file in the folder

    Then copies any PDF file found into your desktop.


    If you can come up with a solutions, i'd glad you donate a few dollars to your paypal account.


     

  • by Tony T1,

    Tony T1 Tony T1 Sep 14, 2015 6:44 PM in response to munchi112
    Level 6 (9,232 points)
    Mac OS X
    Sep 14, 2015 6:44 PM in response to munchi112

    munchi112 wrote:

                  

    Although i have not tried your script, it is a gem...  I'd like to see if we can take it a few more steps .. i have a series of PDF files which i'd like to query from a server.

     

    I think that the script will do what you want.  Give it a try.

  • by munchi112,

    munchi112 munchi112 Sep 14, 2015 7:01 PM in response to Tony T1
    Level 1 (0 points)
    Sep 14, 2015 7:01 PM in response to Tony T1

    Hi Tony,

     

    I thought it was not working, then i checked the install folder, and behold, the files i was searching started to d/l into the folder.  I did some creative naming with the wildcard, and it did help me get most of what i want.  Is it possible to limit the search to a specific folder... all the final files are stored in a #4 folder...  If targeting is permitted, it would allow me to be less specific in the excel file.  Again, what a great pc of code

     

    Chris

  • by Tony T1,

    Tony T1 Tony T1 Sep 14, 2015 7:16 PM in response to munchi112
    Level 6 (9,232 points)
    Mac OS X
    Sep 14, 2015 7:16 PM in response to munchi112

    munchi112 wrote:

     

    Is it possible to limit the search to a specific folder... all the final files are stored in a #4 folder...

     

    Should be able to.

    The script as written searches theDirectory as follows:

      do shell script "find " & theDirectory & " -iname '" & theItem & "*' -exec cp {} " & theDestination& " \\;"

     

    If the #4 folder is always named 4_Final_Files then it can be added as the directory to search as follows:

      do shell script "find " & theDirectory & "4_Final_Files -iname '" & theItem & "*' -exec cp {} " & theDestination& " \\;"

  • by munchi112,

    munchi112 munchi112 Sep 14, 2015 7:32 PM in response to Tony T1
    Level 1 (0 points)
    Sep 14, 2015 7:32 PM in response to Tony T1

    Thank you for quick response.  Can't wait to give it a try tomorrow. I'll post back the results.

     

    Have a good evening.

     

    Regards

     

    Chih

  • by munchi112,

    munchi112 munchi112 Sep 16, 2015 9:40 AM in response to munchi112
    Level 1 (0 points)
    Sep 16, 2015 9:40 AM in response to munchi112

    Good Morning.  I tried the updated path and it gave me a message that the file did not exist.  I'm using 2 wildcard searches to find anything that contains .ai or .pdf for a folder name _In_Use.

     

    Do we need to modify the script so that it digs through all the folders until it finds a sub-folder name _In_Use, then digs in to find any subfolders.

     

    The script alone has achieve my objective of finding files that includes the word "guide" ,etc. 

     

    Here's my 2nd project..   My team works on jobs and part of their process is to check a job into the _In_Use foler.  Inside the _In_User contains the same folder structure which i listed above.

     

    I want to be able to run the script near the end of the day to see if anyone has not move the job out of "_In_Use" before they leave for the day.  Its basically our manual way of checking a job in and out as they work on it.  Call this the poor man's version of version control, checking in and out.  (If you also have any sugguestions, i'd be more than happy to compensate you for a solutions) for now, i just want to know what's left _In_Use at the end of the day.

     

    Thank you so much.

     

     

    Chih

     

     

    error.png

     

    excel file.

     

    test.xls

    1*.ai
    2*.pdf
  • by Tony T1,

    Tony T1 Tony T1 Sep 16, 2015 10:58 AM in response to munchi112
    Level 6 (9,232 points)
    Mac OS X
    Sep 16, 2015 10:58 AM in response to munchi112

    munchi112 wrote:

     

    Good Morning.  I tried the updated path and it gave me a message that the file did not exist. 

     

    The error is saying that the Folder does not exist.

    Do you have a Folder as named in the error message:

         1.png

  • by munchi112,

    munchi112 munchi112 Sep 16, 2015 11:34 AM in response to Tony T1
    Level 1 (0 points)
    Sep 16, 2015 11:34 AM in response to Tony T1

    I think the folder designation is causing it to look for a specific path instead of looking for any directories or sub-directories with the given name: "4_Final_Files"   Can you also see my note regarding the excel file.  I may not be using the correct syntax to search for file type.... also what would i put if i want to look for any files with copy within a folder...  (would an Astrix in excel work?)  *

    folder tree.png

  • by msm87,

    msm87 msm87 Sep 30, 2015 10:09 AM in response to Tony T1
    Level 1 (0 points)
    Sep 30, 2015 10:09 AM in response to Tony T1

    First of all, INCREDIBLE script, thank you Tony! I was wondering if you could help me with the following.

     

    On a daily basis, I start out with a folder that contains 150+ TIF files that are all named by product code, ie. 0400087670195.tif

    All these have to be sorted by vendor (parent folder) and product folders (subfolder) at the end of the day.

     

    For a long time, I've been using a script that created the folders for me off an existing excel sheet and after completing all items, I'd have to move them manually into each folder when complete. After seeing your script however, I'm trying to amend the script to move the already existing TIF files into the respective folders, but I'm having trouble.

     

    I have an excel sheet such as this:

     

    Screen Shot 2015-09-30 at 11.46.27 AM.png


    My current script creates a new folder structure with all values in the first row as parent folders, all second row items as subfolders, like this:


    Screen Shot 2015-09-30 at 11.57.47 AM.png


    I currently have to move all files into those manually, however how can I amend your script to "scan" for the product code in the subfolder, match it with the existing TIF file and move them into the folder automatically? Again, I already have all the files that should go into these subfolders. They have the same filename as the subfolder. Ie. Bob / 0400087670195 / 0400087670195_version1.tif  or Bob / 0400087670195 / 0400087670195_version2.tif

     

    Here's the old script I use:

     

    on run {input, parameters}

     

      set baseFolder to POSIX path of (choose folder)

      tell application "Microsoft Excel"

      set theData to value of used range of active sheet

      end tell

      repeat with anItem in theData

      set {parentFolder, subFolder} to items 1 thru 2 of anItem

      do shell script "/bin/mkdir -p " & quoted form of (baseFolder & parentFolder & "/" & subFolder)

      end repeat

     

      return input

    end run







    Thank you so much for your input!

  • by Tony T1,

    Tony T1 Tony T1 Sep 30, 2015 5:47 PM in response to msm87
    Level 6 (9,232 points)
    Mac OS X
    Sep 30, 2015 5:47 PM in response to msm87

    Are all the TIF files in one (the same) Folder (before you move them)?

    Do all the TIF files have "_version" after the product code?

  • by msm87,

    msm87 msm87 Oct 1, 2015 10:51 AM in response to Tony T1
    Level 1 (0 points)
    Oct 1, 2015 10:51 AM in response to Tony T1

    Yes, I'm starting out with one folder that houses all TIF files.

    Each product code (for the sake, let's say 123.tif) has a few images.

    For example, a typical folder will be something like

     

    123.tif

    123_Alternate1.tif

    123_Alternate2.tif

    456.tif

    456_Alternate1.tif

    456_Alternate2.tif

    456_inBLACK.tif

    456_inRED.tif

    789.tif

    789_Alternate1.tif

    etc.

  • by Tony T1,

    Tony T1 Tony T1 Oct 2, 2015 7:03 AM in response to msm87
    Level 6 (9,232 points)
    Mac OS X
    Oct 2, 2015 7:03 AM in response to msm87

    This bash script wrapped in an Automator workflow should move the files to the directories as you specified (the directories need to be already created and in the same folder as the files):

     

    The bash script is:

     

    for f in "$@"

    do

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

           s1="${f%.*}"

           s2="${s1%%_*}"

           s3="${s2##*/}"

           find "${f%/*}" -type d -iname "$s3" -exec mv -n "$f" {} \;

      fi

    done

     

    Note: s1 strips the ext from the filename, s2 strips everything after the first _ in the filename and s3 strips the path, so find will now search for a directory with the s3 name starting in the directory selected in Automator, and if found, will move the file if it does not already exist there with -exec mv -n

     

    The Automator Workflow is:

     

    Screen Shot 2015-10-02 at 9.52.02 AM.png

  • by msm87,

    msm87 msm87 Oct 2, 2015 7:34 AM in response to Tony T1
    Level 1 (0 points)
    Oct 2, 2015 7:34 AM in response to Tony T1

    This is amazing, works like a charm. Thank you SO much, I can't say how much I appreciate this!

  • by Tony T1,

    Tony T1 Tony T1 Oct 2, 2015 1:56 PM in response to msm87
    Level 6 (9,232 points)
    Mac OS X
    Oct 2, 2015 1:56 PM in response to msm87

    Glad it worked.

    (You could probably add your Applescript that creates the Folder Structure to the Automator Workflow as the 1st Action using the Run AppleScript Action, then save the Workflow as an App)

  • by munchi112,

    munchi112 munchi112 Feb 9, 2016 2:39 PM in response to Tony T1
    Level 1 (0 points)
    Feb 9, 2016 2:39 PM in response to Tony T1

    Hi Tony,

     

    Happy new years.  For some reason now, when i run the script and search across server volume, it would copy a few files then stall.  I thought maybe it was because there were a lot of files to go throught, but it didn't have any issues before.

     

    Here's your script and the source file which i'm using with wildcards. I'd appreciate it if you have any further insights as to why it would stall.

     

     

     

    set theDirectory to quoted form of POSIX path of (choose folder with prompt "Select Search Directory")

     

    set theDestination to quoted form of POSIX path of (choose folder with prompt "Select Directory to Copy Files")

     

    tell application "Microsoft Excel"

       

        tell active sheet

           

            tell used range

               

                set rc to count of rows

               

            end tell

           

            set theList to get value of range ("B1:B" & rc) as list

           

            repeat with theItem in theList

               

                if contents of theItem is not {""} then

                   

                    do shell script "find " & theDirectory & " -iname '" & theItem & "*' -exec cp {} " & theDestination & " \\;"

                   

                end if

               

            end repeat

           

        end tell

       

    end tell

     

     

     

    Source file example testguide3.xls

     

    64AA1305*.pdf
    65AA1311*.pdf
    66AB243BU*.pdf
    67AB243GR*.pdf
    68AB243RD*.pdf
    69AB243TL*.pdf
    70

    AB243YE*.pdf

     

Previous Page 2 of 3 last Next