How to Merge Multiple Excel Workbooks into a single Workbook using Python

Question

I have 8 Excel Workbooks and each Workbook has 4 Worksheets. I want to Merge all these 8 workbooks into a single Workbook such that it will have 32 Worksheets in it.

I did refer: How to club different excel files into one workbook with different sheet names in python

But no luck, I am receiving below error:

Traceback (most recent call last):
  File "/root/scripts/t1.py", line 19, in <module>
    writer.save()
  File "/usr/lib/python2.7/dist-packages/pandas/io/excel.py", line 1462, in save
    return self.book.save(self.path)
  File "/usr/lib/python2.7/dist-packages/xlwt/Workbook.py", line 710, in save
    doc.save(filename_or_stream, self.get_biff_data())
  File "/usr/lib/python2.7/dist-packages/xlwt/Workbook.py", line 680, in get_biff_data
    self.__worksheets[self.__active_sheet].selected = True
IndexError: list index out of range

Please suggest how can I achieve my goal?

What am I missing?

Radu Pacurar Radu Pacurar · Accepted Answer · 2021-09-28T08:44:33

import pandas as pd
from os.path import exists


output_file = "output.xlsx"

list_of_workbooks = ['1.xlsx', '2.xlsx', '3.xlsx']

for wb in list_of_workbooks:
    
    df = pd.read_excel(wb, sheet_name = None)
    for sh in df.keys():
        new_sh_name = wb + '-' + sh
        if exists(output_file):
            mode = 'a'
        else:
            mode = 'w'
        with pd.ExcelWriter(output_file, engine="openpyxl", mode=mode) as writer:
            df[sh].to_excel(writer, sheet_name=new_sh_name)

How to Merge Multiple Excel Workbooks into a single Workbook using Python

1 Answers