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

Page 1 of 3 last Next
  • by Tony T1,

    Tony T1 Tony T1 Nov 5, 2013 1:16 PM in response to JennyMc84
    Level 6 (9,232 points)
    Mac OS X
    Nov 5, 2013 1:16 PM in response to JennyMc84

    You'll need to use Applescript

    See: Applescript in Excel to find a value and then look for another value in the same row

     

    I'm not clear exactly what you want to do.

    Can you post a screenshot of a sample excel file, and then describe what you need done with that sample.

  • by Tony T1,

    Tony T1 Tony T1 Nov 5, 2013 6:05 PM in response to JennyMc84
    Level 6 (9,232 points)
    Mac OS X
    Nov 5, 2013 6:05 PM in response to JennyMc84

    If the Excel file is:

     

         Screen Shot 2013-11-05 at 8.48.12 PM.png

     

    And the Folder with the files is:

         ~/Downloads

     

    Then this Applescript will copy the fiies listed in the Excel flle to the Desktop.

    If the file does not exist, it will be listed in "NotFound.txt" on the Desktop

     

    set theDirectory to path to downloads folder

    do shell script "echo Files Not Found: > $HOME/Desktop/NotFound.txt"

    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

                                  tell application "Finder"

                                            try

                                                      duplicate (theDirectory & theItem as string) to desktop with replacing

                                            on error

                                                      do shell script "echo " & theItem & ">> $HOME/Desktop/NotFound.txt"

                                            end try

                                  end tell

                        end repeat

              end tell

    end tell

     

     

     

    (To wrap in Automator, just copy to the Action Run Applescript)

  • by JennyMc84,

    JennyMc84 JennyMc84 Nov 6, 2013 2:30 AM in response to Tony T1
    Level 1 (0 points)
    Nov 6, 2013 2:30 AM in response to Tony T1

    Thanks Tony T1,

     

    As a Mac newbie I could do with a little guidance around where to put this script? I searched for 'Applescript Editor' and pasted the string above but it didn't work.

     

    I'm guessing that at some point I need to tell it which excel file to look at, and also which folder to look in for the images, or have I missed something?

     

    I could do with the script placing the found files into a folder, rather than loose as there could be hundreds - not sure if the script is already doing that.

     

    Thanks,

    Jenny

  • by Tony T1,

    Tony T1 Tony T1 Nov 6, 2013 5:02 AM in response to JennyMc84
    Level 6 (9,232 points)
    Mac OS X
    Nov 6, 2013 5:02 AM in response to JennyMc84

    The way the script is written, the excel file must be open with the file in question.

    You can find Applescript Editor in /Applications/Utilities (or command-space and search for it in Spotlight.

     

    You can also copy the script into Automator:

     

     

         Screen Shot 2013-11-06 at 7.46.27 AM.png

     

     

    JennyMc84 wrote:

     

    I'm guessing that at some point I need to tell it which excel file to look at, and also which folder to look in for the images, or have I missed something?

     

    The way the script is written, the excel file must be open with the file in question.

    The scriprt is written to look for the files in the Downloads folder.

    Change downloads folder:

         set theDirectory to path to downloads folder

    To change to Other in your Home Directory:

         set theDirectory to (path to home folder) & "Other"

    To change to Other in your Documents Folder:    

         set theDirectory to (path to documents folder) & "Other"

     

     

    I could do with the script placing the found files into a folder, rather than loose as there could be hundreds - not sure if the script is already doing that.

     

    The script is copying the files to the Desktop, change

         duplicate (theDirectory & theItem as string) to desktopwith replacing

    to the directory you want (like above)

     


  • by LuixSilveira,

    LuixSilveira LuixSilveira May 12, 2015 1:04 PM in response to Tony T1
    Level 1 (8 points)
    iPhone
    May 12, 2015 1:04 PM in response to Tony T1

    Screen Shot 2015-05-12 at 5.02.35 PM.png

    Hi, Tony T1.

     

    I know this is an old post. I've tried the same with Apple Numbers and got an error as you can see attached. Could you tell me what I did wrong?

     

    Thanks,

    Luiz

  • by SGIII,

    SGIII SGIII May 18, 2015 1:46 PM in response to LuixSilveira
    Level 6 (10,637 points)
    Mac OS X
    May 18, 2015 1:46 PM in response to LuixSilveira

    In Numbers you have to address document, sheet, table, then range, then cells, something like this if you want to get the values from a range in the currently selected table (one in which you have selected a cell):

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      tell range "B1:B2"

      set theList to the value of every cell

      end tell

      end tell

      end tell

    end tell

     

     

     

    Or, slightly less verbose:

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      set theList to range "B1:B2"'s cells's value

      end tell

      end tell

    end tell

     

     

    SG

  • by LuixSilveira,

    LuixSilveira LuixSilveira May 19, 2015 3:57 AM in response to SGIII
    Level 1 (8 points)
    iPhone
    May 19, 2015 3:57 AM in response to SGIII

    Thanks a lot.

  • by Ween65,

    Ween65 Ween65 Jul 30, 2015 9:30 AM in response to Tony T1
    Level 1 (0 points)
    Jul 30, 2015 9:30 AM in response to Tony T1

    Hi Tony

    I know this is an old post but this is a gem of a script. I know 0% of scripting but I know this script will help me. Is there a way to modify this script to search an entire directory including subfolders? Please keep in my the directory is an external USB drive. Also I see you gave instructions on how to change the directory for the resulted files but is there a way to put them into a folder within that same external drive?

     

    Thank You in advance!

     

    Ween65

  • by Tony T1,

    Tony T1 Tony T1 Jul 30, 2015 5:41 PM in response to Ween65
    Level 6 (9,232 points)
    Mac OS X
    Jul 30, 2015 5:41 PM in response to Ween65

    This should work.

     

    The excel file must be listed as:

     

    Screen Shot 2013-11-05 at 8.48.12 PM.png

     

    Note: If file does not exist, no report is made.

     

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

    set theDestination to 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

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

                end repeat

           end tell

    end tell

  • by Ween65,

    Ween65 Ween65 Jul 30, 2015 6:07 PM in response to Tony T1
    Level 1 (0 points)
    Jul 30, 2015 6:07 PM in response to Tony T1

    Will this search sub-folders? I got a lot of errors. It looks like it doesn't recognize the "Spaces" in my volume name and folder names.

  • by Tony T1,

    Tony T1 Tony T1 Jul 30, 2015 6:34 PM in response to Ween65
    Level 6 (9,232 points)
    Mac OS X
    Jul 30, 2015 6:34 PM in response to Ween65

    Yes, it searches sub-folders.

     

    This will fix spaces in the names (quoted form of):

     

    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

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

           end repeat

      end tell

    end tell

  • by Ween65,

    Ween65 Ween65 Jul 30, 2015 7:43 PM in response to Tony T1
    Level 1 (0 points)
    Jul 30, 2015 7:43 PM in response to Tony T1

    WOW That's Great! I am so sorry but I have one last request. I do have some images that have multiple files.

     

    i.e. 12345_LR.jpg

          12345_LR2.jpg

          12345_LR3.jpg

    But the data in the spread sheet will only be listed like this "12345_LR.jpg.

     

    Could I list my data like this "12345_

    and then your script can what I might call "Wild Card" the rest of the information?

  • by Tony T1,

    Tony T1 Tony T1 Jul 31, 2015 5:01 AM in response to Ween65
    Level 6 (9,232 points)
    Mac OS X
    Jul 31, 2015 5:01 AM in response to Ween65

    Sure, just need to add the * wildcard:

     

    change:

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

    to:

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


    But…if there is a blank cel, then all files will copied (as only the * wildcard will be in the find command), so lets test if the cel is blank with:

        if contents of theItem is not {""} then


    Here's the complete script:


    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


  • by munchi112,

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

    Hi Tony,

     

    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  know the following>

     

    1. I know the initial folder name... ex>  item_partnum_***  (asterix is wildcard character".)

       Inside the folder are a series of folder numbered from  1 to 4.  The PDF i'm looking for is inside the #4 folder.

     

    example

     

    I can compile a spreadsheet that contains the item number + part number, and i would be looking for a PDF file inside the #4 folder:

     

    example

     

    SERVER VOLUME/PATH/

     

    Item_Project Name_45

              Item_Part_name_ProductName_Guide

                                  1_Work_Ups

                                  2_Photo_Scans_Support

                                  3_Current_Files

                                  4_Final_Files  - inside this folder is a PDF that would contain the part number + the word guide. (i'd like to copy this file into the desktop. (ex. DD425_Guide_X_AC.pdf)


    I look forward to your input regarding querying files that are nested inside sub-folders on the server.

     

    Thank you

     

    Chris

Page 1 of 3 last Next