• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

Search and replace with Batch

I have a file which contains lines in the following format

"93"      "Mr"      "Expolink Euorpe Ltd"      "Expolink Europe Ltd"      "Unit 1 Greenways Business Park"      "Bellinger Close"      "Chippenham"      "Wiltshire"      "SN15 1BN"      "U"      "27FEB06"

what i want to do is do a batch for loop which compairs the first token 93, with another file that is in the same format. I currently open the files with notepad ++ and do a find and replace for the string "     " with : then run the following script

for /F "tokens=1 delims=:" %%a in (osc.txt) do (for /F "tokens=1 delims=:" %%t in (ms520test.txt) do if %%a EQU %%t echo %%a:%%t >>results.txt)

This gives me what i want. However the find and replace normally crashes the program as the file has around 200K lines in it sometimes more. I need to be able to do a find and replace batch script or a for loop where the delims can be delims="      "" i have tried to escape the speech marks with the following delims=^"      ^"" but that didnt work

Any Ideas?
2 Solutions
Lee W, MVPTechnology and Business Process AdvisorCommented:
I seem to be finding the questions that I now think vbscript would be better for lately - this being one of them.  Can you clarify what you are looking to replace?  Also, you might consider using Excel if the data if formatted as such.  Is the number of spaces always the same?  If so, then I would suggest something like the code below (save the code as a .vbs file and make sure you change the content of the ReadFromFile and SaveToFile so they accurately reflect the file you want to open and the new one you want to save.

Option Explicit
'Declare the File name variables
Dim ReadFromFile, SaveToFile
'Declare the file system object variables
Dim objFSO, objReadFile, ObjCreateFile, objWriteFile
'Declare the source data variable
Dim SourceData
'Set the name and path of the file we are reading from
'Set the name and path to the file we are writing to (Creating)
'Create and/or open the files to work with
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReadFile = objFSO.OpenTextFile(ReadFromFile)
Set objCreateFile = objFSO.CreateTextFile(SaveToFile)
Set objWriteFile = objFSO.OpenTextFile(SaveToFile, 2, True)
'Read every line of the file and append a comma to the end of every line.  
Do Until objReadFile.AtEndOfStream
        'Put the current line into the SourceData variable and remove any leading and trailing spaces,
		'Then replace the quotes-spaces-quotes portions.
        SourceData = Replace(Replace(Trim(objReadFile.ReadLine), chr(34) & string(6, " ") & chr(34), ":"), chr(34), "")
        'Write the modified source data 
        objWriteFile.WriteLine (SourceData)
'Close the Files
Set objFSO = Nothing

Open in new window

purpleoakAuthor Commented:
Hi Leew

thanks for the quick responce, what im looking to find is "      " the find includes the speech marks and the whitespace is a tab. hope this helps.
Maybe I'm not completely getting what you're doing, but it seems to me that you just need some tiny adjustments to your script, so that it will parse the input file directly.
Simply replace the ":" in "delims=:" in the script with a tab, and the script will break down the input line at the tab separators.
for /F "tokens=1 delims=<TAB>" %%a in (osc.txt) do (for /F "tokens=1 delims=<TAB>" %%t in (ms520test.txt) do if %%a EQU %%t echo %%a:%%t >>results.txt)
If you want to get rid of the quotes around the tokens in the result file, just put a tilde (~) inbetween the percent signs and the variable character when expanding it:
for /F "tokens=1 delims=<TAB>" %%a in (osc.txt) do (for /F "tokens=1 delims=<TAB>" %%t in (ms520test.txt) do if %%a EQU %%t echo %%~a:%%~t >>results.txt)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

This might be helpful to you. The following batch file will rewrite the contents of the OSC.TXT file to RESULTS2.TXT replacing tabs with a ':'.

@echo off
setlocal enabledelayedexpansion
del results2.txt

for /F "tokens=*" %%a in (osc.txt) do (
   set s=%%a
   set s=!s:"      "=":"!
   echo !s!>>results2.txt
purpleoakAuthor Commented:
thanks guys for all your help, i had to do a search and replace with t0T0's script then did the for loop as described by oBdA. 1.5 million records took a LONG LONG time to process but it did the job. thank you for your help
purpleoakAuthor Commented:
Thanks for the quick response very very helpful
Great to be of assistance. Thank you for the feedback.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now