MS Batch, Rename files by pattern in Excel or CSV list

Question

I have documents with names like:

foo_baar_AB_01.ending
fOo_BaAr_BC_05.ending
FOo_baaR_BA_15.ending

And a Excel or CSV List with a ruleset to Rename the Files:

AB ; Data
BC ; Stuff
BA ; Other

My Task is to rename the Files. The Result should look like:

foo_baar_AB_01.Data.ending
fOo_BaAr_BC_05.Stuff.ending
FOo_baaR_BA_15.Other.ending

Is there a Solution to Integrate the Exel-Pattern-List in the batch file or do I have to integrate the Pattern-List in the batchfile? And what would be a Solution for this Problem?

Could you please confirm the list format; a CSV should have lines like this, `"AB","Data"`. Information such as the format of this file is crucial if you are not looking for an excel/VBA solution. — Compo, Sep 28 '16 at 10:41

score 1 · Accepted Answer · answered Sep 28 '16 at 15:24

@echo off
setlocal EnableDelayedExpansion

rem Load the list of names from the ruleset
for /F "tokens=1,2 delims=; " %%a in (list.csv) do (
   set "name[%%a]=%%b"
)

rem Process the files
for /F "tokens=1-5 delims=_." %%a in ('dir /B /A-D *.ending') do (
   ECHO ren "%%a_%%b_%%c_%%d.%%e" "%%a_%%b_%%c_%%d.!name[%%c]!.%%e"
)

After confirmed that the names are correct, remove the ECHO part from the ren command.

score 1 · Answer 2 · answered Sep 28 '16 at 21:33

Nested for loops can do the trick here -- see the explanatory rem remarks:

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define rule-set CSV file here:
set "RULES=%~dp0ruleset.csv"

rem // Resolve the files to rename, provided as command line arguments:
for %%F in (%*) do (
    rem // Extract the third `_`-delimited token from file name:
    for /F "tokens=3 delims=_" %%N in ("%%~nF") do (
        rem // Read the rule-set CSV file (`delims=;<TAB><SPACE>`!):
        for /F "usebackq tokens=1,* delims=;     " %%I in ("%RULES%") do (
            rem // Check whether third token from file name matches rule:
            if /I "%%I"=="%%N" (
                rem // File name matches, so rename file finally:
                ECHO rename "%%~F" "%%~nF.%%J%%~xF"
            )
        )
    )
)

endlocal
exit /B

This is for sure not the most performant and efficient method, but it is quite simple to understand.

After having tested the output, remove the upper-case ECHO command in front of rename.

score 0 · Answer 3 · answered Sep 28 '16 at 09:51

Here is an excel macro doing what you want. In your excel file put the rules in a sheet named rules like this:

COLUMN A COOLUMN B 
    AB     Data
    BC     Stuff

Change your files path in macro line Set objFolder = objFSO.GetFolder("C:\\files"). And run the macro. Hope it helps.

    Sub renameFiles()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    Set rulesSheet = ActiveWorkbook.Worksheets("rules")
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder("C:\\files")
    'loops through each file in the directory
    For Each objFile In objFolder.Files
        Filename = objFile.Path
        ' loop ruleset
        rulesrow = 1
        Do While (rulesSheet.Cells(rulesrow, 1) <> "")
            rule = Trim(rulesSheet.Cells(rulesrow, 1))
            newtext = Trim(rulesSheet.Cells(rulesrow, 2))
            pos = InStr(Filename, rule)
            If pos > 0 Then ' if the rule exists in your file name
                newfilename = objFSO.getparentfoldername(Filename) & "\" & objFSO.GetBaseName(Filename) & "." & newtext & "." & objFSO.getextensionname(Filename)
                ' rename
                Name Filename As newfilename
            End If
            rulesrow = rulesrow + 1
        Loop
    Next objFile
    End Sub

MS Batch, Rename files by pattern in Excel or CSV list

3 Answers3