Use Python to automate boring task 'Search and Copy files'


I have many files whose filenames are stored in a column in an Excel file. I want to search these files, which were stored locally in my computer, and then copy them to a designated folder. I’m using MacOS X (version 10.11.6).

The structure of the Excel file is as follow. structure-excel-file


Use Applescript (via Automator) or Python.

My choice: Use Python

Idea: write a python script that read input from a listFile.csv, search for all filenames in that csv file from a designated folder path, then copy all found files to a folder. Run the script by excecuting in terminal

> python3

Clone this gist in a folder and practice.

Other possibilities: Use AppleScript

ref: discussion in Apple Communities

In order to use AppleScript, I need to locate Applescript Editor in /Applications/Utilities (or search for it in Spotlight). Then I copy the script into Automator (as indicated in the screencap below)


Thoughts: I tried but did not successfully with these AppleScript, just listed here for references.

Method 1:

Search entire directory including subfolders

Note: the Excel file must be opened while script being executed

Clone this searchCopy1.scpt to test.

Method 2:

Search entire directory,subfolders included, for an exact match. Then output an error if the file does not exist (in a file named “error.txt” on the Desktop)

Note: the Excel file must be opened while script being executed

Clone this searchCopy2.scpt to test.

Method 3:

To avoid scripting the various spreadsheet apps, an idea comes up where the user selects the cells in spreadsheet apps, copies to clipboard, and then the script processes the data copied to clipboard instead of relying on data in spreadsheet apps.

Clone this searchCopy3.scpt to test.