Is there a way to simplify this code in python and remove the step of converting the Excel sheet into a csv file?

Viewed 36

I am trying to extract all of the email addresses from an excel sheet to use to send an email to a group of people. the only way I've been able to do that so far is by converting to a csv file first. The excel sheet is not always the same and the format could change there are multiple columns with email addresses and some spots that are empty. Are there any solutions for this?

    #importing libraries
import pandas as pd
import csv
import re
  
# Read and store content of an excel file 
read_file = pd.read_excel ("Email_List.xlsx")
  
# Write the dataframe object into csv file
read_file.to_csv ("Test.csv",  index = None, header=True)
    
# Read the csv file contenets
with open('Test.csv') as csv_file:
    csv=csv.reader(csv_file, delimiter=',')

    # join csv data into string
    for row in csv:
        data=(', '.join(row))

        # search for email addresses
        emails = re.findall("([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.,]+)", data)

        # print list of email addresses
        for mail in emails:
            print(mail)
1 Answers
emails = (pd.read_excel("Email_List.xlsx", header=None).stack().reset_index(drop=True)
          .str.findall(pat=r"([\w_+\-]+@[\w\-]+\.[\w\-,]+)").explode())
print(emails)

Result:

0         first@email.aaa
0    second@email.example
1                   a@b.c
2     xxxx123@yyyy786.com
3        777aaa@fffff.org
dtype: object

Excel source:
enter image description here

Related