Combining Multiple Excel Files

combine_excel.py

import pandas as pd  # Ensure xlrd and xlsxwriter are installed. Needed for working with Excel files.
import os
import easygui

target_open_folder = easygui.diropenbox(msg="Select the folder with the Excel files to be combined.")

file_list = []

for file in os.listdir(target_open_folder):
    file_list.append(os.path.join(target_open_folder, file))

excel_sheets = []

for file in file_list:
    dataframe = pd.read_excel(file)
    excel_sheets.append(dataframe)

combined_excel = pd.concat(excel_sheets)
save_file_path = easygui.filesavebox(msg="Files combined. Select a save location and filename.", default="combined_excel.xlsx",
                                     filetypes=["*.xlsx", "Excel File"])

writer = pd.ExcelWriter(save_file_path, engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
combined_excel.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.save()