Comparing photos from the directory with an excel table and entering them into a new excel table

Question

I have a directory with about 250 photos whose names are defined by numbers. Example: "12341233.jpg". 12341233.jpg indicates the ID of the item displayed on the site. Some items have 2 or more photos, in which case "-1", "-2", "-3", "-4" is added to the photo name. Which means that if Article 12341233 has 3 photos in the directory, there will be 3 photos under the names "12341233.jpg", "12341233-1.jpg", "12341233-2.jpg".

In the excel table, which is stored in the same directory as the photos, we have the row Name of the Article (row C) and the row Picture 1 (row K), Picture 2 (row L), Picture 3 (row M) and Picture 4 (row N).

What the script should do is compare the names of the photos from the directory with the names in the excel table (without the .jpg extension) and if there are names in the excel table and it does not find them in the directory, it creates a new excel table and writes the names of the missing photos there (Picture 1 , Picture 2, Picture 3, Picture 4 ). If they happen to have 2 photos in the directory while 2 are not found, in line Image 3, Image 4 only writes a red background color. The same applies if there is, for example, picture 2 and picture 4 in the directory in the excel table, the background color is red marked by row Picture 1 and Picture 3.

import os
import openpyxl
from openpyxl.styles import PatternFill

photo_dir = "";
excel_dir = "";
new_excel_dir = "";

wb_table = openpyxl.load_workbook(excel_dir)
sheet_table = wb_table.active

wb_new_table = openpyxl.Workbook()
sheet_new_table = wb_new_table.active

photos = [f.split(".")[0] for f in os.listdir(photo_dir) if os.path.isfile(os.path.join(photo_dir, f))]

suffixes = ["-1", "-2", "-3", "-4"]
fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")

for row in sheet_table.iter_rows(min_row=2, min_col=3, max_col=3, values_only=True):
    photo_name = row[0].split(".")[0]
    
    if photo_name in photos:
        sheet_new_table.append([photo_name])

        if any(suffix in photo_name for suffix in suffixes):
            for i in range(2, 6):
                sheet_new_table.cell(row=sheet_new_table.max_row, column=i).fill = fill

wb_new_table.save(new_excel_dir)

When I run the script I get an error:

Traceback (most recent call last):
  File "C:\Users\*******\test.py", line 26, in <module>
    photo_name = row[0].split(".")[0]
                 ^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'split'

Can someone help me with this script, if I'm wrong somewhere, correct me and explain. Thanks to all

Please, post the full traceback you get, not just the error description — buran, May 17 '23 at 07:51
Clearly *row[0] == None* so you'll need to figure out why that might be — DarkKnight, May 17 '23 at 07:58
That is an empty cell. You use `values_only=True`, so row is tuple of row values — buran, May 17 '23 at 08:01
Is it reasonable to have empty cell? If yes - check that `row[0]` in not None before split, handle the case of empty cell accordingly. Also check `os.path.splitext` for proper file name manipulation — buran, May 17 '23 at 08:05
I'm new to python, I'm trying to understand you as best as possible but it's not working. I hope you understand me @buran — Web Admin, May 17 '23 at 08:08
You need to account for empty cells, e.g. validate that `row[0]` is not `None`. — metatoaster, May 17 '23 at 10:23

Comparing photos from the directory with an excel table and entering them into a new excel table

0 Answers0