Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Loop in all folders and subfolders to rename a file, given a directory path.

Hello,

I have got a vbscript which renames the file in a folder the requirement is to loop in a folder and all its subfolders to see the files and rename them.
It works great on a file level but, need to loop in the folders and subfolders as well.
Please find the vbscript attched.
Option Explicit

RenameFiles "C:\test"

Function CanRename(AFileName)

  On Error Resume Next

  Dim Dummy

  Err.Clear
  Dummy = Right(FileNameWithoutExtension(AFileName), 10)
  Dummy = DateSerial(Mid(Dummy, 7, 4), Mid(Dummy, 4, 2), Mid(Dummy, 1, 2))
  CanRename = (Err.Number = 0)
  Err.Clear

End Function

Function FileExtension(AFileName)

  Dim Count
  Dim Result

  Result = ""
  Count = InStrRev(AFileName, ".")
  If Count > 0 Then
    Result = Mid(AFileName, Count, 1024)
  End If

  FileExtension = Result

End Function

Function FileNameWithoutExtension(AFileName)

  Dim Count
  Dim Result

  Result = AFileName
  Count = InStrRev(AFileName, ".")
  If Count > 0 Then
    Result = Mid(AFileName, 1, Count - 1)
  End If

  FileNameWithoutExtension = Result

End Function

Function RenameFileName(AFileName)

  Dim DATE_DELIMITER
  DATE_DELIMITER = "-"

  Dim LITERAL_DELIMITER
  LITERAL_DELIMITER = "_"

  Dim FileName
  Dim DateText

  FileName = FileNameWithoutExtension(AFileName)
  DateText = Right(FileName, 10)
  RenameFileName = _
    Mid(DateText, 7, 4) & DATE_DELIMITER &  Mid(DateText, 4, 2) & DATE_DELIMITER &  Mid(DateText, 1, 2) & LITERAL_DELIMITER & _
    Left(FileName, Len(FileName) - 10) & _
    FileExtension(AFileName)

End Function

Sub RenameFiles(AFolderName)

  Dim DELIMITER
  DELIMITER = "_"

  Dim File
  Dim FileSystemObject
  Dim NewFileName

  Set FileSystemObject = WScript.CreateObject("Scripting.FileSystemObject")
  For Each File in FileSystemObject.GetFolder(AFolderName).Files
    If CanRename(File.Name) Then
      File.Name = RenameFileName(File.Name)
    End If
  Next

  Set FileSystemObject = Nothing

End Sub

Open in new window

Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I think this bit needs changing
Set FileSystemObject = WScript.CreateObject("Scripting.FileSystemObject")
  For Each File in FileSystemObject.GetFolder(AFolderName).Files
    If CanRename(File.Name) Then
      File.Name = RenameFileName(File.Name)
    End If
  Next

Open in new window

Avatar of oBdA
oBdA

