Iterate through all files in a folder and make simple calculation

[danielappleton]

Hi,

I want to iterate through a number of .xlsx files in a folder and make a simple calculation for each of the rows in each file. I want to subtract a given number ('compensation') from the first cell (index 0). I then want the calculated value ('corrected_pressure') returned in the adjacent cell (index 1) to the right. I want this to occur for the first cell in every row where there is data. I then want the process iterated for all the .xlsx files in a folder.

I have begun below but I can't seem to get line 5 working;

1 2 3 4 5 6 7 8 9 10 11 12 13 14

compensation = 1000   import openpyxl as xl import Files for i in Files:     if i[-4:] =='xlsx':         wb = xl.load_workbook(i)         sheet = wb['Content']         for row in range(2, sheet.max_row+1):             cell = sheet.cell(row, 2)             corrected_pressure = cell.value - compensation             corrected_pressure_cell = sheet.cell(row, 4)             corrected_pressure_cell.value = corrected_pressure         wb.save(i)

I get the error;

Error:
Traceback (most recent call last):
  File "C:/Users/daniel.appleton/PycharmProjects/draft/app.py", line 5, in <module>
    for i in Files:
TypeError: 'module' object is not iterable

Any ideas? Should be a simple fix

DA

[DeaD_EyE]

What is Files?
You're importing it and then you iterate over Files, which is not possible, because it's a module.

If you want to get a list of file, use os.listdir or use pathlib.Path.

1 2 3 4 5 6 7 8 9 10 11 12

from pathlib import Path     my_home = Path.home() my_data = my_home / "Desktop/data"   for file in my_data.glob("*.xlsx"):     print(file, type(file))     wb = xl.load_workbook(file)     # if openpyxl is complaining about file, convert it to a str     # wb = xl.load_workbook(str(file))

If openpyxl doesn't support pathlib objects, then you convert the pathlib object to a str.

[danielappleton]

Hi,

1

Files

is the folder which holds all the files that I want to iterate through.

Is

1

my_data

(line 5) supposed to equal the location of the .xlsx files that I want to iterate through?

DA