How to update text file from taking field values from excel file in python?

Question

I would like to fetch value of BSNValue field from excel file and update the same BSNValue against the matching alert_name in corresponding text file.

Original Excel file

| Alert_Name | BSNValue |
|:---:|:---:|
| Alert_number_1 | SVS176 |
| Alert_number_2 | SVS456 |
| Alert_number_3 | SVS678 |
| and so on |  |

Original Text File

\[Alert_number_1\]
Alert.test=true
Alert.BSNValue=$BSNValue$

\[Alert_number_2\]
Alert.test=true
Alert.BSNValue=$BSNValue$

\[Alert_number_3\]
Alert.test=false
Alert.BSNValue=$BSNValue$

....and so on

Expected output in a text file:

\[Alert_number_1\]
Alert.test=true
Alert.BSNValue=SVS176

\[Alert_number_2\]
Alert.test=true
Alert.BSNValue=SVS456

\[Alert_number_3\]
Alert.test=false
Alert.BSNValue=SVS678\`

type here

....and so on Any help/direction much appreciated.

I am thinking of using regex pattern to match same Alert Name from text file and excel file and then take values from excel file of Alert.BSNValue and update the same in text file.

Answer 1

If is csv (coma separated format) you can use pandas (you can read a xlsx too with the method read_excel). Here you have a code example:

(You need to pip install xlsxwriter (if you want to do it with an excel) and pandas)

example csv file (table.csv):

Alert_Name,BSNValue
:---:,:---:
Alert_number_1,SVS176
Alert_number_2,SVS456
Alert_number_3,VS678

Code:

import pandas as pd

# reading the file
with open("file.txt", 'r') as file:
    text = file.read()

alerts = pd.read_csv("table.csv")[['Alert_Name', 'BSNValue']].values

# replacing the values of the variables
for alert, value in alerts:
    if ':---:' not in alert:
        replace = [x for x in text.split("[") if alert in x]
        # if there is an alert with this name then replace the variable with the new one
        if replace:
            replace = replace[0]
            last_variable = [x for x in replace.split("\n") if 'Alert.BSNValue=' in x][0]
            new_variable = last_variable.split("=")[0]+'='+value
            text = text.replace(replace, replace.replace(last_variable, new_variable))
        else:
            print(alert+" doesn't exist in the file!")

# writting the new values to the file
with open("file.txt", "w") as file:
    file.write(text)

This is a simple example. You can change or complete it as you need.

This works if the file has this exact format:

[Alert_number_1]
Alert.test=true
Alert.BSNValue=$BSNValue$

[Alert_number_2]
Alert.test=true
Alert.BSNValue=$BSNValue$

[Alert_number_3]
Alert.test=false
Alert.BSNValue=$BSNValue$