Pandas- Append Excel files in folder, but also each of their respective sheets, so output file has each appended sheet

Question

The following works great to look through a folder of .xlsx files and append them. But it only works to append the first tab and save the appended first tabs. I know about read pd.read_excel(f, sheet_name=None)- but this just does not work, I really want the output file to have multiple tabs, appended respectively as the first tab does. Any Idea?

import glob
import pandas as pd
import xlrd
#import XlsxWriter
all_data = pd.DataFrame()
for f in glob.glob(r'C:\*'):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
all_data.to_excel(writer)
writer.save()

yogkm yogkm · Accepted Answer · 2018-09-14T23:50:20

You need to use pandas.ExcelFile object. It has sheet_names method to fetch all the sheets in an excel file.

Take a look at the below code.

with pd.ExcelFile(<path_to_xlsx>) as xls:
    for each in xls.sheet_names:
        all_data.append(xls.parse(each), ignore_index=True)

Pandas- Append Excel files in folder, but also each of their respective sheets, so output file has each appended sheet

1 Answers