Replace text in file A with info from file B

[mikekiwi]

I do have a file containing multiple lines with travel information; in this file destinations are used with their description. Another file I have contains the same destination-description and a destination code. I want to replace the destination-description in file A with the destination code which can be found in file B. Of course, I assume that the destination-descriptions in both files are equal...

I included a sample file...

Many thanks in advance for any suggestions on if this is possible and if so, how this can be done....

[Mofi]

That needs a macro or script. Here is a quickly developed macro solution. It is important that you have only the 2 files open and file B has the focus when you start the macro.

The macro property Continue if a Find with Replace not found or Continue if search string not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
SelectAll
Copy
NextWindow
Top
Paste
IfColNumGt 1
"
#
"
Else
"#
"
EndIf
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%?+|"
Copy
EndSelect
Key LEFT ARROW
StartSelect
Key END
Find "|^c"
Replace All "^s|"
DeleteLine
EndLoop
ClearClipboard
Clipboard 0

Same as above but now commented and indented for better understanding using UEM syntax.

Code: Select all

/*! Only the 2 files should be open when running this macro or the 2 files
    are the top most files in the history of last active files. Additionally
    file B must have the focus when starting the macro. !*/
    InsertMode
    ColumnModeOff
    HexOff
    UnixReOff
//  Copy whole content of file B into user clipboard 9.
    Clipboard 9
    SelectAll
    Copy
/*! Change the focus to the window of file A and paste at top of this file
    the content of file B. !*/
    NextWindow
    Top
    Paste
/*! Insert as separator between these 2 lists a new line starting with
    a character which surely does not exist at start of any other line
    in file A or B. !*/
    IfColNumGt 1
        "
        #
        "
    Else
        "#
        "
    EndIf
/*! Set the cursor back to top of the file (= top of file content from
    file B and run a loop until the cursor reaches the inserted line
    with the special character at start of the line. This previously
    inserted separator line must be deleted before exiting the loop. !*/
    Top
    Loop
        IfCharIs "#"
            DeleteLine
            ExitLoop
        EndIf
/*! Select everything from start of the line to first |. That is the part
    of the line from file B which should be searched for in file A. This
    string is copied to user clipboard 9. !*/
        Find RegExp "%?+|"
        Copy
        EndSelect
/*! Remove current selection and select the second part of the line which
    is the replace string. The cursor is not at end of the current line
    from file B. !*/
        Key LEFT ARROW
        StartSelect
        Key END
/*! Search now for the all strings in user clipboard 9 (first line part)
    and replace them with the selected string (second line part). !*/
        Find "|^c"
        Replace All "^s|"
/*! Delete that line from file B. The cursor is then automatically at
    start of the next line. So the number of lines decreases by 1 on
    every run of the loop. !*/
        DeleteLine
    EndLoop
//  Clear user clipboard 9 to free RAM and switch back to Windows clipboard.
    ClearClipboard
    Clipboard 0

[mikekiwi]

Thanks Mofi, that works great....but only on the testfiles I supplied...

Maybe it has something to do with this: I don't have the option "Continue if search string not found " to check in my version of UE (10.00c). If this is the reason, do I need to upgrade or can the macro be adjusted to make it work? It looks like the macro is in a sort of loop...

By-the-way, in the real files/situation over here, file A is a bit bigger with more columns (the destinations are in the 22nd column (instead or the 33f column in the example)). Does that make a difference?


Thanks for your help up till now!

[Mofi]

You have to activate macro property Continue if a Find with Replace not found. You can do this for an existing macro at Macro - Delete Macro/Modify Properties.

In which CSV column the destinations exist in file A does not matter as long as there is a | on both sides of the destination name. The format of file B is very important. If it does not look as you have uploaded, the macro will not work.

[mikekiwi]

Many thanks Mofi; I've played with it now for a few days and it works fine; must have overlooked something before or got 'scared' when the content of the entire file B was pasted into our original "file a" and the search began (it took quite some time to complete).

Do I understand it correct that if I change the separator in the following piece of code:

Code: Select all

Find "|^c"
Replace All "^s|"

