How can I write a python scripts using pandas to iterate over Excel .xlsx files with multiple sheets?

Question

I have some Excel .Xlsx files. Each file contains multiple sheets. I have used the following code to read and extract data from the files:

import pandas as pd
file = pd.ExcelFile('my_file.xlsx')
file.sheet_names #Displays the sheet names
df = file.parse('Sheet1') #To parse Sheet1
df.columns #To list columns

My interest is the email columns in each sheet. I have been doing this almost manually with the code above. I need a code to automatically iterate over the sheets and extract all emails. Help!

Bruno Mello · Accepted Answer · 2020-04-09 14:29:17Z

3

You can pass over all files and all sheets with a for loop:

import pandas as pd
import os

emails = []
files_dir = "/your_path_to_the_xlsx_files"
for file in os.listdir(files_dir):
    excel = pd.ExcelFile(os.path.join(files_dir,file))
    for sheet in excel.sheet_names:
        df = excel.parse(sheet)
        if 'email' not in df.columns:
            continue
        emails.extend(df['email'].tolist())

Now you have all the emails in the emails list.

edited Apr 9, 2020 at 14:29

answered Apr 9, 2020 at 11:08

Bruno Mello

4,6881 gold badge16 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

urbanmonk Over a year ago

Forgot to mention that some sheets don't have these 'email' column. I am getting some error

Bruno Mello Over a year ago

Just edited, if it doesn't have an email column it continues

urbanmonk Over a year ago

Thanks @Bruno. Can you advise on why am getting this error: FileNotFoundError: [Errno 2] No such file or directory: 'bongaigaon 500.xls.xlsx'" I have the file in my /tmp/work path

Bruno Mello Over a year ago

Sorry! My fault, I forgot to append the name of the dir along with the file name, see if it works now @urbanmonk

urbanmonk Over a year ago

Working now! Thanks mate

Collectives™ on Stack Overflow

How can I write a python scripts using pandas to iterate over Excel .xlsx files with multiple sheets?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related