Search and replace with Batch

Posted on 2009-04-22
Last Modified: 2012-05-06
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?
Question by:purpleoak
    LVL 95

    Expert Comment

    by:Lee W, MVP
    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

    LVL 2

    Author Comment

    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.
    LVL 82

    Accepted Solution

    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)
    LVL 16

    Assisted Solution

    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
    LVL 2

    Author Comment

    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
    LVL 2

    Author Closing Comment

    Thanks for the quick response very very helpful
    LVL 16

    Expert Comment

    Great to be of assistance. Thank you for the feedback.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This Windows batch file is useful for organizing image files from a digital camera or other source, but can have many other uses.  It simply renames the file(s) to match their create date.  For example, if you took a picture today at 1:40pm and the …
    You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
    Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now