Rename multiple excel files

[sferns.89]

Hello,

I have a multiple excel reports (100+) that are I get everyday. The file are named in the following format "yyyy-mm-dd_som_tin_dom_userid_Here-report-ALL.xls"

I need to rename them to "Here-report-ALL.xls""

So basically I need all the files in my folder renamed with all the characters after the 5th underscore(_) from the left. I got the logic figured out but not the programming skills to do it! Can someone help we with with a bat file for this please?

PS: I know there probably must be a software for this but I would like to also understand how a bat file would do this.

Thanks,
Steve

[foxidrive]

Remove the echo keyword to make it perform the rename. ATM it echos the command to console.

You have made provision for any filename clashes, I presume.

Code: Select all

@echo off
for %%a in (*ALL.xls) do (
for /f "tokens=5,* delims=_" %%b in ("%%a") do echo ren "%%a" "%%c"
)

[sferns.89]

Thanks but I can't get it to work. I saved the below code in a text file as Rename.bat (removing the echo as you said.) Placed it in the folder with the file(s) to be renamed. Opened it but file does not rename. Am I doing something wrong?

Code:

@echo off
for %%a in (*ALL.xls) do (
for /f "tokens=5,* delims=_" %%b in ("%%a") do ren "%%a" "%%c"
)

[foxidrive]

Don't call the batch file the same name as a reserved keyword or command. It's something we all learn at some time.


Still, it should have renamed at least one file unless you omitted some details.

[sferns.89]

Renamed the file...

Still, Have been playing around with this for an hour and still cant get it to work.....Even created some new excel files with the format to test it but it does nothing. Can anyone else pls test it and see if it works for them?

[foxidrive]

Try it with the echo still in place and add pause as the last line. See if it echos the ren commands to the console screen.

If it doesn't then check the filespec. You said your files have "ALL.xls" at the end of the name and this is what the code expects.

[Aacini]

There is a small mistake in the code. The %%c should be %%g:

Code: Select all

@echo off
for %%a in (*ALL.xls) do (
for /f "tokens=5* delims=_" %%b in ("%%a") do echo ren "%%a" "%%g"
)

For command assign the first token to the letter given, and subsequent tokens to the following letters, so:

Code: Select all

"tokens=5* delims=_" %%b in ("yyyy-mm-dd_som_tin_dom_userid_Here-report-ALL.xls")

%%b = yyy-mm-dd
%%c = som
%%d = tin
%%e = dom
%%f = userid
%%g = Here-report-ALL.xls

[foxidrive]

There is a small mistake in the code. The %%c should be %%g:

Code: Select all

@echo off
for %%a in (*ALL.xls) do (
for /f "tokens=5* delims=_" %%b in ("%%a") do echo ren "%%a" "%%g"
)

For command assign the first token to the letter given, and subsequent tokens to the following letters, so:

Code: Select all

"tokens=5* delims=_" %%b in ("yyyy-mm-dd_som_tin_dom_userid_Here-report-ALL.xls")

%%b = yyy-mm-dd
%%c = som
%%d = tin
%%e = dom
%%f = userid
%%g = Here-report-ALL.xls

No, only the tokens assigned have a metavariable. %%c is correct. Try the code if you need proof.

[Aacini]

Opps! Yes, you are right! My mistake...

Note to sferns.89: my description would be right if the for command would be this:

Code: Select all

for /f "tokens=1-5* delims=_" %%b in ("%%a") do echo ren "%%a" "%%g"

[sferns.89]

If it doesn't then check the filespec. You said your files have "ALL.xls" at the end of the name and this is what the code expects.

Thanks Foxidrvie!! I got it to work. I didn't have files ending All.xls. I thought *ALL.xls would make it work on all xls files in the folder..... and then realized my example had all.xls at the end. Long time since I touched coding of any sorts. Changed it to *.xls Thanks again. This will save a lot of time. Thanks Aacini for you inputs as well