Most of the articles I'm seeing either: a) Combine multiple excel single-sheet workbooks into one master workbook with just a single sheet or; b) Split a multiple-sheet excel workbook into individual workbooks.

However, my goal is to grab all the excel files in a specific folder and save them as individual sheets within one new master excel workbook. I'm trying to rename each sheet name as the name of the original file.

import pandas as pd
import glob
import os

file = "C:\\File\\Path\\"
filename = 'Consolidated Files.xlsx'
pth = os.path.dirname(file)
extension = os.path.splitext(file)[1]
files = glob.glob(os.path.join(pth, '*xlsx'))

w = pd.ExcelWriter(file   filename)

for f in files:
    print(f)
    df = pd.read_excel(f, header = None)
    print(df)
    df.to_excel(w, sheet_name = f, index = False)
   
w.save()

How do I adjust the names for each sheet? Also, if you see any opportunities to clean this up please let me know

CodePudding user response：

You cannot rename sheet with special characters because f is full path and file name. You should use only filename to names sheetname, Use os.path.basename to get file name and use split to seperate file name and extension.

for f in files:
    print(f)
    df = pd.read_excel(f, header = None)
    print(df)
    
    # Use basename to get filename with extension
    # Use split to seperate filename and extension
    new_sheet_name = os.path.basename(f).split('.')[0]
    
    # 
    df.to_excel(w, sheet_name = new_sheet_name , index = False)