Benches macOS

14

How do I rename a lot of files from an ex…

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Hello all!

I have 2k+ files in a folder named sequentially from 0001.wav to 24xx.wav.

I have their contents referenced in an .xls.

This is a library made for Windows when it didn't accept very long names, so you couldn't just name the file accordingly.

So what I want to do is to rename these files taking the name from my excel sheet and pasting it on the file. To do that one by one is really too much.... Can anyone think of an automated (or semi-automated) way of doing this?

Thanks!!!

14 replies

Sign in or join Beachball Help to reply.

  1. 0

    Community User

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    What with the *.wav files? WAV is a MS proprietary audio format....

    What do you use for your XLS sheets? MS Office 2004, MS Office 2008, iWork '08, or 'Work '09, or, some other program? It may be able to be done with some AppleScript, but the XLS program probably needs to be scriptable.

  2. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    If you save the spreadsheet as a CSV file and assuming the data is stored as something as simple as:

    000001.wav, my
    newtitle.wav

    then you can easily do this with a bit of unix scripting in the Terminal, such as this:

    for details in `cat my
    spreadsheetdetails.csv`

    do

    OLD_NAME=`echo $details | *** -f1 -d,`

    NEW_NAME=`echo $details | *** -f2 -d,`

    echo "Renaming $OLD_NAME to $NEW_NAME"

    mv $OLD_NAME $NEW_NAME

    done

    Obviously you'd need to change the CSV filename used and this also assumes that all the wav files are in the same directory. If you want to test what it does, just omit the 'mv $OLD_NAME $NEW_NAME' line - that's the line that actually does the renaming.

  3. 0

    Badunit

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    It was all created on a Windows machine, thus WAV files are completely understandable.

    I'm sure there's a way to do what you want but it will probably involve programming or scripting. Automator has actions that will let you pull text out of Excel documents and actions to rename files but

    I can't see how to put it all together to go through 2000+ Excel cells one-by-one and renaming each file in turn. At best, using automator you may be able to throw a few actions together that will let you select a cell and a file, one at a time, and it'll rename the selected file. Better than retyping all the text but still laborious.

    EDIT: Ahh, someone replied already -- a hazard of getting interrupted in the middle of replying and thereby taking too long to finish and hit "post reply".

    Message was edited by: Badunit

  4. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi iBod!

    Thanks for your reply!

    Unfortunately I'm note very verse on UNIX scripting at all so I really don't know how to work this script out in terminal. I'm more than willing to try it out though and learn a new skill... 😉

    So I have a couple of questions, if you don't mind:

    1)where in the script am I telling in wich directory the files are?

    2)will this script rename all the files automatically? I mean, when it finds 001.wav it will rename it to my
    newtitle.wav and when it finds 004.wav it will rename it to my
    newtitle4.wav? even if they are not sequential? even if not all the the files in the CSV are present in the directory, or viceversa?

    3)what do you mean by testing? how can I test the script without actually renaming the files?

    Of course I'm going to do a test with a backup copy of the files, just in case, but I feel like I need a little more understanding on theterminology and commands I'm using because I can't use the script as it is so I have to know what to change and what not to.

    Thanks again for your great reply!

    2)

  5. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    So I tried the script but Terminal said "no such file or directory"

    I think I'm not giving Terminal the correct directions on where to find such file....

    I don't know how to do it, though....

  6. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi again,

    So I have a couple of questions, if you don't mind:

    Of course not! 🙂

    To hopefully make things more understandable, here's the script again with some line numbers I can refer to:

    1 for details in `cat my
    spreadsheetdetails.csv`

    2 do

    3 OLD_NAME=`echo $details | *** -f1 -d,`

    4 NEW_NAME=`echo $details | *** -f2 -d,`

    5

    6 echo "Renaming $OLD_NAME to $NEW_NAME"

    7 mv $OLD_NAME $NEW_NAME

    8 done

    Firstly, some assumptions I've made.

    1. The excel spreadsheet has 2 columns. Column A is the current name of the wav file, column B is the new name you want the wav file to have.

    2. Both name in columns A and B have the .wav extension specified

    3. None of the names have a space character in them. Eg. 'A cool song.wav'

    4. The script is being run from the folder containing the files to be renamed.

    Now, back to your questions:

    1)where in the script am I telling in wich directory the files are?

    The first thing we'd need to do in the Terminal, is change the working directory to where the wav files are located. The simplest way to do this is type 'cd ' (i.e. the cd followed by the space character) and then drag and drop the folder containing the wav files onto the Terminal window. That will populate the path and you can then press return to change to that directory.

    Line #1 specifies the file name that details the old and new names. Let's assume you save the excel spreadsheet as a csv file and create the file on your Desktop as being called songs.csv. In this case, line #1 would become:

    for details in `cat ~/Desktop/songs.csv`

    (The ~ is a shortcut that means your home folder.)

    2)will this script rename all the files automatically? I mean, when it finds 001.wav it will rename it to my
    newtitle.wav and when it finds 004.wav it will rename it to my
    newtitle4.wav? even if they are not sequential? even if not all the the files in the CSV are present in the directory, or viceversa?

    The script currently does not do anything more complex than follow what is present in the spreadsheet. So for example, if you have the following data:

    <pre style="font-family:'Courier New';">

    Column A | Column B

    0001.wav | MyNewTitle.wav

    0002.wav | SomeOtherTitle.wav

    2304.wav | Test123.wav

    0003.wav | GreatSong.wav

    </pre>

    it will rename, 0001.wav to MyNewTitle.wav, 0002.wav to SomeOtherTitle.wav, 2034.wav to Test123.wav and so on. The names themselves are irrelevant.

    If that's not what you want, can you give me a few examples?

    3)what do you mean by testing? how can I test the script without actually renaming the files?

    The only command that actually 'does' anything is line #7. When copying the commands in the Terminal if you skip that one line, then when you run it, the echo statement on line #6 will just print to the screen what it would do, without actually doing anything.

    If you can post a sample of the spreadsheet that should help me confirm I'm not going to trash your files 😉

    If none of that made sense, just yell and I'll try to rephrase.

  7. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    iBod,

    This is absolutely GREAT! thanks for all your help.

    I tried the script againt with your new data and here's whats happenning:

    first of all let me show you a bit of my test csv (I'm doing it all with test files and test names so I can replicate later with the real files and correct names)

    so if I open my textoraro.csv file in TextEdit I see the following

    01-texto.txt,01barrote.txt

    02-texto.txt,02barrote.txt

    03-texto.txt,03barrote.txt

    04-texto.txt,04barrote.txt

    05-texto.txt,05barrote.txt

    06-texto.txt,06barrote.txt

    07-texto.txt,07barrote.txt

    and so on and so forth

    when I run your script in the correct subdirectory only one file gets changed, the first one.

    "01-texto.txt" gets changed to "01barrote.txt 02-texto.txt"

    Si it is basically working but apparently the script does not undestand that the name ends before "return" and keeps on reading until it reaches the next comma.

    But I think we are getting there!

    Let me include here that my new names DO have spaces and characters such as "dog, barking, loud, city background.wav" will this be a problem for the script?

    Thanks again!

  8. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    OK, it sounds like we need to make a few tweaks.

    Let me include here that my new names DO have spaces and characters such as "dog, barking, loud, city background.wav" will this be a problem for the script?

    Yes, that will affect how things run, and that's a good example as the commas in there will also make a difference. The first thing we need to change is the how the csv is saved from the spreadsheet. Instead of using commas to separate the values, we need to change it to something not used in any name. If none of the filenames include a semi-colon ( ; ) use that. I'm hoping Excel allows you to change the delimiter in the csv save options.

    So, the csv file should look something like this:

    0001.wav;dog, barking, loud, city background.wav

    0002.wav;thunder, lightning.wav

    0004.wav;something_else.wav

    ... etc.

    so if I open my textoraro.csv file in TextEdit I see the following

    01-texto.txt,01barrote.txt

    02-texto.txt,02barrote.txt

    ...

    Did you mean .wav instead of .txt? Just want to make sure we both mean the same thing there 🙂

    when I run your script in the correct subdirectory only one file gets changed, the first one.

    "01-texto.txt" gets changed to "01barrote.txt 02-texto.txt"

    That sounds like the encoding on the csv file is set to use Windows line returns. Can you check and make sure it is saved with UNIX line returns and as a plain text file? TextEdit defaults to Rich Text normally, so check the format being used under the Format menu - it should say 'Make Rich Text', i.e. it is currently in plain text mode.

    Finally, I need to change the script a little for the filenames with spaces in them:

    1 cat ~/Desktop/songs.csv | while read details

    2 do

    3 OLD_NAME=`echo $details | *** -d; -f1`

    4 NEW_NAME=`echo $details | *** -d; -f2`

    5 echo "Renaming $OLD_NAME to $NEW_NAME"

    6 mv "$OLD_NAME" "$NEW_NAME"

    7 done

    To test it, skip line #6 this time for it not do any real work (and obviously omit the line numbers I added in there 😉 )

    Let me know how it goes!

  9. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    You are definetly getting very close......

    but still no luck 😟

    I'm sorry to bother you so much with this, I hope I can repay yo sometime...

    Anyway first of all let me tell you that I'm using .txt files as test files for the real thing.

    so I made 32 small text files and named them sequentially

    01-texto.txt

    02-texto.txt

    and so on

    after that I made a new spreadsheet that read:

    ColumnA ColumnB

    01-texto.txt 01barrote.txt

    02-texto.txt 02barrote.txt

    and so on

    I first saved it as csv as you told me (I tried all the possible csvs in excel) but the script did not work. It just did not do anything, not even an error message, as before.

    I did check if it was plain text and all the csvs were plain text in TextEdit.

    So I saved it as .txt from excel, I made a find/replace in TextEdit to make the separation between fields a semicolon and modified the new script you sent to reflect the new name of my reference file "textoraro1.txt"

    When I ran the script I got the following error message:

    ***: stdin: Illegal byte sequence

    ***: stdin: Illegal byte sequence

    Renaming to

    mv: rename to : No such file or directory

    the 3rd and 4th lines of the error message were repeated 32 times (wich means that the scrpt actually went through the 32 lines in the reference text file, right?)

    when trying the script again without line 6 (mv....etc) I got the first 3 lines of the message and the the 3rd line repeating 32 times.

    I checked all my filenames just in case but the test files are actually very simply named so I can assure you that all the file names are in the spreadsheet.

    I hope you don't feel bored yet... just think of this as a mental excercise.... heheheheheh

    Again, Thank you so much, if there's anything you need that I can help you with.... you know, I promise I'll be the first in line!

    Cheers.

  10. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi iBod!

    I really don't mean to be a pain, I'm sorry if I am, but I feel that you got very close in your final answer... it would be great if you can give me some last 5 minutes of attention.

    If you can't it's fine, I'll do my best to sort it out.

    Thank you so much for all your help!!

    Fede

  11. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Oops, sorry Fede, I missed your last reply.

    Let me have a read, get some coffee and I'll get back to you.

  12. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Actually, it might be quicker if we could discuss things in 'real-time' 🙂

    It sounds like the file format/location your running the script from may be the problem. Do you have access to AIM? If so, send me a message at iBodG5 - I'll be logged on for a few hours. Cheers, Ben

  13. 0

    fhpetrone

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    NEVER deny someone his cofee...

    it's a baaad thing to do...

    😉

  14. 0

    iBod

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    OK! We managed to get things working off the boards 🙂

    It turned out the CSV file was encoded as UTF-16 which was causing problems with 'odd' characters appearing in the Terminal. After re-saving the file as UTF-8 (and not DOS format) the script finally worked.

    Good luck with renaming all the sound files, Fede! Don't forget to back them up just in case... 😉