Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Iterate through all files in a folder and make simple calculation
#1
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;

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
Quote
#2
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.

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.
My code examples are always for Python >=3.6.0
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Quote
#3
Hi,

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

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

DA
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  make a list of the file in the folder and change the name of file regarding to time go127a 5 245 Feb-21-2020, 10:36 AM
Last Post: go127a
  Python: possibilty ro make a list of the file in the folder & change the name of file go127a 0 125 Feb-05-2020, 01:16 PM
Last Post: go127a
  os.walk does not see files that are in the folder kiton 1 258 Jan-21-2020, 07:26 PM
Last Post: micseydel
  Read all csv files, and store the last line from each folder SriRajesh 4 469 Jan-17-2020, 12:13 AM
Last Post: SriRajesh
  How to make a simple automation process of login using "if condition" soimba 3 283 Jan-07-2020, 11:58 PM
Last Post: SheeppOSU
  Downloading And Saving Zip Files To A Particular Path Folder eddywinch82 2 273 Jan-06-2020, 07:56 PM
Last Post: eddywinch82
  remove files from folder older than X days kerzol81 2 358 Jan-03-2020, 11:55 PM
Last Post: snippsat
  Problem with simple 3D Vektor calculation Pythocodras 0 114 Dec-11-2019, 07:18 PM
Last Post: Pythocodras
  Loop through folder of Excel Files and extract single column fioranosnake 2 399 Oct-28-2019, 05:19 PM
Last Post: fioranosnake
  Replace string in many files in a folder metro17 8 517 Oct-16-2019, 06:46 PM
Last Post: ndc85430

Forum Jump:


Users browsing this thread: 1 Guest(s)