Again, just for reference (and, let's be honest, to mercilessly mock VBScript - only needed to add -Recurse to Get-ChildItem), a PowerShell version (distributed on three lines for readability, but could just be combined to a one-liner).
As before, in test mode, so it will not actually do anything (other than eating CPU cycles and accessing the file system).
Get-ChildItem -Path "C:\Temp\*.*" -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	Rename-Item -NewName {"$($Matches.Head)_$($Matches.yyyy)-$($Matches.MM)-$($Matches.dd)$($_.Extension)"} -WhatIf

Open in new window

Avatar of RIAS

ASKER

Thanks, any suggestion to change this bit :
Set FileSystemObject = WScript.CreateObject("Scripting.FileSystemObject")
  For Each File in FileSystemObject.GetFolder(AFolderName).Files
    If CanRename(File.Name) Then
      File.Name = RenameFileName(File.Name)
    End If
  Next

The organisation decided to have in vbscript so have to do it that way. 

Open in new window

Any specific reason to use a clearly outdated and outmatched scripting language? It's the parrot in the Monty Python sketch.
Is that a one-time operation? Then it really doesn't matter if you're doing it in VBScript, Batch, VBA, C#, PowerShell, or manually.
Avatar of RIAS

ASKER

Yes it is one time operation , just a temp solution.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
For what it's worth, Powershell really is the right tool for this, but understand there are folks that aren't anxious to move forward.  Heck, I still use BAT and VBS scripts myself, as well as transitioning to PS1.  There is a learning curve, and it takes an investment, but long term it pays back in increased productivity and quality in future projects.


»bp
Avatar of RIAS

ASKER

Hi BP,
It does not understand Subfolders
Avatar of RIAS

ASKER

Thanks Bill Prew, but its only one time. Will let them know.
So the powers that be, who don't seem to even know where to start, insist on you (who is clearly not an expert in VBScript) to do a one-time operation in a script language that is utterly clunky and hard to use? Alrighty then.
I don't see why it makes a difference for a one-time operation, especially if you have to ask for help anyway.
If you're not an expert in VBScript yet, then for heaven's sake, don't start to become one. VBScript is as outdated as Windows 95.
PowerShell is the script language of choice for Windows, and it's part of Windows OS since Windows 7.
Avatar of RIAS

ASKER

Thanks oBdA, But, at the moment need to get this resolved quickly.
It ran without error here and did the renames, just the way I posted it.


»bp
Thanks Bill Prew, but its only one time. Will let them know.
Okay, understand, and not trying argue, but my suggestion in the prior post to use a utility like Bulk Renamer probably would have been the best choice.  For example you could have changed from processing a single folder to all subfolders with just the check of a box, etc...


»bp
Avatar of RIAS

ASKER

Thanks BP, bt can't use external software...just need to loop in the subfolders   at the moment.
Need more info since the code posted seems to work here.

Are you getting an actual error?

How are you running the script?

Did you make any changes to it from what I posted?

If you are still having trouble after some debugging there, post your exact code here for me to review.


»bp
Avatar of RIAS

ASKER

Yes, actual error that subfolder is not defined
Avatar of RIAS

ASKER

Oh BP,
You know what your solution worked like charm, my bad as I didn't see it property and had a typo, apologies.
Avatar of RIAS

ASKER

Thanks a ton BP!
Welcome.


»bp
Avatar of RIAS

ASKER

testfoldersubfolder.docxBP,
A slight problem in the code:
It just renames one file in the folder and it looks like it is not looping through all files.

Thanks
Well, the existing logic seemed to look at all files.  I didn't look at any of the support logic though, there is likely a bug in the prior code that you originally posted...

 For Each File in AFolder.Files
    If CanRename(File.Name) Then
      File.Name = RenameFileName(File.Name)
    End If
  Next


»bp
Can you share file names that were not renamed?


»bp
Avatar of RIAS

ASKER

Option Explicit

Dim FileSystemObject
Set FileSystemObject = WScript.CreateObject("Scripting.FileSystemObject")

RenameFiles FileSystemObject.GetFolder("C:\test")


Function CanRename(AFileName)

  On Error Resume Next

  Dim Dummy

  Err.Clear
  Dummy = Right(FileNameWithoutExtension(AFileName), 10)
  Dummy = DateSerial(Mid(Dummy, 7, 4), Mid(Dummy, 4, 2), Mid(Dummy, 1, 2))
  CanRename = (Err.Number = 0)
  Err.Clear

End Function

Function FileExtension(AFileName)

  Dim Count
  Dim Result

  Result = ""
  Count = InStrRev(AFileName, ".")
  If Count > 0 Then
    Result = Mid(AFileName, Count, 1024)
  End If

  FileExtension = Result

End Function

Function FileNameWithoutExtension(AFileName)

  Dim Count
  Dim Result

  Result = AFileName
  Count = InStrRev(AFileName, ".")
  If Count > 0 Then
    Result = Mid(AFileName, 1, Count - 1)
  End If

  FileNameWithoutExtension = Result

End Function

Function RenameFileName(AFileName)

  Dim DATE_DELIMITER
  DATE_DELIMITER = "-"

  Dim LITERAL_DELIMITER
  LITERAL_DELIMITER = "_"

  Dim FileName
  Dim DateText

  FileName = FileNameWithoutExtension(AFileName)
  DateText = Right(FileName, 10)
  RenameFileName = _
    Mid(DateText, 7, 4) & DATE_DELIMITER &  Mid(DateText, 4, 2) & DATE_DELIMITER &  Mid(DateText, 1, 2) & LITERAL_DELIMITER & _
    Left(FileName, Len(FileName) - 10) & _
    FileExtension(AFileName)

End Function

Sub RenameFiles(AFolder)

  Dim DELIMITER
  DELIMITER = "_"

  Dim File
  Dim NewFileName
  Dim SubFolder

  For Each File in AFolder.Files
    If CanRename(File.Name) Then
      File.Name = RenameFileName(File.Name)
    End If
  Next

  For Each SubFolder In AFolder.Subfolders
    RenameFiles SubFolder
  Next

End Sub

Open in new window

Avatar of RIAS

ASKER

This is the existing code I have
Avatar of RIAS

ASKER

Please find the print screen testfoldersubfolder.docx.
Also the renamed file had format like : 2019-05-07filename  it should actually be 2019.05.07filename
Need . instead of -

Thanks
Seems to work okay in a small test here:

Fri 12/06/2019 12:31:25.99 b:\EE\EE29166314>tree /f /a
Folder PATH listing for volume bill
Volume serial number is DD35-7C06
B:.
|   EE29166314.vbs
|
\---Files
    |   abc.01.06.2019.txt
    |   abc.01.01.2019.txt
    |
    +---sub2
    |       abc.01.04.2019.txt
    |       abc.01.03.2019.txt
    |
    \---sub1
            abc.01.02.2019.txt
            abc.01.05.2019.txt


Fri 12/06/2019 12:31:29.26 b:\EE\EE29166314>cscript EE29166314.vbs
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.


Fri 12/06/2019 12:31:33.70 b:\EE\EE29166314>tree /f /a
Folder PATH listing for volume bill
Volume serial number is DD35-7C06
B:.
|   EE29166314.vbs
|
\---Files
    |   2019-06-01_abc..txt
    |   2019-01-01_abc..txt
    |
    +---sub2
    |       2019-03-01_abc..txt
    |       2019-04-01_abc..txt
    |
    \---sub1
            2019-05-01_abc..txt
            2019-02-01_abc..txt

Open in new window


»bp
"testpdf 07.05.2018.pdf" got renamed to "2018-05-07_testpdf .pdf" in a test here.

I know that's probably not exactly what you wanted, but it did process the file.  There seem to be several bugs in the code you carried over from the prior question.  Notice in my test the renamed file was "2019-05-01_abc..txt" for example, with ".." before the extension.  And in this PDF file case, the trailing spaces weren't trimmed off the renamed base file name.

These issues will each need to be worked, and any others.  I wouldn't have written it like the original expert did, so you need to carefully go through that code and look for bugs or things you need to adjust for your needs.


»bp
Avatar of RIAS

ASKER

Do you suggest any changes...as we can have a completely new start.
Avatar of RIAS

ASKER

Please find the screenshot of the error.
ErrorFilename.PNG
I'd probably rewrite the whole thing, differently.  But running out of time today / this week.

And there are more details that would be needed to get it right.  Like will there always be a real file type extension after the current date stamp (like TXT, DOC, PDF, etc), or could a valid file be named "XYZ_01.02.2019"?  Is the date format always MM.DD.YYYY, or for months and days less that 10 could there only be a single digit in the file name, like "XYZ_1.2.2019.pdf"?


»bp
Please find the screenshot of the error.
ErrorFilename.PNG
Yes, that's another issue that wasn't dealt with, what to do if the new renamed file name already exists in that folder, what should happen.

This is why my early replies to questions are often more questions, I prefer to work out the small details before jumping in to code when I can.  But often other folks post attempted solutions before I even get questions asked...


»bp
Avatar of RIAS

ASKER

BP, lets start it all over again.
Okay, Monday I'll work up a new approach, and do some testing here.  If you could answer those questions that would also be helpful.


»bp
Avatar of RIAS

ASKER

Great!!take your time. Thanks!!
Avatar of RIAS

ASKER

even powershell is fine.
Changed to match the target format yyyy.MM.ddFileName.ext, and still in test mode.
Just run it, and it will list which file it would rename how.
Get-ChildItem -Path "C:\Temp\*.*" -File -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	Rename-Item -NewName {"$($Matches.Head)$($Matches.yyyy).$($Matches.MM).$($Matches.dd)$($_.Extension)"} -WhatIf

Open in new window

Knowing oBdA's work like I do, I suspect the Powershell script proposed should handle all the know situations, I'd encourage you to give that a try.


»bp
Actually, I take that back, I ran a test here and it doesn't look like it does quite what you  wanted...

PS C:\Users\bprew> Get-ChildItem -Path "b:\EE\EE29166314\Files\*.*" -File -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	Rename-Item -NewName {"$($Matches.Head)$($Matches.yyyy).$($Matches.MM).$($Matches.dd)$($_.Extension)"} -WhatIf
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\sub2\abc.01.04.2019.txt Destination: B:\EE\EE29166314\Files\sub2\abc.2019.04.01.txt".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\sub2\abc.01.03.2019.txt Destination: B:\EE\EE29166314\Files\sub2\abc.2019.03.01.txt".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\sub1\abc.01.02.2019.txt Destination: B:\EE\EE29166314\Files\sub1\abc.2019.02.01.txt".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\sub1\abc.01.05.2019.txt Destination: B:\EE\EE29166314\Files\sub1\abc.2019.05.01.txt".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\testpdf 05.07.2018.pdf Destination: B:\EE\EE29166314\Files\testpdf 2018.07.05.pdf".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\abc.01.06.2019.txt Destination: B:\EE\EE29166314\Files\abc.2019.06.01.txt".
What if: Performing the operation "Rename File" on target "Item: B:\EE\EE29166314\Files\abc.01.01.2019.txt Destination: B:\EE\EE29166314\Files\abc.2019.01.01.txt".

Open in new window


»bp
This seems to be more like what you were looking for I think...

Get-ChildItem -Path "b:\EE\EE29166314\Files\*.*" -File -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	Rename-Item -NewName {"$($Matches.yyyy).$($Matches.MM).$($Matches.dd)_$($Matches.Head.Trim())$($_.Extension)"} -WhatIf

Open in new window


»bp
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What it does:
Get-ChildItem -Path "C:\Temp\*.*" -File -Recurse |
will get all children of "C:\Temp\*.*", -File makes it only get files (not directories), -Recurse makes it process subdirectories.
The "|" "pipes" the file objects found to the next command.
Where-Object {$_.BaseName -match '^(?<Head>.*?)\s*(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
will filter incoming objects; it will let only files with something matching "<two digits>.<two digits>.<four digits>" at the very end pass. $_ is the currently processed file object; its "BaseName" property contains the file name without its extension.
The -match operator processes Regular Expressions, this is probably the most scary part here.
^ - "anchors" the pattern to the beginning of the string.
(?<Name>expression) is a "named group", which makes the elements found easier to track.
.*? - The "." matches anything, "*" is a quantifier meaning any number of repetitions, and the "?" makes it non-greedy, that is, match as few as possible (otherwise the .* would match until the end of the string, including the date parts we want separately).
So (?<Head>.*?) matches anything up until the Date part begins, and the \s* eats any potential spaces between the name and the date.
(?<dd>\d\d) - a group named "dd" that matches exactly two digits "\d\d"
\. - a literal '.'
(?<MM>\d\d) - a group named "MM" that matches exactly two digits "\d\d"
\. - a literal '.'
(?<yyyy>\d{4}) - a group named "yyyy" that matches exactly four digits "\d{4}"; the {x} is a quantifier again.
The results of the match (if the file name matched) are automatically saved in the automatic variable "Matches", which is used in the next command.
If there's a match, the file object will be passed through to the next command, otherwise Where-Object will just drop it.
Rename-Item -NewName {"$($Matches.yyyy).$($Matches.MM).$($Matches.dd)$($Matches.Head)$($_.Extension)"} -WhatIf
will rename the incoming items. Rename-Item supports a ScriptBlock (enclosed in {}) for the NewName argument, where the original object that was passed via the pipeline is accessible in the $_ variable.
It's just one big string, because unlike VBScript, you don't need to mess with "+" to join literal strings and/or string variables.
The $Matches is a hash table that contains the results of the previous match in Where-Object, and you can access the groups found like properties.
The -WhatIf makes it pretend instead of actually processing the command.
Avatar of RIAS

ASKER

Thanks experts!
Please let me know which script works so can try it . .
The last PS1 script I posted worked in a test here.


»bp
Just run the one in https://www.experts-exchange.com/questions/29166314/Loop-in-all-folders-and-subfolders-to-rename-a-file-given-a-directory-path.html?anchorAnswerId=42991923#a42991923
As I said: it's in test mode and will not actually rename anything until you remove the -WhatIf at the end.
Avatar of RIAS

ASKER

Brilliant Experts!!! Thanks a ton. Will test it on Monday in office.
Thanks
Avatar of RIAS

ASKER

oBdA special thanks to you for guiding me...
You're welcome.
For your peace of mind (and, of course, to further drive home the point about the advantages of PS), here are two helper functions to get ...
1. a report about files not matching the old or new naming conventions, and
2. a report about files with the old naming convention where a file with the new name already exists.
Plus the renaming function again, so that you have it all in one place.
## Get a report about files not matching either the old or the new naming conventions
Get-ChildItem -Path "C:\Temp\*.*" -File -Recurse |
	Where-Object {($_.BaseName -notmatch '^(?<Head>.*?)\s*(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$') -and ($_.BaseName -notmatch '^(?<yyyy>\d{4})\.(?<MM>\d\d)\.(?<dd>\d\d)(?<Name>.*?)$')} |
	Select-Object -Property Name, Length, LastWriteTime, DirectoryName, FullName |
	Export-Csv -NoTypeInformation -Path C:\Temp\UnexpectedNames.csv

Open in new window

## Get a report about files matching the old conventions, and an already existing duplicate with the new name
Get-ChildItem -Path "C:\Temp\*.*" -File -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)\s*(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	ForEach-Object {
		$newName = "$($Matches.yyyy).$($Matches.MM).$($Matches.dd)$($Matches.Head)$($_.Extension)"
		If ($duplicate = Get-Item -Path "$($_.DirectoryName)\$($newName)" -ErrorAction SilentlyContinue) {
			$_ | Select-Object -Property Name, Length, LastWriteTime, @{n='Dupe_Name'; e={$duplicate.Name}}, @{n='Dupe_Length'; e={$duplicate.Length}}, @{n='Dupe_LastWriteTime'; e={$duplicate.LastWriteTime}}, DirectoryName
		}
	} |
	Export-Csv -NoTypeInformation -Path C:\Temp\DuplicateNames.csv

Open in new window

## Rename files with the old naming convention to the new format
Get-ChildItem -Path "C:\Temp\*.*" -File -Recurse |
	Where-Object {$_.BaseName -match '^(?<Head>.*?)\s*(?<dd>\d\d)\.(?<MM>\d\d)\.(?<yyyy>\d{4})$'} |
	Rename-Item -NewName {"$($Matches.yyyy).$($Matches.MM).$($Matches.dd)$($Matches.Head)$($_.Extension)"} -WhatIf

Open in new window

Avatar of RIAS

ASKER

Amazing!!! Will test everything on Monday and accept the answer!!!
Thanks a ton mate!
Avatar of RIAS

ASKER

Thanks a lot mate oBdA you rock!!! BP thanks to you  for the effort and helping me so promptly.
Welcome, glad the Powershell approach worked out for you.


»bp