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:

Does this answer your question? [Import multiple excel files into python pandas and concatenate them into one dataframe](https://stackoverflow.com/questions/20908018/import-multiple-excel-files-into-python-pandas-and-concatenate-them-into-one-dat) — Anurag Dabas, Aug 10 '21 at 07:22
related [using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook](https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook) and [pandas-read-excel-with-multiple-sheets-and-specific-columns](https://stackoverflow.com/questions/41128526/pandas-read-excel-with-multiple-sheets-and-specific-columns) — Anurag Dabas, Aug 10 '21 at 07:23

Anton van der Wel · Accepted Answer · 2021-08-10T08:15:11.477

4

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()

edited Aug 10 '21 at 08:15

answered Aug 10 '21 at 07:22

Anton van der Wel

451
1
6
20

Thanks. For some reason this code only create an excel file with every third sheet concat. – buzzmind Aug 10 '21 at 07:41
did you copy my code, or did you retype it? It sounds like you forgot the double dots. For example you wrote: `mysheetnames[2]` instead of `mysheetnames[2:]` (the difference is ":") – Anton van der Wel Aug 10 '21 at 07:46
Maybe I interperted your question differently, but the problem is that in the excel file one 1 sheet exists? and you want to maintain all the sheetnames you had? – Anton van der Wel Aug 10 '21 at 07:57
Yes. The code you gives only create an excel with one sheet (with all the sheets from different excels) but I need each sheet combines separately. For example, sheet 3 from File A should combine with sheet 3 from File B and so on. So the result file should end up with 9 sheets (in you case 7 sheets without instruction sheet). – buzzmind Aug 10 '21 at 08:05
Still the same. Did you try your code yet? – buzzmind Aug 10 '21 at 09:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235816/discussion-between-anton-van-der-wel-and-buzzmind). – Anton van der Wel Aug 10 '21 at 09:18

How to concat excels with multiple sheets into one excel?

1 Answers1