Updating Excel sheet with Python

Hi all - new to python, new to ML. Trying something here.
My basic objective is to take a PNG of an invoice, then use Azure Form recognizer to extract Unit Price and Item Name information and use this information to update the columns in the Excel sheet by the same names. For context, I am using StreamLit to build this app.

I have checked and re-checked permissions on the excel sheet file and it has all the relevant Read/Write permissions set correctly. This file is also not password locked. It is located in the same folder as the script I am running. And the path in the script is set correctly.

The problem:
The script runs through no problem and it tells me the excel sheet has been updated, but when I open it, the information in there has not been updated.

PLEASE HELP!! :grimacing:

Here is the script:

# Update the Excel sheet by matching the item names and updating the unit prices
 def update_excel_sheet(unit_prices, item_names, excel_file_path):
# Load the Excel file into a pandas DataFrame
df = pd.read_excel(excel_file_path)

# Update the unit prices based on the item names
for item_name, unit_price in zip(item_names, unit_prices):
    df.loc[df['Item Name'] == item_name, 'Unit Price'] = unit_price

# Save the updated DataFrame back to the Excel file
df.to_excel(excel_file_path, index=False)

Even if you’ve checked permissions, ensure that the Excel file isn’t open elsewhere when you’re running the script. Sometimes having the file open can prevent updates.
Additionally, I would look into other options than using excel. Using a .csv file format would even be better than excel, and easier to use, but ideally use something like sqlite3. The python package is free and easy to use with lots of online resources available.

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.