Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 712
Sam Avatar asked Dec 17 '22 21:12

Sam


1 Answers

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()
like image 172
Venkata Gogu Avatar answered Jan 19 '23 01:01

Venkata Gogu