Combine multiple Excell sheets in Python into the first one

Question

So I have one Excel Workbook, with 2 sheets with similar structure. I tried to combine Multiple Excel sheets that have a very similar name (they only differ in the last number) ,which are generated in my script, into the first sheet without a number.

So i tried to merge the 20248 Devicesi sheets into the 20248 Devices one, but im blocked. I've also tried to use pd.ExcelWriter(path_mapas + xlsx_mapa, mode = 'a', engine = 'openpyxl', if_sheet_exists = 'overlay') mode but that doesnt seem to be working for me (pandas 1.4.1).

This is kind of my code but im new at this :)

df_general = pd.concat(l_generales)
# for column in df_general.columns:
        # df_general[column] = df_general[column].astype(str).str.strip()

df_general.to_excel(xlsx_writer, sheet_name = plant_sheet_name_write, index = False)
xlsx_writer.save()
xlsx_writer = pd.ExcelWriter(path_mapas + xlsx_mapa, mode = 'a', engine = 'openpyxl', if_sheet_exists = 'new')


# for column in df_planta.columns:
        # df_planta[column] = df_planta[column].astype(str).str.strip()

for i in range(len(l_projects)):
    df_planta = l_plantas[i]
    # project = l_projects[i]
    project = str(df_planta['Project'].values[0])
    print(project)
    # print(l_plantas[j]['Project'])
    # if (project == l_plantas[j]['Project'].values[0]):
    #     df_planta = pd.concat(df_planta, l_plantas[i])
    planta = str(df_planta['Name'].values[0])
    print(planta)
    df_planta.to_excel(xlsx_writer, sheet_name = project + " Devices", index = False)
    


xlsx_writer.save()

for f in maps_xlsx_file:
    xlsx_mapa = pd.ExcelFile(f, engine = 'openpyxl')
    sheets = xlsx_mapa.sheet_names
    i=0
    for s in sheets:
        if (s == project + " Devices" + i-1):
            pd.concat(pd.read_excel(maps_xlsx_file, sheet_name = project + " Devices" + i), ignore_index = True)
        i=i+1```



I've tried the code above, but as i said, im not an expert and i cant use the overlay option.