modifying the excel files using pandas in python

Question

I am trying to modify an existing Excel file using the pandas library in Python.

i used the file contents for my automated email triggering the email to the customer

once the mail sent i just want to update the status of the customer in the excel file

is there a way to do this using pandas library in python?

subprocess.Popen('C:\\Program Files (x86)\\Microsoft Office\\Office15\\OUTLOOK')
    time.sleep(10)
    for i in range(0, len(DAYS_COUNT)):
        if df['Acceptance status'][i] in NO_FOLLOWUP:
            pass
        if df['Days \ncount'][i] >= 7 and df['Platform'][i] == "Email":
            time.sleep(3)
            print(df['Company Name/ \nEmail Address'][i])
            send_to = df['Company Name/ \nEmail Address'][i]
            pyautogui.click(x=25, y=244)  # sent_click
            time.sleep(1)
            pyautogui.click(x=118, y=158)  # search_sent_click
            time.sleep(1)
            pyautogui.click(x=286, y=74)  # search_by_to_click
            time.sleep(1)
            pyautogui.write(df['Company Name/ \nEmail Address'][i])
            time.sleep(2)
            # pyautogui.click(x=207, y=254)  # first_item_click
            pyautogui.click(x=186, y=276)  # first_item_click
            time.sleep(1)
            pyautogui.click(x=639, y=162)  # forward_click
            time.sleep(1)
            pyautogui.write(f"{send_to};")  # to_address
            time.sleep(5)
            pyautogui.press("tab")
            pyautogui.write(f"{CC};")  # cc_address
            time.sleep(5)
            pyautogui.press("tab")
            pyautogui.tripleClick(x=607, y=252)  # subject_line_remove_existing
            subject_status = df['Acceptance status'][i].lower()
            pyautogui.write(MAIL_SUBJECT[subject_status])  # subject
            time.sleep(5)
            pyautogui.press("tab")
            pyautogui.press("tab")
            mail_body_status = df['Acceptance status'][i].lower()
            if df['Remarks'][i] == "economic times":
                pyautogui.write(BD_FRESH_MAIL['economic_mail_body'])
            else:
                pyautogui.write(MAIL_BODY[mail_body_status])  # mail_body
            time.sleep(5)
            # pyautogui.press("delete")
            # pyautogui.press("delete")
            pyautogui.click(x=489, y=205)  # send_click
            time.sleep(1)

once the mail sent i just want to update the status of the customer in the excel file

and save it without losing the data in the excel file

Romerin · Answer 1 · 2023-03-01T22:39:46.457

I recommend you to use the pandas DataFrame data structure. You can read the excel file using read_csv function and after that use DataFrame.at function to set a new value.

import pandas as pd
data = pd.read_csv("PATH TO EXCEL FILE")
row_index = 5 # ROW THAT NEEDS TO BE UPDATES
col_name = "STATUS"
data.at[row_index, col_name] = True # SET THE NEW VALUE
data.to_csv("PATH TO A NEW EXCEL FILE")

After update, you can save the data frame using to_csv function passing a new file path or use the same path to overwrite the older excel file.

Pandas doc about .at function: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.at.html

score 0 · Answer 2 · answered Mar 01 '23 at 22:40

Of course! Pandas is using openpyxl to complete some of the excel-specific actions, though, so you may be able to simply use that library instead.

You should be able to insert your excel-manipulation code after your desired trigger action.

Example of reading an excel sheet into memory with both pandas and openpyxl. Let us know what else you'd like to do!

import pandas as pd
df = pd.read_excel('path_to_file.xlsx')

from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')

https://openpyxl.readthedocs.io/en/stable/tutorial.html

P.s. You might be able to simplify (and error-proof) your pyautogui actions with the win32com library using outlook programmatically, instead of using the GUI.

Send Outlook Email Via Python?

Dennis · Answer 3 · 2023-03-01T23:06:23.527

I use openpyxl to write excel files if I need more control. Otherwise just pd.to_excel(). This is just a general idea:


with pd.ExcelWriter("path_to_file.xlsx", 
  mode="a", engine="openpyxl") as writer:
     
  data_frame.to_excel(writer, sheet_name="Customers")

or

df.to_excel("name.xlsx")

If you are at a stage where you need to update df first (just an example):

# Your source of emailed customers
# this is just a demo
listOfReceivers = [nam1, name2, name3]

def checkName(customer):
   # may want to strip() and lower() all names
   if customer in listOfReceivers:
      return True
   else:
     return False


# Change columns to match yours
df['Emailed'] = df['Customers'].apply(checkName)
df.to_excel("name.xlsx")

modifying the excel files using pandas in python

3 Answers3