Yasin's read excel and produce search

search_through_excel.md

You need to install Python and pandas library before running this script.

Assuming we have .xlsx sheet sit right at where we run Python script.

sheets = pd.ExcelFile('Lista de e-mails & Email List.xlsx') # use pandas to read excel sheets
search_sheet = pd.read_excel('Exampl names.xls', header=None)[[2]].rename(columns={2: 'search_name'}) # also use to read search name

Here, I create function to parse excel sheets and grab all name founded in documents then concat them together

def search_through_sheet(sheets, search_term):
    found_name = []
    for sheet_name in sheets.sheet_names:
        df = sheets.parse(sheet_name)
        df['SheetName'] = sheet_name
        found_name.append(df[df['Nome'].map(lambda x: search_term in x.lower())])
    return pd.concat(found_name, axis=0)

Here, we use the function to look into all s_terms in the given file and return dataframe output and sheet name which will be keyword that we use to search.

s_terms = map(lambda x: x.lower().strip(), list(search_sheet.search_name)) # search terms from excel file that we have

search_found = []
for s_term in s_terms:
    df_found = search_through_sheet(sheets, s_term)
    sheet_name_out = s_term
    search_found.append((sheet_name_out, df_found))

Finally, we write it back into excel sheet, named search_found.xlsx.

writer = pd.ExcelWriter('search_found.xlsx')
for (name, df) in search_found:
    df.to_excel(writer, name)
writer.save() # save to excel sheet