How To / Python: Loop over Excel files and extract a worksheet

In a previous post, I show how to append multiple csv files into one big file. In this post, I show how to loop over

Let’s assume you have a set of Excel files that have the same structure (e.g., 4 worksheets in the same order). Below I show a Python script to extract one particular worksheet from the files and create one workbook with all those worksheets. Although it does not matter for this script, let’s assume our files have names such as 1980data.xlsx, 1981data.xlsx, 1982data.xlsx.

import os
import win32com.client

path = "C:/Folder"  #This folder contains the spreadsheets
pathmaster = "C:/Desktop/MyFile.xlsx"

xl = win32com.client.DispatchEx('Excel.Application')
xl.Visible = False

wbmaster = xl.Workbooks.Open(Filename=pathmaster)

os.listdir(path)

for sheet in sheets:
    print(sheet)
    wb1 = xl.Workbooks.Open(Filename=os.path.join(path, sheet))

    ws1 = wb1.Worksheets(4)
    ws1.Name = sheet[:4]

    ws1.Copy(Before=wbmaster.Worksheets(1))
    wb1.Close(True)

wbmaster.Close(SaveChanges=True)

In line 4 you can set the folder where you have all the Excel files. Then, line 5 defines the path to the file where you are going to store the extracted worksheets. In line 8, you can specify whether or not you want to see Excel in action doing all these things (i.e., Excel instances are going to start opening and closing as the loop goes on).

In line 18, you can set which worksheet you want to extract. In this case, I am extracting (i.e., copying) the 4th one. In the next line, 19, I am extracting the first four character of the file name, and I add that as the worksheet name in the new file. Finally, line 21 copies the worksheet from the current file to the file where you want to group all the worksheets.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s