Merge all excel files into one file with multiple sheets

Question

i would like some help.

I have multiple excel files, each file only has one sheet.

I would like to combine all excel files into just one file but with multiple sheets one sheet per excel file keeping the same sheet names.

this is what i have so far:

import pandas as pd
from glob import glob
import os


excelWriter =  pd.ExcelWriter("multiple_sheets.xlsx",engine='xlsxwriter')



for file in glob('*.xlsx'):
    df = pd.read_excel(file)
    df.to_excel(excelWriter,sheet_name=file,index=False)
excelWriter.save()

All the excel files looks like this:

https://iili.io/HfiJRHl.png

sorry i cannot upload images here, dont know why but i pasted the link

But all the excel files have the exact same columns and rows and just one sheet, the only difference is the sheet name

Thanks in advance

Answer 1

import pandas as pd
import os

output_excel = r'/home/bera/Desktop/all_excels.xlsx'

#List all excel files in folder
excel_folder= r'/home/bera/Desktop/GIStest/excelfiles/'
excel_files = [os.path.join(root, file) for root, folder, files in os.walk(excel_folder) for file in files if file.endswith(".xlsx")]

with pd.ExcelWriter(output_excel) as writer:
    for excel in excel_files: #For each excel
        sheet_name = pd.ExcelFile(excel).sheet_names[0] #Find the sheet name
        df = pd.read_excel(excel) #Create a dataframe
        df.to_excel(writer, sheet_name=sheet_name, index=False) #Write it to a sheet in the output excel