Writing multiple spreadsheet files to worksheets

Combine individual files into one file of multiple tabs

Here we got 37 spreadsheet files within a folder and I want all the files to be in a single spreadsheet file with each file on a separate worksheet.

This requirement is different from merging the file into a single excel worksheet file. What is required here is to have each file as a worksheet within one excel file as seen below.

The code is as follow:-

It makes use of the ExcelWriter pandas method. The parameter "options={'strings_to_urls': False}" is set to allow writing of cell values that will have more than 225 characters.

import glob
import pandas as pd

folder = r"C:\Users\Yusuf_08039508010\Documents\Distinguished Senators"

senators_files = glob.glob(folder + '/*.xlsx')
len(senators_files)


# Writing multiple dataframes to worksheets...
writer = pd.ExcelWriter('DistinguishedSenators.xlsx', engine='xlsxwriter', options={'strings_to_urls': False})

for sheet in senators_files:
    print("Writting sheet...", sheet)
    
    sheetname = sheet.split('\\')[-1].split('.')[0]
    
    sheet_df = pd.read_excel(sheet)
    sheet_df = sheet_df.head(-1)
    
    print(sheet_df.shape)
    
    sheet_df.to_excel(writer, sheet_name=sheetname, index=None) # Save each df to excel

writer.save()

Related Materials

1) How to Write Pandas DataFrames to Multiple Excel Sheets

2) Example: Pandas Excel with multiple dataframes