Can automator gather files listed in an excel spreadsheet?

Thank you - I have created this in a new thread. Download files listed as URLs in a spreadsheet?

Attempting this script I received this error :

curl: (6) Could not resolve host: Link

Tony - This all looks like Magic - Thank you for your assistance.

Could you also help out someone (me) trying to develop a script that sources (100+) URLs listed in excel that link to PDFs online and then download these onto a local folder on the desktop using the cell to the right of the URL column to provide the filename?

One more wrinkle is that the website requires username and password authentication to view the files.

Thank you for your assistance.

-Danny

DMarx81 wrote:

Tony - This all looks like Magic - Thank you for your assistance.

Could you also help out someone (me) trying to develop a script that sources (100+) URLs listed in excel that link to PDFs online and then download these onto a local folder on the desktop using the cell to the right of the URL column to provide the filename?

One more wrinkle is that the website requires username and password authentication to view the files.

Thank you for your assistance.

-Danny

Might be better to post this as a new question.

But, my first attempt would be to see if the url can be d/l'd with curl.

In Terminal, try:

curl <url> -o ~/Desktop/test.pdf

I did this with a random pdf I found with google:

curl http://www.pdf995.com/samples/pdf.pdf -o ~/Desktop/test.pdf

Then we need to deal with the password. Take a look at: man curl There's options to deal with passwords. Googling curl password can also give some help on dealing with passwords (see; http://stackoverflow.com/questions/2594880/using-curl-with-a-username-and-passwo rd) (but you need to be careful in sending passwords in the clear).

Once you can d/l one pdf with curl, then it will be a simple matter to get the url's from an excel sheet.

This Applescript will read the current Excel Worksheet (with the URL in Col A and the name of PDF in Col B) and d/l to the Desktop.

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 ("A1:B" & rc) as list

repeat with theItem in theList

do shell script "curl " & item 1 of theItem & " -o $HOME/Desktop/" & item 2 of theItem

end repeat

end tell

end tell

This does not handle username and password authentication, but can easily be added to the curl command (but need to be careful in sending passwords in the clear)

Hi Tony T1

Thanks for taking the time to continue to respond to this thread and for the great script, works like a charm for me. I'm also wondering if it is possible to tweak it so that you can search for a list of files in Excel and return their individual file paths to either the same spreadsheet or a new spreadsheet instead of copying the file to a new location? The reason being I'd like to hook this spreadsheet containing names and image file paths to a data merge in InDeswouldnd woudl like to keep the images where they are on a server.

If all you need is a list of the files found, then a CSV file can be easily created (and the CSV can then be opened in Excel).

This searches for files listed in Col A, the files found will be in list.csv on the Desktop

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

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 ("A1:A" & rc) as list

repeat with theItem in theList

do shell script "find " & theDirectory & " -iname " & theItem & " >> ~/Desktop/list.csv"

end repeat

end tell

end tell

This will write the path of the file listed in excel Col A to to Col B.

(Script will fail if there are any blank cels between file names)

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

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 ("A1:A" & rc) as list

set theList2 to {}

repeat with theItem in theList

set i to do shell script "find " & theDirectory & " -iname " & theItem

copy i to end of theList2

end repeat

set counter to 0

repeat with theItem2 in theList2

set counter to counter + 1

set value of cell ("B" & (counter as string)) to theItem2

end repeat

end tell

end tell

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.

If the Excel file is:

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 todownloads 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) todesktop 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)

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:

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 todownloads 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) todesktopwith replacing

to the directory you want (like above)

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

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

Thanks a lot. 🙂

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