How to combine multi excel workbook into single workbook with multiple worksheets

Question

I have 3 workboooks with single sheets.I need to combine all workbooks into single workbook with 3 sheets.

I tried the below code :

from pandas import ExcelWriter

writer = ExcelWriter("Sample.xlsx")

for filename in glob.glob("*.xlsx"):
    df_excel = pd.read_excel(filename,engine='openpyxl')

    (_, 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()

i got an error like "File is not zip File"

Answer 1

"Sample.xlsx" is created in the same directory as the input workbooks and before you look for all files with glob.glob("*.xlsx"). Therefore you try to read "Sample.xlsx" which is your writer. This isn't working.

Make sure to only iterate over the real input workbooks e.g. like that:

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

writer = ExcelWriter("Sample.xlsx")
input_workbooks = glob.glob("*.xlsx")
input_workbooks.remove("Sample.xlsx")

for filename in input_workbooks:
    df_excel = pd.read_excel(filename,engine='openpyxl')

    (_, 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()

Better would be to save the output workbook ("Sample.xlsx") to another directory to avoid confusion. Obviously, when you do that, you do can not remove it from the list any longer, so just delete the line: input_workbooks.remove("Sample.xlsx")