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.

I can convert the file in any format.So, it's okay whether xlsx of CSV. Thanks for your response — Tarun, Mar 17 '23 at 13:04

kithuto · Answer 1 · 2023-03-20T18:48:16.710

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$

Thanks @Kithuto for your response. Much appreciated. But somehow this code is not working properly. BSN value is getting updated to each alert_name but not in expected manner. If my Original text file looks like this:- — Tarun, Mar 20 '23 at 18:28
If my Original text file looks like this:- '\[Alert_number_3\] Alert.test=true Alert.BSNValue=$BSNValue$ \[Alert_number_1\] Alert.test=true Alert.BSNValue=$BSNValue$ \[Alert_number_2\] Alert.test=false Alert.BSNValue=$BSNValue$' In the above condition corresponding BSNValue for Alert_name_1 is not getting udpated with correct corresponding rather first BNSValue from excel file is getting appended to all the alert present in text file. Once again thanks for helping me out — Tarun, Mar 20 '23 at 18:28
I updated the code (It works if the file has the exact detailed format with the \n in each line). If is still not working execute step by step (using jupyter cells for example) and tell me what is printing out — kithuto, Mar 20 '23 at 18:49

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

1 Answers1