Find & Replace in CMD line based on Filename

[east3rd]

Hoping someone here is clever enough to figure this one out. I have a large number of files (900+) that need a text string replaced based on the filename. This isn't a simple Find & Replace for a static value. The table below is an example of what I'm trying to do. As you can see, there is a very specific find & replace value I'm working with for each file. I've done something similar in Excel plus command line for filename changes but this one is above my skill level. As always, any help is appreciated. Thanks!

filename	Search Value	Replacement Value
file1.xml	Subscription/Movies & Series/ChannelName/Series/Drama	Subscription/Movies & Series/ChannelName/Series/Drama/1992: Berlusconi Rising/Season 1
file2.xml	Subscription/Movies & Series/ChannelName/Series/Drama	Subscription/Movies & Series/ChannelName/Series/Drama/1993: Berlusconi Rising/Season 1
file3.xml	Subscription/Movies & Series/ChannelName/Series/Educational	Subscription/Movies & Series/ChannelName/Series/Educational/A Cure for Fear/Season 1
file4.xml	Subscription/Movies & Series/ChannelName/Series/Comedy	Subscription/Movies & Series/ChannelName/Series/Comedy/aka Wyatt Cenac/Season 1

 

[yky]

I used to use Excel to construct batch files. I found Excel a good tool for doing so because of its find & replace and concatenate functions. I did a dir, redirected the output to a file, opened it with Excel, manipulated it, saved it as text with a ".bat" exension, and that's my batch file. However, this method is only good if a rule can be found to apply to find & replace and/or concatenate. In your case, I don't see a rule or rules. All four examples you gave is unique. I don't see how Excel can help.

I guess you are not working with Windows because colon ":" is no allowed in filename in Windows.

 

[John_w]

Here's a pure VBA solution which uses FileSystemObject to read and write the files. Test it on a folder containing only 4 or 5 of the .xml files. As a further precaution, the macro doesn't change the original file, but creates a new file with the prefix "Modified ". You can delete the & "Modified " in the code when you're happy that it works correctly.

Public Sub Find_and_Replace_In_Files_LB()

    Dim XMLfilesFolder As String
    Dim FSO As Object
    Dim inFile As Object, outFile As Object
    Dim fileData As String
    Dim r As Long

    XMLfilesFolder = "C:\XMLfiles\folder\"  'CHANGE THIS - FOLDER CONTAINING THE XML FILES
    
    If Right(XMLfilesFolder, 1) <> "\" Then XMLfilesFolder = XMLfilesFolder & "\"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    With ActiveSheet
    
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
        
            Set inFile = FSO.OpenTextFile(XMLfilesFolder & .Cells(r, "A").Value)
            fileData = inFile.ReadAll
            inFile.Close
            
            Set outFile = FSO.CreateTextFile(XMLfilesFolder & "Modified " & .Cells(r, "A").Value)
            fileData = Replace(fileData, .Cells(r, "B").Value, .Cells(r, "C").Value)
            outFile.Write fileData
            outFile.Close
    
        Next
        
    End With
        
End Sub

 

[east3rd]

@John_w Thanks! This works (almost) perfectly ; ) Seems I have some lines in my doc for files that I don't have so the script stops as soon as it hits a "file not found" scenario. Is there any way to skip over these "errors"?

 

[east3rd]

Scrap that - working on getting the correct filenames into my doc - seems easier ; )

@John_w Thanks! This works (almost) perfectly ; ) Seems I have some lines in my doc for files that I don't have so the script stops as soon as it hits a "file not found" scenario. Is there any way to skip over these "errors"?

 

[John_w]

@John_w Thanks! This works (almost) perfectly ; ) Seems I have some lines in my doc for files that I don't have so the script stops as soon as it hits a "file not found" scenario. Is there any way to skip over these "errors"?

In case you want to ignore missing files change the For ... Next loop to:

        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
        
            If FSO.FileExists(XMLfilesFolder & .Cells(r, "A").Value) Then
                Set inFile = FSO.OpenTextFile(XMLfilesFolder & .Cells(r, "A").Value)
                fileData = inFile.ReadAll
                inFile.Close
                
                Set outFile = FSO.CreateTextFile(XMLfilesFolder & "Modified " & .Cells(r, "A").Value)
                fileData = Replace(fileData, .Cells(r, "B").Value, .Cells(r, "C").Value)
                outFile.Write fileData
                outFile.Close
            End If
        
        Next