Question Is there a way to rename cds track filename in bulk from an Excel list or any other semi-automation / automation process?

[pwangdel]

Greetings:

I have about 43,000 audio files in trackxx.wav format. I have to manually replace trackxx filenames with their corresponding long song name and the artist (ex: track01.wav = 01. ABCD... - EFGH... .WAV, track02.wav = 02. IJKL... - MNOP... .WAV and so forth). It takes me forever to finish this project. I am thinking of OCR the CDs cover, put it in Excel and concatenate with artist but don't know how to replace these track filename in bulk from the Excel list. Any suggestion to somewhat automate, reduce the time or making this project less painful would be greatly appreciated.

 

[igor_kavinski]

If you can share a short google sheet with the sample format that you will follow in creating the records, I can create the command for you (if the pattern makes sense, it's doable).

 

[pwangdel]

Thank you so much for your offer to help.
As requested here is the link to the file name conversion project.

FYI:
- I try Concatenate multiple cells in Google sheet to no avail. So I cheated by creating them in Excel and copy their values to Google sheet.
- Example provided is for 9 tracks but some CDs / DVDs may have more than 16 tracks.

From:


To Options 1:


To Options 2:

 

[igor_kavinski]

That was really easy. I thought it was something complicated.

FROM sheet formula:

=A2&B2&C2

To Options 1 formula:

=A2&" "&B2&" "&C2&" "&D2&E2&F2

To Options 2 formula:

=A2&" "&B2&" "&C2&D2&E2

I have used the simple comparison to verify that both strings are matching:

=D2=E2



For concatenation, just use & between cell references.

If you want to use space or any other character as a separator that isn't pasted in a cell, just use something like this:

A1&" "&B1

With hyphen as separator, it would be A1&"-"&B1

 

[pwangdel]

Thank you for your quick response and Google sheet concatenate lesson.

The complicated part is how to bulk rename or convert them in Windows Explorer.

I wonder if there is a way to get the data from Excel / Google Sheet and rename the files in Windows Explorer (track01.wav...track10.wav) automatically instead of copying & pasting line by line.

From: To:

 

[igor_kavinski]

All cells in column A have this: ren
Cells in column B have the track01 02 etc. filenames.
Cells in column C have the full filenames.

In cell D1, you type this: =A1&" "&B1&" "&C1 and copy this formula down to the last filename.

Now copy the commands from D1 to D10 or whatever.

Paste them in a text file in the same folder where the track files are.

Rename the text file to batchren.cmd or anything else. But extension should be cmd. Make sure to uncheck the option "hide extensions of file types" in your Folder Options:



Select the batchren.cmd and press enter once to run it. Press F5 to refresh the folder and you should find all the names changed.

 

[pwangdel]

Thank you for the detail instruction.

I follow steps by steps and it doesn't seem to work.

I also try to include the extensions and still no go.

Not sure what I am missing.

By the way, my OS is Windows 10 Pro v21H2 build 19044.2251

 

[igor_kavinski]

My mistake. The filename with the song should be included in quotes.

Here is the revised formula: =A1&" "&B1&" "&CHAR(34)&C1&CHAR(34)

Make sure that the batchren.cmd contains the commands like this:



But with the revised formula, the first command will be ren track01.WAV "PHUONG BUON.WAV"

and so on. It will only work if you include the extensions.

 

[pwangdel]

I think the culprit is that the folder is read only. I remove it from the folder property; however once I hit apply and ok. Reopened the folder property and it went back to read only again.

I google and find below instructions; however, as you can see from the screen shot, there is no Security tab.

More digging and find out that Remove Security tab in gpedit were not configured. Follow the instruction set it to Disable, restart the PC and still no Security tab. Any idea?



-------------------------------------------------

1. Right-click the folder, then select Properties.
2. Go to Security tab, then click Advanced.
3. Click Change to the right of Owner.
4. Enter Users into the box, then click OK.
5. Enable the checkbox Replace owner on sub containers and objects, then click Apply.
6. If prompted that You do not have permissions to read, click Yes.
7. Completely close out of the Advanced Security Settings dialog.
8. Repeat step 1.
9. Go to Security tab, then click Edit.
10. Select Add.
11. Enter Users into the box and click OK.
12. Enable the Full Control checkbox, then click OK

-------------------------------------------------

 

[igor_kavinski]

Manually change the filename in that folder. If that works, then batchren.cmd with the revised Excel formula should also work.

 

[sdifox]

You didn't create a cue file when you ripped the CDs?

 

[pwangdel]

Manually change the filename in that folder. If that works, then batchren.cmd with the revised Excel formula should also work.

Never mind. The new formula works perfectly. I owe you one. Beers / Coffee / Jack Daniel.... let me know?

 

[pwangdel]

You didn't create a cue file when you ripped the CDs?

No, my bad, I wasn't aware that I have to create a cue file so I just ignore it. Anyway, the revised formula works wonderfully and thanks for your feedback.

 

[igor_kavinski]

Never mind. The new formula works perfectly. I owe you one. Beers / Coffee / Jack Daniel.... let me know?

Nah. You don't owe me anything. But feel free to give me more chances to refine my Excel skills

That char(34) was something new for me

 

[pwangdel]

Nah. You don't owe me anything. But feel free to give me more chances to refine my Excel skills

That char(34) was something new for me

Completely forget about famous DOS command and have a chance to learn new things.
So simple but so effective without needing any programming skills like me. It works like a charm / magic and most importantly shaves about 2/3 of my time with almost 0 typo.
Once again, can't ever thank you enough and hope that I can return this favor some day.

 

[igor_kavinski]

most importantly shaves about 2/3 of my time with almost 0 typo.

I'm actually a really lazy procrastinator. Do a lot of this stuff in my job. There's something addictive about the thrill of accomplishing something when the clock's ticking and you barely make it in the nick of time. A lot of times I curse my lazy ass for waiting till the last minute but then when all ends well and I pretend to be Tom Cruise in Office Impossible, I let out a sigh of relief and relish in my almost-failure-turned-success

If you ever want to feel that, play the game Uplink. You will know what I mean