Consolidate_excel_sheets.py

# -*- coding: utf-8 -*-
"""
Created on Tue Apr 16 15:45:52 2019

@author: Vinoth_Subramanian
Parse through multiple excel files in a folder heirarchy and consolidate
Only limitation - Since the copy is done through pandas, only the values - 
- from the sheets will be copied. Formulas and formatting will be ignored 
"""

import os
import pandas as pd

my_path = os.getcwd()

xl_name = "Consolidated.xlsx"

# Open the writer upfront so each worksheet gets appended rather than replaced
with pd.ExcelWriter(xl_name, engine = "openpyxl", 
                                            mode="a") as writer:
    
    for path, dirs, files in os.walk(my_path):
        for f in files:
            
            curr_path = path
            
            extn = os.path.splitext(f)[1]
            f_name = os.path.splitext(f)[0]
            
            # Filter the excel files based on certain criteria.
            # In this case, I am fetching all files with names
            # ending as "commercial model"
            if (extn == ".xlsx"and 
                f_name.split()[-1].lower() == "model" and 
                    f_name.split()[-2].lower() == "commercial"):
                        account = f_name.split()[0]
                        print(account)                        
                        source_wb = curr_path + "\\" + f
                        
                        data = pd.read_excel(source_wb, sheet_name="sheet 1")
                        
                        data.to_excel(writer, sheet_name=account, index=False, 
                                          header=None, startrow=2, )

    writer.save()