CODEWITHSUNDEEP
windowsbatch-filecmdbatch-rename
chris
chris

Reputation: 57

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

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?

Upvotes: -1

Views: 1167

Answers (3)

msaint
msaint

Reputation: 101

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   COLUMN 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

Upvotes: 0

aschipfl
aschipfl

Reputation: 34899

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.

Upvotes: 1

Aacini
Aacini

Reputation: 67186

@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.

Upvotes: 1

Related Questions