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

first Previous Page 3 of 3
  • by Tony T1,

    Tony T1 Tony T1 Feb 9, 2016 5:55 PM in response to munchi112
    Level 6 (9,232 points)
    Mac OS X
    Feb 9, 2016 5:55 PM in response to munchi112

    Not sure why it would stall.  The script looks like it should work.

    You might want to add a delay 2 statement within the repeat loop to pause the script 2 seconds to see if that helps.

  • by munchi112,

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

    Sorry Tony, i'm not good at scripting.  Where would you insert the delay 2 statement..  Thank you.

     

    -----

     

     

    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 Tony T1,

    Tony T1 Tony T1 Feb 9, 2016 7:45 PM in response to munchi112
    Level 6 (9,232 points)
    Mac OS X
    Feb 9, 2016 7:45 PM in response to munchi112

    After: end if and before: end repeat

     

         end if

    delay 2

    end repeat

     

    I'm not sure if this will solve the problem, but it's worth a try.

  • by FlagBand,

    FlagBand FlagBand Mar 6, 2016 6:49 PM in response to Tony T1
    Level 1 (0 points)
    Mar 6, 2016 6:49 PM in response to Tony T1

    Hey Tony,

     

    This is totally what I've been looking for, too. I often have to go through an entire library of files (3,000+ PDFs) and I copy drag 10-20 files to a folder where it's more easily accessed for that one event.

     

    The thing is though, I use Open Office. Can this script be extended to Open Office spreadsheets?

     

    Thanks

  • by Tony T1,

    Tony T1 Tony T1 Mar 6, 2016 7:27 PM in response to FlagBand
    Level 6 (9,232 points)
    Mac OS X
    Mar 6, 2016 7:27 PM in response to FlagBand

    FlagBand wrote:

     

    Hey Tony,

     

    The thing is though, I use Open Office. Can this script be extended to Open Office spreadsheets?

     

    Thanks

     

    I've never used Open Offfice.

    It may be as simple as changing:

         tell application "Microsoft Excel"

    to

         tell application "Open Office"


    If that does not work, then you would need to look and the ApplesScript Library (Open AppleScript and ⇧⌘L, then look for Open Office (you may need to to look add it), then look for comparable definitions.

  • by SGIII,

    SGIII SGIII Mar 6, 2016 7:41 PM in response to Tony T1
    Level 6 (10,637 points)
    Mac OS X
    Mar 6, 2016 7:41 PM in response to Tony T1

    To avoid scripting the various spreadsheet apps perhaps could use Hiroto;s idea where the user selects the cells, copies to clipboard, and the script then processes the clipboard, which for more than one column is tab-separated with a return, and for one column would be values separated by return.

     

    SG

  • by FlagBand,

    FlagBand FlagBand Mar 6, 2016 8:02 PM in response to SGIII
    Level 1 (0 points)
    Mar 6, 2016 8:02 PM in response to SGIII

    Hey guys,

     

    I'm a total novice with automator and scripting, I've started exploring today. So here's what I tried:

     

    I tried changing the name and finding Open Office, but the automator locks up at "tell active sheet" - same place if I change the application to Numbers.

     

    I realize I can totally use the Numbers app for this function. I tried to use SGIII's earlier script though it doesn't search the folders and copy it to where I'd like. How do I organize that?

  • by SGIII,

    SGIII SGIII Mar 7, 2016 12:02 PM in response to FlagBand
    Level 6 (10,637 points)
    Mac OS X
    Mar 7, 2016 12:02 PM in response to FlagBand

    I don't have Open Office to test, but Tony's nice AppleScript modified to handle the clipboard (rather than read file names directly from a spreadsheet) works here to copy files from a source directory to a destination directory.

     

    To use,

    1. copy-paste into Script Editor (in Applications > Utilities)
    2. select the cells containing the filenames and type command-c to copy to the clipboard.
    3. click the 'run' button

     

    SG

     

     

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

    set theDestination to quoted form of POSIX path of (choose folder with prompt "Select Destination Director")

     

    set theContent to the clipboard as «class utf8»

    set theList to the theContent's paragraphs

    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

  • by FlagBand,

    FlagBand FlagBand Mar 7, 2016 1:10 PM in response to SGIII
    Level 1 (0 points)
    Mar 7, 2016 1:10 PM in response to SGIII

    Hey SG,

     

    Alright, so I tried this and here's the result. In the background I still have what I selected and copied to the clipboard.

     

    Screen Shot 2016-03-07 at 4.10.52 PM.png

  • by SGIII,

    SGIII SGIII Mar 7, 2016 4:54 PM in response to FlagBand
    Level 6 (10,637 points)
    Mac OS X
    Mar 7, 2016 4:54 PM in response to FlagBand

    I'm pretty sure Tony's part of the script assumes the names in the spreadsheet are exactly the same as the file names in Finder.  None of your files have extensions?

     

    To test whether the script works, set up two folders.  Have 'Test Folder 1' contain some files named something like test file 1.txt test file 2.txt.  Enter test file 1.txt and test file 2.txt in a column in your spreadsheet. Have 'Test Folder 2' be empty.  Then run the script, navigating to 'Test Folder 1' at the first prompt and 'Test Folder 2' at the second.  Then run the script.  If all is well you will find test file 1.txt and test file 2.txt have been copied to 'Test Folder 2'.

     

    SG

  • by Hiroto,

    Hiroto Hiroto Mar 7, 2016 6:05 PM in response to FlagBand
    Level 5 (7,281 points)
    Mar 7, 2016 6:05 PM in response to FlagBand

    Hello

     

    It is because your name contains apostrophe that is to be escaped properly in shell script.

     

    Try something like the following AppleScript script which is a mere wrapper of bash script. Names are assumed to be given by TSV (tab separated values) text in the clipboard. Only the first field (column) is used. Line may be terminated by CRLF, CR or LF. Currently given name is foreward matched in file name. Verbose output of cp(1) and error messages if any are returned in the result pane/window of Script Editor.

     

     

    set src to (choose folder with prompt "Choose source folder")'s POSIX path
    set dst to (choose folder with prompt "Choose destination folder")'s POSIX path
    set args to ""
    repeat with a in {src, dst}
        set args to args & a's quoted form & space
    end repeat
    do shell script "/bin/bash -s <<'EOF' - " & args & "
    SRC=$1
    DST=$2
    export LC_CTYPE=UTF-8
    {
    while IFS=$'\\t' read -r name rest
    do
        [[ -z $name ]] && continue
        find \"$SRC\" -type f -iname \"$name\"\\* -print0 | xargs -0 -J% cp -v -pPR % \"$DST\"
    done < <(pbpaste | perl -CS -lne 'map {print quotemeta} split /\\r\\n|\\r|\\n/')
    } 2>&1
    exit 0
    EOF"
    

     

     

     

    Briefly tested under OS X 10.6.8 but no warranties of any kind. Please make sure you have complete backup of the original directories before running this sort of script.

     

    Good luck,

    H

  • by Hiroto,

    Hiroto Hiroto Mar 7, 2016 6:32 PM in response to Hiroto
    Level 5 (7,281 points)
    Mar 7, 2016 6:32 PM in response to Hiroto

    Here's a revised script with minor correction.

     

     

    set src to (choose folder with prompt "Choose source folder")'s POSIX path
    set dst to (choose folder with prompt "Choose destination folder")'s POSIX path
    set args to ""
    repeat with a in {src, dst}
        set args to args & a's quoted form & space
    end repeat
    do shell script "/bin/bash -s <<'EOF' - " & args & "
    SRC=$1
    DST=$2
    export LC_CTYPE=UTF-8
    {
    while IFS=$'\\t' read -r name rest
    do
        [[ -z $name ]] && continue
        find \"$SRC\" -type f -iname \"$name\"\\* -print0 | xargs -0 -J% cp -v -pPR % \"$DST\"
    done < <(pbpaste | perl -CS -ln0e 'map {print quotemeta} split /\\r\\n|\\r|\\n/')
    } 2>&1
    exit 0
    EOF"
    

     

     

     

    * Notes on correction.

     

    perl -CS -lne
    

     

     

    is replaced with

     

    perl -CS -ln0e
    

     

     

    although -lne should work here.

     

     

    Regards,

    H

  • by FlagBand,

    FlagBand FlagBand Mar 7, 2016 8:20 PM in response to Hiroto
    Level 1 (0 points)
    Mar 7, 2016 8:20 PM in response to Hiroto

    Hiroto,

     

    This is epic, works like a charm. Literally saves me hours and hours of time compiling files. The fact that it's whatever data is on the clipboard too, is brilliant.

     

    Thanks guys!

  • by NBFamieo,

    NBFamieo NBFamieo Aug 12, 2016 8:34 AM in response to Tony T1
    Level 1 (4 points)
    Aug 12, 2016 8:34 AM in response to Tony T1

    Hello Tony,

     

    Is there any possible solution for searching a whole directory for the files using the shell script, AND to also report on any files that are not found? I am always having to go back and double check if I missed any files in the copy, which slows down the batch-processing. I like the concept of generating the txt file you showed at the beginning of this forum question, but it was dropped as soon as we went to using the shell script to duplicate the files.

     

    Nick (NBFameio)

  • by Tony T1,

    Tony T1 Tony T1 Aug 13, 2016 6:25 AM in response to NBFamieo
    Level 6 (9,232 points)
    Mac OS X
    Aug 13, 2016 6:25 AM in response to NBFamieo

    I'm not sure which script in this thread you are referring to,

    however, to test if a file does not exist, use: !

     

         if [ ! -f /tmp/foo.txt ]; then

              echo "File not found!"

         fi

     

    If you are trying to move an exact file name (i.e foo.txt) and not a wildcard situation (i.e. foo.txt* to find foo.txt, foo.txt2, foo.txt3, etc), then instead of using find, use test:

     

    This example tests if foo.txt exists in the directory /tmp, and if it does, moves it to the Desktop.  If it does not exist, it writes /tmp/foo.txt to the file NotFound.txt

         if [ -f /tmp/foo.txt ]; then

              mv  /tmp/foo.txt ~/Desktop

         else

              "echo /tmp/foo.txt ">> $HOME/Desktop/NotFound.txt"

         fi

     

    If you need more info, you'll need to post the script that you are using.

first Previous Page 3 of 3