Like a dummy, I recently accidentally deleted all my DuckTales episodes (photo of my collection so that the FBI doesn’t get on my case) from both my desktop and my portable backup drive (be careful when using robocopy with and without the /mir switch). Fortunately, I still had a backup on my computer at home back in the Bay Area, so I have been transferring these files back onto my desktop. Because I’ve been using IRC to send the files, one side effect is that spaces in the filename turn into underscores, like this:
I could go in and manually remove all of these underscores (on occasion I have, sometimes it’s just easier and quicker to do it that way), but with 75 files overall it’s worth it to create a system for quickly renaming the files.
Here’s a quick rundown of the tools used:
- Windows 7 Command Line
- Microsoft Excel
- Notepad++ (get it here)
Ultimately, we want an Excel spreadsheet that will generate the ren commands. It will have 3 columns: one for the source filename, one for the destination, and one for the command.
We start by generating a list of the files in the folder/directory. This can be done with the classic dir command, with the /b switch (/b for “bare”, resulting in filenames only; otherwise you’ll get dates, times, and filesizes). In Windows 7 (and Vista and 8, I believe), if you hold SHIFT and right-click the Explorer window, you can open up a command window that already has the proper path. In XP, you can download a PowerToy that does the same thing. Otherwise, you’ll have to navigate to the path using the CD command.
Once you’ve opened up a command window with the proper path, input this command:
dir /b *.mkv > list.txt
The command above lists all the MKVs in this folder in bare format, then redirects (the > sign) the output to a new file called list.txt (as opposed to the screen; if list.txt already exists, it will be overwritten). Without the *.MKV, dir will recursively list list.txt inside list.txt. You could manually remove it from the list, but I like to keep it clean so I use the wildcard.
Now we open up list.txt with Notepad++. You can use any text editor you like, maybe even Microsoft Word (though over the years I’ve found its spell-check and auto-correct features can sometimes cause issues), as long as it has a search/replace function. Before we do the search/replace, however, we want to copy this list of files into column A (our source filename column) of our spreadsheet. Use Ctrl-A to quickly select the entire list and then Ctrl-C/Ctrl-V it into Excel (obviously I like keyboard shortcuts, but you can copy/paste using whatever method you prefer).
Back in Notepad++, do a search/replace for the underscore, replacing it with a space. Once again, select-all and then copy/paste. Now you should have a list of original filenames in column A, and a list of desired filenames in column B.
Column C is where you will input the formula for creating the ren command. Here is the command that we want:
ren "DuckTales_#67_-_The_Duck_Who_Would_Be_King.mkv" "DuckTales #67 - The Duck Who Would Be King.mkv"
Notice how the filenames are inside quotation marks. When a filename has spaces, it’s best to use quotation marks to avoid ambiguity. I know that on occasion I haven’t used them and commands completed successfully, but there have also been times when they haven’t. So, quotation marks.
The formula for creating this command is:
="ren """&A1&""" """&B1&""""
In Excel, if you want to represent a quotation mark as text, you need to escape it using another quotation mark. That’s why there are 14 of them in the formula above. You have 6 for the formula itself, and 2×4=8 for the filenames (and if you look at the example above, you’ll see 4 quotation marks total in the command). The ampersand is used to concatenate text, so basically we’re concatenating a bunch of text to create the command.
Review the command to make sure it’s correct, and then copy/paste the formula to the rest of the column. In Excel, there’s a convenient way to do this. Select the cell with the formula, then double-click the little black square on the bottom right. I don’t know what the square is called, but it will autofill the rest of the column for you.
A convenient side-effect of the autofill is that the entire column will already be selected afterwards. Now you can quickly copy this text back to Notepad++. If you had left it open after the search/replace function previously, all you’d have to do is paste over, since the text would already be selected. Ctrl-S to save, then quit Notepad++.
Back in the Explorer window, you can now rename list.txt to list.bat*. Confirm that you want to change the file extension, then double-click or hit enter to run the commands. Wow! The files are now properly renamed!
*This assumes that your system is configured to display file extensions, which IMHO is something that should be enabled by default. To enable it, type ALT-T-O in any Explorer window, then uncheck Hide extensions for known file types under Advanced Settings in the View tab.
At this point you can delete list.bat or rename it back to list.txt for later use. I usually just delete it. I do save the Excel spreadsheet since I now have the formula in there. I suppose I could save it permanently since it’s a generic formula for renaming any two files, but it’s so easy to re-create it that once I’m done renaming all my DuckTales episodes, I’ll probably just delete it along with the temp folder I’m using to hold the episodes.
So, there you have it. Though it may seem like a long procedure (this is a pretty long post, and I tried to explain everything thoroughly), it’s actually quite a time-saver once you’ve set it up and get used to all the keyboard shortcuts. I’ll summarize the steps below:
- SHIFT-right-click to open command window
dir /b *.mkv > list.txt
- Open list.txt, copy/paste filenames to column A of new spreadsheet
- Search/replace list.txt, replace underscores with spaces
- Copy/paste new filenames to column B
- In column C, enter formula
="ren """&A1&""" """&B1&""""
- Autofill formula
- Copy column C back to list.txt
- Save and close list.txt, rename to list.bat
- Run list.bat
Not too bad!
I first used this method back at work, when we sometimes had to rename hundreds of files at a a time. There are probably utilities and/or creative ways of using wildcards to make this happen, but when I tinkered around and looked online, I couldn’t find anything that didn’t require buying something or learning something new and esoteric. These are the tools that I had and knew at the time so I made do. The cool thing is that most people already have them on their PCs, so anyone can do it.
Sometimes, you never know what you might come up with when the need arises. I hope you found this post useful!