Subject:File-naming from Excel list - possible?

Posted by: Phil Sayer

Date:8/30/2007 4:03:59 PM

Bear with me - this is complicated!

I've recorded 800 brief audio clips, and they are currently numbered 1 to 800.

Each of them needs a name from a list that has been supplied to me as an Excel document. The names are related to, but not the same as, the files - for example the audio plays the word "Barnsley" but the filename will be "BNSLY."

This is where lateral thinking is needed... by opening Excel and SF in the same window, I could copy one filename at a time from the Excel doc, and use Save As in SoundForge to paste it in. (Remember - I have to rename each file - they currently are numbered sequentially.) Sounds laborious, though!

Is there a way to do this more easily? I'm wondering if there's a way, for example, that the audio clips could be incorporated into Excel, (since the filenames themselves are sequentially numbered in Excel) then renamed by a formula within Excel.

Or, the other way round - can you think of a way to re-name the files en masse by somehow importing the list of names into SF?

As ever, any help gratefully appreciated!

Subject:RE: File-naming from Excel list - possible?

Reply by: pivot

Date:8/30/2007 5:44:18 PM

... not the excel answer you're looking for... but have you looked at the various file renaming utilities on the web? I use one called pfrank (easy searchterm, gets multiple hits). If you are just a little into programming, this thing uses regular expressions to match and manipulate filenames. It has a very good helpfile that explains how to do that. So if there is any regular relationship between the original filename and your target filename (your example seems to suggest that?) you can probably program it, and then its a single mouseclick (though the programming usually takes me a number of iterations - more fun than manual labor though....)

Subject:RE: File-naming from Excel list - possible?

Reply by: pivot

Date:8/30/2007 5:57:40 PM

This sounds exactly like your problem...
http://www5.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_21114208.html

maybe you give it the "free trial" (I didn't go past the login page, so not sure how they do it, but sounds like programming again, this time visual basic scripts)

Subject:RE: File-naming from Excel list - possible?

Reply by: Phil Sayer

Date:9/6/2007 1:42:44 AM

Thanks for the suggestions, pivot - it was good of you to help me out. In the end, it was a copy and paste job, one file at a time - all 800 of them...

I really must try to learn some scripting skills - but I don't do jobs like this one often enough to justify it, really.

Subject:RE: File-naming from Excel list - possible?

Reply by: pivot

Date:9/16/2007 5:34:19 PM

Hi Phil,
reading about your ordeal somehow reactivated my rusty brain and I realized that there is a simple way to do this... it's so simple you will want to scream.... (sorry I didn't realize this earlier.... but maybe for next time).
Here it is: it's a script too, but its very easy to create from excel.
the idea is that Windows still understands DOS .bat files. And the syntax for renaming is simply
RENAME old_filename new_filename
so all you have to do is create a text file with one line like that for each of your files, change its name to .bat and run it.
here are the details assuming very little excel knowledge
- make sure your excel spreadsheet has two columns where the left column is all the old filenames and the right column is the matching new filenames
- it has to have the EXACT filenames, including the extensions (e.g. .wav)
- if your exel sheet doesn't have the extensions, then it'seasy to add them with the ampersand, say cell a1 has the filename without the extension, go to any cell and type
=a1&".wav ",
that assumes it's a wav file.
that cell will then have the name with the extension, and you can just copy it down to complete the list
I'd put a space behind the wav, before the end ", just in case (need that for the sytax)
- so you need a sheet that has only these two columns, nothing else (bes create a new sheet and copy paste-special-value these two columns)
- then, insert two columns before the left column, and insert one columnb between the two columbns with the names (this is to leave the spaces later in the bat file)
- in the first if the leftmost two empty columns you now copy "rename" into every single cell
- so now you have a spreadsheet like this
rename | blank | old_filename | blank | new_ filename
all 800lines of it
- almost done...
- now save that file with "save as" and use the "text space delimited" option, in my excel that saves it as a .prn file, say as "change.prn"
- find the file with explorer and change its name manually to "change.txt"
- now open it, (it probably will open in notepad) and see if everything is as you want (it should have 800 lines that all go
rename old.wav new.wav
just be sure there are spaces between the words)
- if you see that, close it (if not you can try editing it with search-replace-stuff in the editor or back in excel)
- go back to explorer, and change the extension to "change.bat" (you know, of course that it could be any name, just the extension .bat is important)
- now do the magic: copy this file into the directory with all your old files, look at the directory in explorer and doubleclick on the file
- voila! that's it.... you can see the filenames change to the new ones

wrting this up took about 5x longer than trying it out (ok, only with two files)

have fun screaming...

pivot

ok, gotta admit that this assumes that you also have a list of your old filenames, and not just the files themselves ... but if you didn't have that original name-list then some sort of algorithmic renaming would be the only option... more complicated scripting.. or manual, like you did