#3 Data Preparation - Splitting

 Here is clean, simple Python code that does exactly what you asked:

✅ Accept input folder
✅ Accept output folder
✅ Open each .xlsx file
✅ Read each sheet
✅ Save each sheet as a separate Excel file in output folder

---

✅ Python Script: split_excel_sheets.py

import os
import pandas as pd
def split_excel_sheets(input_folder, output_folder):
    # Create output folder if it does not exist
    if not os.path.exists(output_folder):
        os.makedirs(output_folder)
    # Loop over all files in input folder
    for file in os.listdir(input_folder):
        if file.endswith(".xlsx"):
            file_path = os.path.join(input_folder, file)
            print(f"Processing: {file}")
            # Load the entire Excel workbook
            excel_file = pd.ExcelFile(file_path)
            # For each sheet in the workbook
            for sheet_name in excel_file.sheet_names:
                print(f"  Extracting sheet: {sheet_name}")
                # Read sheet
                df = pd.read_excel(file_path, sheet_name=sheet_name)
                # Create output file name → original filename + sheet name
                base_name = os.path.splitext(file)[0]
                output_file_name = f"{sheet_name}.xlsx"
                output_path = os.path.join(output_folder, output_file_name)
                # Save sheet as a separate Excel file
                df.to_excel(output_path, index=False)
    print("Completed splitting all sheets!")
# -----------------------------
# MAIN PROGRAM
# -----------------------------
if __name__ == "__main__":
    input_folder = input("Enter input folder path: ")
    output_folder = input("Enter output folder path: ")
    split_excel_sheets(input_folder, output_folder)

Response(s)

Enter input folder path: input-folder

Enter output folder path: split-data

Processing: Book 1977-1982.xlsx

  Extracting sheet: 1977

  Extracting sheet: 1978

  Extracting sheet: 1979

  Extracting sheet: 1980

  Extracting sheet: 1981

  Extracting sheet: 1982

Processing: Book 1983-1988.xlsx

  Extracting sheet: 1983

  Extracting sheet: 1984

  Extracting sheet: 1985

  Extracting sheet: 1986

  Extracting sheet: 1987

  Extracting sheet: 1988

  Extracting sheet: Sheet1

Processing: Book 1989-1994.xlsx

  Extracting sheet: 1989

  Extracting sheet: 1990

  Extracting sheet: 1991

  Extracting sheet: 1992

  Extracting sheet: 1993

  Extracting sheet: 1994

Processing: Book 1995-2000.xlsx

  Extracting sheet: 1995

  Extracting sheet: 1996

  Extracting sheet: 1997

  Extracting sheet: 1998

  Extracting sheet: 1999

  Extracting sheet: 2000

Processing: Book 2001-2006.xlsx

  Extracting sheet: 2001

  Extracting sheet: 2002

  Extracting sheet: 2003

  Extracting sheet: 2004

  Extracting sheet: 2005

  Extracting sheet: 2006

Processing: Book 2007-2010.xlsx

  Extracting sheet: 2007

  Extracting sheet: 2008

  Extracting sheet: 2009

  Extracting sheet: 2010

  Extracting sheet: Sheet2

Processing: Book 2011-2016.xlsx

  Extracting sheet: 2011

  Extracting sheet: 2012

  Extracting sheet: 2013

  Extracting sheet: 2014

  Extracting sheet: 2015

  Extracting sheet: 2016

  Extracting sheet: Sheet7

Completed splitting all sheets!

---

✅ How to Run

1. Save the script as:

split_excel_sheets.py

2. Run in terminal / command prompt:

python split_excel_sheets.py

3. Enter:

Enter input folder path: C:\data\input
Enter output folder path: C:\data\output

---

🎯 Result

• Each sheet becomes a separate Excel file

• File name format:

OriginalFileName_SheetName.xlsx

---

If you want, I can add:

✔ GUI version
✔ Streamlit UI
✔ Progress bar
✔ Logging system
✔ Merge sheets instead of splitting

Just tell me!