1

I am new to python and openpyxl. I have the beginning of a script that will loop through a directory and subdirectories and find any xlsx file that starts with "BEAR". Each of these files are in the same exact format. What I am trying to do is find the average of cell I3 from all the xlsx files it finds. Here is where I am so far.

I have tried altering my method by appending the sheet name "Sensor Status" to a new workbook. The problem I am now having is that it is copying over all the worksheets and not just the "Sensor" status sheet and is overwriting itself so I only have the data from the last file it looked at. How can I just copy the sheet I want and not overwrite itself at the same time? Here is my code 
import os
import openpyxl
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
from openpyxl import load_workbook
import csv

directoryPath = r'c:\users\username\documents\reporting\export\q3'
os.chdir(directoryPath)
folder_list = os.listdir(directoryPath)
for folders, sub_folders, file in os.walk(directoryPath):
    for name in file:
        if name.startswith("BEA"):
            filename = os.path.join(folders, name)
            wb = load_workbook(filename, data_only=True)
            sheet = wb.get_sheet_by_name("Sensor Status")
            rows = []
            for row in sheet.iter_rows(min_row=1):
                row_data = []
                for cell in row:
                    row_data.append(cell.value)


            wb.save('test.xlsx')
Lord Beerus
  • 69
  • 1
  • 1
  • 5
  • Use this approach [iterate-over-worksheets-rows-columns](https://stackoverflow.com/questions/42974450/iterate-over-worksheets-rows-columns/42977775) – stovfl Nov 18 '19 at 16:49
  • I should mention that with this script, it does loop through and find all the files that start with "Bear" and I can see the output file change file size however instead of just copying over the Sensor Status sheet it copies over all sheet, ignores cells with formulas and only paste from the last file it read. – Lord Beerus Nov 19 '19 at 13:56

0 Answers0