Recently i was involved with a project that had me automate, recieving emails from a system, formatting them into excel files and redacting all names, from several columns in the file.
I just want to preface that I am NOT a programmer by trade, however was able to solve the problem with the following script i cobbled together. I am SURE there are a 1000 better ways to do this, but it got the job done for me.
The script i wrote did several different things to the file, however the piece of it i struggled with (briefly) was the redaction part.
I have seperated and generalized that piece and offer it up to anyone else who might have a use for it.
# This Script iterates through an excel file and redacts text strings from a list, in specifed excel files
import pandas as pd
# TODO Script Variables
excel_file = '' #Excel File to iterate through
sheet_name = 'Sheet1' #Name of Excel Worksheet
old_strings = [] #List of strings to search for and redact
new_string = '[Redacted]' #String to replace text with
column_list = [] # List of Excel files to search for old_string
# TODO Function to Execute String Replacement
def replace_string_in_excel_column(file_path, sheet_name, column_name, old_string, new_string):
try:
lower_string = old_string.lower()
df = pd.read_excel(file_path, sheet_name=sheet_name)
df[column_name] = df[column_name].astype(str).str.replace(old_string, new_string, regex=False)
df[column_name] = df[column_name].astype(str).str.replace(lower_string, new_string, regex=False)
df.to_excel(file_path, sheet_name=sheet_name, index=False)
except FileNotFoundError:
print(f"Error: File not found: {file_path}")
except KeyError:
print(f"Error: Column '{column_name}' not found in sheet '{sheet_name}'.")
except Exception as e:
print(f"An error occurred: {e}")
# TOOO Main Function to iterate through the Excecl File
def redactinator(file, column_list, sheet, new_string, old_strings):
def parse_excel_file():
for col in column_list:
for old_string in old_strings:
replace_string_in_excel_column(file_path=file, sheet_name=sheet, column_name=col, old_string=old_string, new_string=new_string)
# TODO Usage
redactinator(file=excel_file, column_list=column_list, sheet=sheet_name, new_string=new_string, old_strings=old_strings)