CODEWITHSUNDEEP
excelpandasglob
sf61
sf61

Reputation: 55

Loop through Excel sheets in Python

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

Upvotes: 0

Views: 5665

Answers (2)

mjspier
mjspier

Reputation: 6526

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')

Upvotes: 2

Oleg O
Oleg O

Reputation: 1065

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))

Upvotes: 1

Related Questions