How to Update df to existing excel file with python

Question

I have existing excel file for updated finished e_mail.I need to update any detail to existing excel.This's my existing excel file.It have only header Like This:

This's my output.It's split two excel files.Like this.

In Excel file it show detail like this:

I want output to update detail to existing excel file. like this:

This's my python code:

import os
import pandas as pd
import win32com.client
import time
from datetime import datetime
import openpyxl
today = datetime.today().strftime('%d%m%Y')
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.GetDefaultFolder(6).Folders.Item("3.1done_FPA")
finishbox = outlook.GetDefaultFolder(6).Folders.Item("4.finished")
outlook = inbox.Items
mail = outlook.GetLast()

filename = "R:/ReportAFS/PaymentOutlookForm/PaymentOutlookForm_"+str(today)+".xlsx"

for mail in reversed(outlook):
    subject = mail.Subject

    df = pd.read_excel("R:/veerachai/Invoice_form/copy_file/b'"+str(subject)+"'"+"/"+"b'"+str(subject)+"'.xlsm",sheet_name ='PaymentOutlookForm')

    directory = 'R:/ReportAFS/PaymentOutlookForm'
    file = 'PaymentOutlookForm_'+str(today)+'_'+str(subject)+'.xlsx'

    if not os.path.exists(directory):
        os.makedirs(directory)
    df.to_excel(os.path.join(directory, file),index = False)

    time.sleep(1)
    mail.Move(finishbox)
    print(str(mail.Subject)+ ' working success.')

Please tell me how to solve this problem.

score 0 · Accepted Answer · answered May 12 '20 at 09:47

Excel can open and work with .csv files quite easily. You can convert the current Excel file to .csv and then work easily with it using pandas. It offers the to_csv() method with different modes which is perfect for what you need. Given that you just want to append a row to an already existing .csv, you can just call the to_csv() method specifying the a mode parameter and setting the header to False (since we don't want to re-write the header every time).

df.to_csv('my_csv.csv', mode='a', header=False)

If you want to work directly with Excel files (without converting to .csv), you can have a look at this.

How to Update df to existing excel file with python

1 Answers1