Loop through Excel sheets in Python

Question

I am trying to extract all data from one excel file (data is spread into different sheets from the same file) into one new excel file

import pandas as pd
import glob as glob

appended_data = []
f_list = glob.glob(r'C:\Users\Sarah\Desktop\test\
               *.xlsx') 
for f in f_list: 
    data = pd.read_excel(f, sheet_name= ('May', 'April'))
    appended_data.append(data)
appended_data = pd.concat(appended_data)
appended_data.to_excel(r'C:\Users\Sarah\Desktop\test\appended.xlsx')

the problem is in the sheet_name if i try the code below :

data = pd.read_excel(f, sheet_name= [0,1,2,3])

i get a Value Error : No objects to concatenate

the code only works when i type the name of one sheet :

data = pd.read_excel(f, sheet_name="April")

But it doesn't work when i try the name of two sheets or more :

data = pd.read_excel(f, sheet_name= ('May', 'April'))

How to tell the code to loop through all the sheets? Thank you

mjspier · Accepted Answer · 2019-12-12 11:50:59Z

2

you can read all sheets by providing sheet_name=None

dict_of_frames = pd.read_excel(f, sheet_name=None)

full example:

all_sheets = []
for f in glob.glob(r'C:\Users\Sarah\Desktop\test\*.xlsx'):
        all_sheets.extend(pd.read_excel(f, sheet_name=None).values())
data = pd.concat(all_sheets)
data.to_excel(r'C:\Users\Sarah\Desktop\test\appended.xlsx')

edited Dec 12, 2019 at 11:50

answered Dec 12, 2019 at 10:30

mjspier

6,5565 gold badges36 silver badges47 bronze badges

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

2 Comments

sf61 Over a year ago

same problem : No objects to concatenate

mjspier Over a year ago

I think this error is coming from the pd.concat(appended_data) line. Can you validate that the appended_data list is not empty

Oleg O · Accepted Answer · 2019-12-12 11:01:48Z

1

Get all sheet names using pd.ExcelFile class

sheet_names = pd.ExcelFile(f).sheet_names

and iterate them via a generator

appended_data = pd.concat((pd.read_excel(f, sheet_name=s) for s in sheet_names))

Update with the context:

appended_data = pd.concat((pd.read_excel(f, sheet_name=None) for f in f_list))

edited Dec 12, 2019 at 11:01

answered Dec 12, 2019 at 10:25

Oleg O

1,0757 silver badges12 bronze badges

2 Comments

sf61 Over a year ago

it says that the sheet_name is not defined

Oleg O Over a year ago

Of course the sheet_names are specific for every file if you iterate files. Thus, you should (for example) save appended_data to a list, and then pd.concat that list again. Anyway the solution with sheet_names=None above is much simpler and elegant.

Collectives™ on Stack Overflow

Loop through Excel sheets in Python

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related