Resource Mass Search and Replace with Notepad++

Post-editors will probably know the feeling of having to replace the same terms over and over again (often names of cities or people). You can of course do search & replace for each individual term but that takes time and is not feasible if you want to do a mass search & replace.

My current solution is to use Notepad++ and regex, with thanks to this poster on Stackexchange.
I don't think it's the most elegant solution (so if anyone else has a more refined approach do tell). Still, it may be of use to someone out here so I'll just post it here.

Step 1 - terminology list
While not absolutely necessary, I find this overview quite handy (column 1 = terms to be replaced, column 2 = new terms).




Step 2 - convert into regex string
Use whatever you like to create a regex string that can be used by Notepad++. As for me, I wrote a trashy Python script that reads the Excel terminology list and outputs a regex string in the correct format.



Step 3 - paste regex string into Notepad++
Paste your MTL text into Notepad++. Open search & replace window and paste your two regex strings in it. Don't forget to enable regular expressions. That's basically it.



Python code for people who don't code

Code:

import pandas as pd
import openpyxl

import pandas as pd
file = 'tab_search_replace.xlsx'
data = pd.ExcelFile(file)
#print(data.sheet_names) #this returns the all the sheets in the excel file

df = data.parse('Sheet1')

ps = openpyxl.load_workbook('tab_search_replace.xlsx')
sheet = ps['Sheet1']

bad_words = []
good_words = []

for row in range(2, sheet.max_row + 1):

    bad_word = sheet['A' + str(row)].value
    good_word = sheet['B' + str(row)].value

    bad_words.append(bad_word)
    good_words.append(good_word)

find_string = ""
replace_string = ""

for bad_word in bad_words:
    find_string += '('
    find_string += bad_word
    find_string += ')'
    find_string += '|'

counter = 1
for good_word in good_words:
    replace_string += '('
    replace_string += '?'
    replace_string += str(counter)
    replace_string += good_word
    replace_string += ')'
    counter += 1

find_string = find_string[:-1]

print(find_string)
print()
print(replace_string)

 

Um. don't you know sublime text? the meaning and its very existence? you can download packages for it too..... and its much more simple.

Spoiler

I'm genuinely confused. does anyone not use sublime text or atom?

 

I was just about to comment about this one. Sublime Text is simply sublime when it comes to making this stuff less of a hassle.

It's quite easy on the memory of your system, too.

 

I wholeheartedly agree to that, couldn't have said it better myself.

sublime text is just simply that sublime.

 

If you know python why not just do the text replacement in python? Import the excel file into a list/array, import the text file you want modified, then replace. You can then package into an exe file to share if needed.

 

Try this python code (probably not the most cpu efficient method but code is short so whatever):

Code:

import pandas as pd
from tkinter import messagebox
from tkinter.filedialog import askopenfilename

#choose excel file dialog
messagebox.showinfo('Mass Search and Replace', 'Choose excel dictionary (.xlsx)')
dictionary = askopenfilename()

#choose text file dialog
messagebox.showinfo('Mass Search and Replace', 'Choose text file (.txt)')
txtfile = askopenfilename()

#read excel file
df = pd.read_excel(dictionary)

#get each column words
wrongWords = df['Bad'].values.tolist()
rightWords = df['Good'].values.tolist()

#read text file
with open(txtfile, 'r', encoding='utf8') as file :
  filedata = file.read()

#replace wrong words with right words
repeats = len(wrongWords)
for x in range(repeats):
   filedata = filedata.replace(wrongWords[x], rightWords[x])

#rewrite the file
with open(txtfile, 'w', encoding='utf8') as file:
  file.write(filedata)

I added tkinter for dialog boxes to make choosing files easier, but you can change it to normal file location input if you prefer that.

To people who don't have python:

1. Install python from python.org, remember to tick "add python to PATH"
   
2. Open command prompt
3. Type: pip install openpyxl pandas tk
4. Create MassSearchAndReplace.py using your favorite text editor (e.g. notepad)
   
5. Copy the code into MassSearchAndReplace.py
6. Double-click MassSearchAndReplace.py and follow onscreen instructions
7. Excel file should be laid out exactly like the original post, otherwise replace the column headings "Bad" and "Good" in the code with whatever you wrote.