Renaming files based on file contents

macOS
1

Hello,

I have a folder of over 6,000 Excel files that I need renamed (perfect for automation, huh?). All of these files currently have random names. For example, one of them is named “000e8d53-86e0-4fb3-a3e8-7453c95b2601.xlsx.” I need to somehow get these files renamed and restructured.

I’d like to be able to rename them based on content INSIDE the excel file. All of the files are the same format, and include data like a date, product number, and customer name. Is there a way to write a script (or maybe use Hazel) to look inside the excel documents, grab the data, and then rename the file?

Thanks in advance! (Oh, and I LOVE the podcast :slight_smile:)

2

I know that you would be able to use Python (using openpyxl) or Perl (using Spreadsheet::Read) to do this. I don’t know the file format of Excel well enough, nor which languages you feel comfortable with, to be able to recommend a general solution.

I know you could also be able to do this with AppleScript, but my (ancient) experience tells me that might be a headache.

1 Like
3

Thanks @Iaaph

I ended up figuring it out! Thanks for pointing me to Openpyxl. Great resource!

4

How did you do it in the end? What was your approach? I am eager to learn.
Right now the first thing which comes to my mind is to use Hazels match patterns… that should work.

5

I actually ended up doing it on windows (though it pained me to do so).

There is a tool called WinAutomation. It is a lot like Keyboard Maestro, but has the ability to grab data out of excel documents. They have a nice free trial, so I was able to do it all without paying anything.

6

@RosemaryOrchard was interested on some automation for Windows. @jacob_harmon can you share more details on this? Like the workflow or script?

Thank,
Luis

7

@LuisMartinez,

Unfortunately, my trail of WinAutomation ended, but I will share some more details from what I remember.

  • For {Files} in folder (and I gave it a folder location)

  1. Open {File} in Excel

  2. Get data in (give a specific cell) and store in {Variable}

  3. Close Excel

  4. Clean up data in {Variable} using RegEx (change “/” to “-” so that renaming works, remove extra spaces, etc…)

  5. Rename {File} to {Variable}

8

thank you @jacob_harmon. need to play with this a little bit more.

9

Did not about WinAutomation, will check it out… Backs away slowly after finding price

10

Yeah… It’s not cheap, but their free trial gives you full access, and that was enough time for me to get the job done.

2 Likes