Take input from excel and data need to be removed from text file

Question

I have below data in excel file (data.xlsx) more than 6000 rows and i have many text files in my ubuntu system.

Text files directory structure:-

/home/user/excel/report/ML_PROJECT_APPLICATION_WRITE.txt
/home/user/excel/report/folder-1/ML_PROJECT_APPLICATION_OPEN.txt
/home/user/excel/report/folder-1/filepath/ML_PROJECT_UBUNTU_OPEN.txt
/home/user/excel/report/folder-2/ML_PROJECT_CENTOS_WRITE.txt
/home/user/excel/report/folder-3/ML_PROJECT_RHEL_WRITE.txt

One of the text file format as follows,

ML_PROJECT_APPLICATION_WRITE.txt

# //DEPOT/ABCD/PROJECT/Jerd
# Permission: WRITE

dreac.leoson
ritu.bhangale
makyen
markerikson.s
bernardo.pereira 
elitezen

The filename of the text files are matching excel sheet's D column. For each row, i want to a search text file as per D column and need to look the User-ID in H column, if user-id exists in that particular row matched text file then user-id need to be removed from that text file. Need help to achieve this in automated way. Thank you!

not_speshal · Answer 1 · 2021-06-29T14:39:41.460

0

First, import data.xlsx into a pandas.DataFrame and create a dict to map the file names and user ids:

import pandas as pd
data = pd.read_excel("data.xlsx")
d = dict(zip(data["File Name"], data["User-ID"]))

Use a custom function to return the path of your file (with sub-directory):

import os
def find(name):
    for root, _, files in os.walk("/home/user/excel/report"):
        if name in files:
            return os.path.join(root, name)

Then, loop through the dict and transform your files as needed. First, read the file. Then, remove the user-id if it exists and write back to the file.

for file in d:
    fullname = find(f"{file}.txt")
    if fullname is not None:
        with open(fullname, "r") as f:
            contents = f.read().strip()
    
        with open(fullname, "w") as f:
            f.write(contents.replace(d[file], ""))

Note, if your files are in a different directory, make sure you specify the entire file path in pd.read_excel and open.

edited Jun 29 '21 at 14:39

answered Jun 28 '21 at 16:12

not_speshal

22,093
2
15
30

not_speshal, thank you. Files are in different directories under for example /home/user/excel/report/ – user4948798 Jun 28 '21 at 16:34
Even this handles recursively the `text` files present inside sub-directories of `/home/user/excel/report/` directory as well? – user4948798 Jun 29 '21 at 03:12
It is not able to find the `text files` exists under `home/user/excel/report/` its sub-directory. Could you please help me to search inside its sub-directories as well? – user4948798 Jun 29 '21 at 10:11
Update your question with your folder structure and where to find each file. – not_speshal Jun 29 '21 at 12:59
i have updated the question with folder structure of `text files`. As per `data.xlsx` file it need to search/find the `text file` in `/home/user/excel/report/` folder and its sub-folder. – user4948798 Jun 29 '21 at 13:28
Edited my answer - This works on a Windows PC. Please look to adapt for Ubuntu if needed. – not_speshal Jun 29 '21 at 14:28
Ok on windows what is the python version used? – user4948798 Jun 29 '21 at 14:37
Python 3.8.10 - If you have any further issues, please open a new question. – not_speshal Jun 29 '21 at 14:41

Take input from excel and data need to be removed from text file

1 Answers1