PDA

View Full Version : Search and replace certain strings in multiple text files



swaggerbox
11-28-2012, 05:57 AM
I have multiple text files (*.txt) located in "E:\Test". I need to replace the text strings that contain the following terms with their corresponding replacements (see below)

"EXAMPLE_AA" replace with "AA\EXAMPLE_AA"
"EXAMPLE_CC" replace with "CC\EXAMPLE_CC"
"EXAMPLE_EE" replace with "EE\EXAMPLE_EE"
"EXAMPLE_GG" replace with "GG\EXAMPLE_GG"
"EXAMPLE_HH" replace with "HH\EXAMPLE_HH"
"EXAMPLE_JJ" replace with "JJ\EXAMPLE_JJ"
"EXAMPLE_KK" replace with "KK\EXAMPLE_KK"
"EXAMPLE_RR" replace with "RR\EXAMPLE_RR"
"following materials in EXAMPLE_" replace with "following materials in UU\EXAMPLE_"

There may be multiple instances of these terms in a single text file. I have only limited VBA experience so I would appreciate any help that I can get to get this started.

I need to do this in Excel since I'm not very familiar with MS Word.

mohanvijay
11-29-2012, 12:24 AM
Use "TextStream" object From "Microsoft Scripting Runtime" library

Teeroy
11-29-2012, 01:41 AM
There are 2 basic ways you can use a TextStream to do this. How large are the text files (approx. no. of lines)?

Teeroy
11-30-2012, 03:24 AM
No answer? OK the code below will work on files of any length.

Even though this doesn't overwrite the input file I recommend always testing on copies of data first.

Sub ModifySpecificText()
Dim FSo As Object, objInputFolder As Object, objFile As Object
Dim inputFile As Object, outputFile As Object
Dim reg As Object, File_Location As String, contents As String

'set base file location
File_Location = "C:\Test\" '*********change to your location**********
'create objects
Set FSo = CreateObject("Scripting.FileSystemObject")
Set reg = CreateObject("VBScript.regexp")
'check for output location and create if necessary
If Not FSo.FolderExists(File_Location & "output") Then
FSo.CreateFolder (File_Location & "output") '*** output goes in folder under input directory ***
End If
'set folder object
Set objInputFolder = FSo.GetFolder(File_Location)
'Loop through file list and check for text files for input
For Each objFile In objInputFolder.Files
If FSo.GetExtensionName(objFile) = "txt" Then
Set inputFile = FSo.OpenTextFile(objFile)
Set outputFile = FSo.createtextfile(objInputFolder & "\output\" & FSo.GetFileName(objFile))
Do Until inputFile.AtEndOfStream
'run the regular expression replace to change the pattern
With reg
.Global = True
.IgnoreCase = False
.Pattern = "(EXAMPLE_)(\w\w)"
contents = .Replace(inputFile.readline, "$2" & "\" & "$1$2") & vbCrLf
End With
'write output
outputFile.write contents
Loop
End If
Next
End Sub

ps: I couldn't decide whether you were using "EXAMPLE_" literally or as a placeholder for various text. I've coded it as a literal since more information would be needed to identify and match a placeholder.