I have a master .xlsx file on which other spreadsheets depend. Each week, I export a new .xlsx file (from MRP) that may or may not be the same as the master. The columns are always identical. Rows may increase or decrease. Cell values may change, usually just one (price value, column AB).
I wrote a Python program that reviews the differences, and updates the master with the new values. I wanted this program to update the master workbook to match the new values. Any new rows in the new workbook would be appended to the master as well.
Problems:
This is slow. Is there a more efficient way to do this? Luckily there are only about 2 - 4k rows. As in VBA code instead? Or another language altogether?
Any Pythonification updates I could make? I am a beginner so I'd like to learn how to optimize code as I go.
Any other advice or help would also be appreciated.
import sys
import openpyxl
import openpyxl.utils
from datetime import datetime as dt
import os
#get teh mater path
hm_dir = "C:\\path\\MASTER SHEETS"
suffix = ".xlsx"
#loop to get the choice of master workbook to update
while True:
wb_choice = input("Enter the master sheet you want to update:\n" \
"a for Main Warehouse\n"\
"c for Composite Item List\n"\
"i for Item Master\n")
if wb_choice.lower() == "a":
old_workbook_path = os.path.join(hm_dir, "Main_WHSE.xlsx")
break
if wb_choice.lower() == "c":
old_workbook_path = os.path.join(hm_dir, "COMPOSITE.xlsx")
break
if wb_choice.lower() == "i":
old_workbook_path = os.path.join(hm_dir, "ITEMS.xlsx")
break
else:
print("Enter A C or I")
continue
# Get the paths to the new workbook
new_workbook_fn= input("Enter the filename of the new workbook" \
" It must be xlsx format and " \
"it must be in the same folder as the master" \
" sheets, which is: \n" \
+ str(hm_dir) \
+ "\n\nFilename: ")
new_workbook_path = os.path.join(hm_dir, new_workbook_fn + suffix)
# Load the older and newer workbooks
old_workbook = openpyxl.load_workbook(old_workbook_path)
new_workbook = openpyxl.load_workbook(new_workbook_path)
# Select the active sheet in each workbook
old_sheet = old_workbook["Item"]
new_sheet = new_workbook["Item"]
sheet2 = old_workbook["Sheet 2"]
#copies the current max rows of the mater worksheet before updating
current_row = sheet2.max_row + 1
sheet2.cell(row=current_row, column=4).value = old_sheet.max_row
# Create a dictionary to map unique IDs to row numbers in the new workbook
new_id_map = {}
for i in range(2, new_sheet.max_row + 1):
new_id_map[new_sheet.cell(row=i, column=1).value] = i
#Create a dictionary to map unique IDs to row numbers in the old workbook
old_id_map = {}
for i in range(2, old_sheet.max_row + 1):
old_id_map[old_sheet.cell(row=i, column=1).value] = i
# Loop through the rows in the older workbook
rows_updated = 0
for j in range(2, old_sheet.max_row + 1):
old_id = old_sheet.cell(row=j, column=1).value
if old_id in new_id_map:
# If the ID is found in the new workbook, update the data in the old workbook
new_row_num = new_id_map[old_id]
updated = False # flag to keep track of whether row was updated or not
for k in range(1, new_sheet.max_column + 1):
old_value = old_sheet.cell(row=j, column=k).value
new_value = new_sheet.cell(row=new_row_num, column=k).value
if old_value != new_value:
# update the value only if it is different from the new value
old_sheet.cell(row=j, column=k).value = new_value
updated = True # mark the row as updated
if updated:
rows_updated += 1
# Loop through the rows in the new workbook and add any missing rows to the old workbook
for j in range(2, new_sheet.max_row + 1):
new_id = new_sheet.cell(row=j, column=1).value
if new_id not in old_id_map:
# If the ID is not found in the old workbook, add a new row to the old workbook
old_sheet.append([new_sheet.cell(row=j, column=k).value for k in range(1,
new_sheet.max_column + 1)])
old_id_map[new_id] = old_sheet.max_row
rows_updated += 1
# Save the changes to the older workbook
old_workbook.save(old_workbook_path)
# Add a row to Sheet 2 of the old workbook with the date
sheet2.cell(row=current_row, column=1).value =
dt.now().strftime("%m/%d/%Y %H:%M:%S")
sheet2.cell(row=current_row, column=3).value = new_sheet.max_row
# Save the changes to the old workbook
old_workbook.save(old_workbook_path)
# Display the number of rows updated
print(f"Number of rows updated: {rows_updated}")
