How to club different excel files into one workbook with different sheet names in python

Question

I have two excel workbooks.

One with 3 sheets and the other with only one sheet. I am trying to combine these two into one workbook. This workbook should have 4 sheets.

from pandas import ExcelWriter

writer = ExcelWriter("Sample.xlsx")

for filename in glob.glob("*.xlsx"):
    df_excel = pd.read_excel(filename)

    (_, f_name) = os.path.split(filename)
    (f_short_name, _) = os.path.splitext(f_name)

    df_excel.to_excel(writer, f_short_name, index=False)

writer.save()

Doing this gives me a workbook, but with only 2 sheets. First sheet of the first workbook and second sheet of second workbook.

How to get all the 4 sheets in one workbook?

Answer 1

You have to loop through the sheet names. See the below code:

from pandas import ExcelWriter
import glob
import os
import pandas as pd

writer = ExcelWriter("output.xlsx")

for filename in glob.glob("*.xlsx"):
    excel_file = pd.ExcelFile(filename)
    (_, f_name) = os.path.split(filename)
    (f_short_name, _) = os.path.splitext(f_name)
    for sheet_name in excel_file.sheet_names:
        df_excel = pd.read_excel(filename, sheet_name=sheet_name)
        df_excel.to_excel(writer, f_short_name+'_'+sheet_name, index=False)

writer.save()