Rename Files Based on Excel File [Example]

ms_excel
files_folders

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

  • 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]

~~~ VER: 1.2    2019-03-14 ~~~

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

DOWNLOAD:

Rename Files Based on Excel File [Example].kmmacros (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


Renaming data files with alternate IDs from spreadsheet
#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..