I can make it work in other separated files as well? I think this will be a much used and hopefully multi-functional macro over here...

Thanks once again for your help!

[Mofi]

You have to change also the line

Find RegExp "%?+|"

The separator | is used also in your file B. Because for your files it was necessary to have the separator also in the search and replace string, the macro selects the destination description (first line part) with including | at the end and copies it to the clipboard, and selects | again with the destination code string (second line part) which is used as replace string.

The macro could be slightly modified to exclude the separator used in file B in the clipboard string (referenced with ^c) and the selected string (referenced with ^s) to be more flexible. The disadvantage is the slightly lower speed because of an additional cursor movement. Here is the macro with the red marked changes to exclude the separator in the search and replace strings.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
SelectAll
Copy
NextWindow
Top
Paste
IfColNumGt 1
"
#
"
Else
"#
"
EndIf
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%?+|"
StartSelect
Key LEFT ARROW
Copy
EndSelect
Key RIGHT ARROW
StartSelect
Key END
Find "|^c|"
Replace All "|^s|"
DeleteLine
EndLoop
ClearClipboard
Clipboard 0

[Jamos]

Hi,

I'm trying to do a replace, but ideally what I want is on three open files.
1) File 1 - a list of words which I want to replace
2) File 2 - the list of replacement words
3) File 3 - the open code file that is hundreds of lines long.

You see currently I have to search and replace manually possibly hundreds of words. I wondered if anyone has a script etc. that will do what I'm asking?

For example:

File 1
one
two
three

File 2
four
five
six

File 3 the code before
one two three

File 3 the code after
four five six

I know that's a simple example but it's the only way I can put what I'm after in a crystal clear format.

Thank you in advance.
Jim.

[Mofi]

Open the 3 files in the correct order. It is important for the following macro that file 1 is the left one in the file tab (document) order, file 2 is in the middle and file 3 is the right one. Make sure that file 1 has the focus when you start the macro.

The macro property Continue if a Find with Replace not found or Continue if search string not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
UnixReOff
TrimTrailingSpaces
Bottom
IfColNumGt 1
"
"
EndIf
Clipboard 9
SelectAll
Copy
NextDocument
TrimTrailingSpaces
Bottom
IfColNumGt 1
"
"
EndIf
Top
ColumnModeOn
ColumnInsert "|"
Paste
ColumnModeOff
Find RegExp " +|"
Replace All "|"
SelectAll
Copy
NextDocument
Top
Paste
"#
"
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%?+|"
StartSelect
Key LEFT ARROW
Copy
EndSelect
Key RIGHT ARROW
StartSelect
Key END
Find MatchCase MatchWord "^c"
Replace All "^s"
EndSelect
DeleteLine
EndLoop
ClearClipboard
Clipboard 0

Add UnixReOn or PerlReOn (v12+ of UE) at the end of the macro if you do not use UltraEdit style regular expressions by default - see search configuration. Macro command UnixReOff sets the regular expression option to UltraEdit style.

This macro merges the first content of file 1 with the words (or strings) to search for with the content in file 2. The result is that file 2 then is a CSV file with the pipe character as delimiter. It is important for the macro that character | does not exist in file 1 or file 2. Column 1 contains the search strings, column 2 the replace strings. It is also important that both files have the same number of lines.

Next the combined word list is copied into file 3 at top of this file and a new line is inserted with character # to mark the position of the end of the list. It is important that file 1 with the search strings does not contain a line which starts with #. The rest of the macro is nearly the same as in my previous posts in this thread. So I think I don't have to explain it again.

You might want to adjust the final find parameters MatchCase MatchWord depending on your words (search strings).

File 1 should be unmodified after the macro execution, except the last line of file 1 was not terminated or it contained trailing spaces.

File 2 is after macro execution an unsaved CSV file with the search and replace words (strings). Close that file without saving.

File 3 is also unsaved after macro execution and all the words from file 1 should be replaced by the words in file 2.

[Jamos]

It worked Mofi!
You my kind sir, are simply just awesome.
I bow to you!

Thanks again