How to concat excels with multiple sheets into one excel?

Question

The folder contains at least 20 excels. Each excel contains nine sheets. These excels have same type of sheets (same header but different data). I need to concat these 20 excels sheet by sheet into one excel. And the first two sheets in each excel are instruction. They are skippable. How can I achieve this? Thanks!

Example: File A Sheet 3, File B sheet 3, File A sheet 4, File B sheet 4

So eventually the combination file will be like:

Answer 1

I had to do something similair a while back:

This code should do the trick for you:

import pandas as pd
import os

collection = {}
for file in os.listdir():
    if file.endswith(".xlsx"):
        mysheets = pd.ExcelFile(file)
        mysheetnames = mysheets.sheet_names
        for i in mysheetnames[2:]: #change the 2 in [2:] to change how many sheets you delete
            mydata = pd.read_excel(file, i)
            combi = collection.get(i, [])
            collection[i] = combi + [mydata]

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

for key in collection:
    myresult = pd.concat(collection.get(key), sort=False)
    myresult.to_excel(writer, sheet_name=key)

writer.save()