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

Macro Library
,
1

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:

image
image1099×456 242 KB

  • 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

Demo-File-Rename-Using-Excel-KM-Macro-AN

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

#### DOWNLOAD:
<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 

**PURPOSE:**

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

**REQUIRES:**

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

**MACRO SETUP**

* **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.
.
* **REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:**
(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

USER SETTINGS:

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

ACTION COLOR CODES

* 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


**USE AT YOUR OWN RISK**

* 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.

---

![image|458x1642](upload://hSbKWoNafXERKwGKmTV5U35wsqY.png)
5 Likes
2

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
3

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

4

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

5

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

987654302.ext
987654303.ext
987654304.ext
...
987654321.ext

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

987654302.ext
987654302-a.ext

the script stops.

Replaced action:
image

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

6

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