Can automator gather files listed in an excel spreadsheet?

This should work.

The excel file must be listed as:

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

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.

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

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?

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

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

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.

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.

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

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& " \\;"

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

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

excel file.

test.xls

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:

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?) *

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:

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:

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!