Rename Files Based on an ID Number in an Excel File [Example]

MACRO:   Rename Files Based on Excel File [Example]

Use Case

  • Rename all files in a folder based on data you have in an Excel file
  • Use a keyword (ID) contained in the original file name to correlate with data in Excel to construct the new file name
  • For example:

  • Macro is designed to easily handle many different use cases without having to change any scripts or complex Actions
    • The first column in Excel is always the lookup column
    • If you need multiple columns, just add a new column and combine them. Then use that computed column for lookulp
    • The column to be used for renaming the files is specified by the column name you give it in Row 1.
    • Just set the Local__ExcelHeaderName KM variable to this column name.
  • Error Handling
    • If the ID or the Column Header is not found, the script throws and error and a friendly error msg is displayed.
  • Sample Data

As always, feel free to post your questions, comments, issues, and/or suggestions concerning this macro.

This macro was built in response to this request:
Renaming data files with alternate IDs from spreadsheet

Example Output


MACRO:   Rename Files Based on Excel File [Example]

==**UPDATED**==:  2019-03-14 15:02 GMT-5
  • Revised script to Find Cell whose entire contents match the key ID

<a class="attachment" href="/uploads/default/original/3X/d/4/d4f51d6e65b398f8534b0826df1498aeb82bf026.kmmacros">Rename Files Based on Excel File [Example].kmmacros</a> (28 KB)
**Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.**


### ReleaseNotes

Author.@JMichaelTX		KM Forum Topic 


* **Rename All Files in Folder Open in Finder Using Excel Data**


1. **KM 8.2+**
2. **macOS 10.11.6 (El Capitan)**

**NOTICE: This macro/script is just an _Example_**

* It has had very limited testing.
* You need to test further before using in a production environment.
* It does not have extensive error checking/handling.
* It may not be complete.  It is provided as an example to show you one approach to solving a problem.

**How To Use**

1. First, be sure you have setup the Macro following the instructions in "Macro Setup" section.
2. Open Excel file to the worksheet that contains the lookup data for renaming
   * Must have a Header Row with the Names of your Columns
   * The Lookup ID must be in Column A
   * The Returned Renaming Data must be in a Column between B-ZZ
3. Open the Folder in Finder that contains the files to be renamed.
   * This is known as the "Finder Insertion Folder"
4. Trigger this macro.
5. The Macro will then loop through each File in the Folder
    * Extract the Lookup ID as the first number in the File Name
    * Search Excel for that ID, and return the text in the Column specified by the ExcelHeaderName variable
    * Rename the file with that text using the orginal file extension


* **Carefully review the Release Notes and the Macro Actions**
  * Make sure you understand what the Macro will do.  
  * You are responsible for running the Macro, not me.  ??
1. Assign a Trigger to this maro..
2. Move this macro to a Macro Group that is only Active when you need this Macro.  Most likely this will be the Finder app.
3. ENABLE this Macro.
(all shown in the magenta color)
   * Set Local__ExcelHeaderName to Header Cell Value That is Used for Renaming
     * If you need to use more than one Excel Column for the renaming data, just add another Column where you combine the other Columns

TAGS: @Excel @Files @Finder @RegEx @Example


* Any Action in _magenta color_ is designed to be changed by end-user


* To facilitate the reading, customizing, and maintenance of this macro,
      key Actions are colored as follows:
* GREEN   -- Key Comments designed to highlight main sections of macro
* MAGENTA -- Actions designed to be customized by user
* YELLOW  -- Primary Actions (usually the main purpose of the macro)
* ORANGE  -- Actions that permanently destroy Variables or Clipboards,
OR IF/THEN and PAUSE Actions


* While I have given this limited testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
* If you have any doubts or questions:
  * **Ask first**
  * Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.



Posted an update earlier today to make the Excel Find in the AppleScript be more reliable. It now requires that the Find string match the entire cell contents..

1 Like

Excuse me:Must "ParticipantIDs.xlsx" be opened? Can I open it with Chrome?

Yes. This macro is designed specifically to use the data in an Excel file opened in Excel.

Is there a way to use this when the filename is exactly the text in the ID column?


Tried replacing the Regex action with a Split Path action. Seems to work, but stops on this scenario:

if I have in the Finder two files with a similar name, ie


the script stops.

Replaced action:

Edit: Sanitized the folder to get rid of similar names, and script worked great :+1:

34 posts were split to a new topic: Rename Files Using a Simple Microsoft Excel or Apple Numbers Spreadsheet or a Text Table in BBEdit