CODEWITHSUNDEEP
python-3.xopenpyxl
Jose Daniel Rojas
Jose Daniel Rojas

Reputation: 71

Iterating and editing a varying number of excel files in a specific directory

I got a number of .xls files inside a specific directory with varying names and quantities that are downloaded from outlook. The objective of the script is to open each file and then write "Confirmed" in the O column if the M column is not blank.

import openpyxl as xl
import os
import sys
import pathlib
from pathlib import path

if __name__ == "main":
    while True:
        desktop_folder = Path.home().joinpath("Desktop", "Excel Files")
        folder = (str(desktop_folder) + str("\\"))
        os.chdir(folder)
        excelFiles = os.listdir('.')

        for i in range(0, len(excelFiles)):
            wb = xl.load_workbook(excelFiles[i])
            sheet = wb.active
            for c, cellObj in enumerate(sheet['O'], 1):
                if c != 1:
                    cellObj.value = '=IF(M="","","Confirmed")'.format(c)
            wb.save(excelFiles[i])
            print(excelFiles[i] + 'completed')
        sys.exit()

At the moment this is the code I have, but I'm not getting any output on the terminal. Any thoughts?

Thanks!

Upvotes: 0

Views: 58

Answers (1)

moken
moken

Reputation: 6536

From looking at your code there are a few issues I see. Some of these may be copy/paste errors. Therefore given that issue 1 and 2 below are probably Ok in your testing you should at least be getting the 'completed' print output UNLESS there are no [xlsx] files whatsoever in your '<user>\Desktop\Excel Files' directory. Even a file that is not an xlsx file should cause an error. So it seems this is probably the reason for your issue.
As Andreas says, DEBUG, see if you are actually adding any [xlsx] files into the excelFiles list.

  1. You are importing 'path' from pathlib. The function is 'Path' upper case 'P'

    from pathlib import Path

  2. __name__ should equal __main__ Very much likely this is just a copy/paste error

    if __name__ == "__main__":

  3. Your formula wouldn't do much

    =IF(M="","","Confirmed")'.format(c)

M="" is not going to achieve what you think. You need to use the cell coordinate so there is some missing brackets

cellObj.value = '=IF(M{}="","","Confirmed")'.format(c)

or the new method

cellObj.value = f'=IF(M{cellObj.row}="","","Confirmed")

Note you dont need the enumerate, just use the cells' row value

  1. There is no space between the Excel file name and the word 'completed'

    print(excelFiles[i] + 'completed')

The two words would be run together like 'excelfilecompleted'

Upvotes: 2

Related Questions