CODEWITHSUNDEEP
pythonexcelpandasdataframexlrd
buzzmind
buzzmind

Reputation: 109

How to concat excels with multiple sheets into one excel?

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

File A Sheet 3 File B sheet 3 File A sheet 4 File B sheet 4

So eventually the combination file will be like:

File combination sheet 3 File combination sheet 4

Upvotes: 0

Views: 331

Answers (1)

Anton van der Wel
Anton van der Wel

Reputation: 451

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

Upvotes: 4

Related Questions