I have a few collections of PDF files that need to be put on microfilm for long-term preservation. For the microfilm to be useable, the documents would need to be in chronological order. That’s a bit of a problem because the files aren’t named in a way that they can be sorted by date. I do have a spreadsheet that has both filename and date info though. I googled for a way to batch rename files with Excel, and found a few options. Some involved downloading Excel plugins or VBS script, which I can’t do on my office computer. But the tutorial Rename Multiple Files using Excel in Windows gave me a command-line option.
The first step is to get a list of the filenames. Open a command prompt, cd to the desired directory, and type
dir /b > _list.txt
to create a text file (_list.txt) of all the filenames in the directory. Then you can open the text file and paste the names in an Excel column. I actually didn’t need to do this because I already had the inventory.
In Excel, I made a new column before the filename column. I wanted to use it to number everything sequentially. I put 101 in the first cell and used a formula to add 1 to it in the next cell, and copied that formula down the column. That gave me the numbers. Then I made a new column after the filename column, and used a CONCAT(A1,B1) formula to join the two cells creating the new filename. Easy enough so far.
Then I had to use Excel to make the rename command. That meant making another column after the new name column, and using this formula
=CONCAT(“ren “””,B1,””””,” “,””””,C1,””””)
There’s an explanation for why you need all those quote marks. I’m not quite sure I understand it, but I took their word for it.
What the formula does is compose a list of commands which rename each file. I copied all those new cells as one block, and pasted it in the terminal after the command prompt. It ran each command one by one and renamed all the files in a matter of seconds.
I’m not sure if I’ll need this again, but I’ve got it here just in case.
