How to save dataframe value in excel/csv

Question

i am trying to save outlook email body content to dataframe then to csv/excel, we usually get prices from vendor for different indices in tabular format, i tried using Body_content = message.HTMLBody but didnt work as intented.

Thus i am ok with using Body_content = message.Body and print (df.To_string()). Now the next step is to store those values in excel sheet even if its open by an anlyst in below format.

we keep getting index prices from different vendors, if this one works perfectly, i will follow the same method for others as well.

Output

This is how it ends up getting a single column dataframe

import pandas as pd
import win32com.client
import re
import os
import pandas

import datetime
from datetime import date

EMAIL_ACCOUNT = 'lakshmi@NY.com'
EMAIL_SUBJ_SEARCH_STRING = 'MSBPSCSP Index Price'
EMAIL_CONTNT = {'Ticker': [], 'TickerLevel': [], 'DATE': []}

out_app = win32com.client.gencache.EnsureDispatch("Outlook.Application")
out_namespace = out_app.GetNamespace("MAPI")
#lastWeekDateTime = dt.datetime.now() - dt.timedelta(days=1)
#lastWeekDateTime = lastWeekDateTime.strftime('%m/%d/%Y %H:%M %p')


root_folder = out_namespace.GetDefaultFolder(6)

out_iter_folder = root_folder.Folders['Email_Snapper']

#char_length_of_search_substring = len(EMAIL_SUBJ_SEARCH_STRING)



item_count = out_iter_folder.Items.Count

Flag = False
cnt = 1
if item_count > 0:
    for i in range(item_count, 0, -1):

        message = out_iter_folder.Items[i]
        if EMAIL_SUBJ_SEARCH_STRING in message.Subject:
            #message = message.Restrict("[ReceivedTime] >= '" + lastWeekDateTime + "'")

            Body_content = message.Body
Body_content = Body_content[:Body_content.find("Disclaimer")].strip()
df = pd.DataFrame([Body_content])
print(df.to_string())

The final objective is to insert those prices in oracle database either through saving them in excel first or if possible directly from the dataframe (that's even better)

user_11105356 · Answer

You can export the dataFrame to excel

df.to_excel("output.xlsx", index=False)

or CSV -

df.to_excel("output.csv", index=False)

user_1603351 · Answer

The Outlook object model supports three main ways of customizing or getting the message body:

The Body property returns or sets a string representing the clear-text body of the Outlook item.
The HTMLBody property of the MailItem class returns or sets a string representing the HTML body of the specified item. Setting the HTMLBody property will always update the Body property immediately. For example:

     Sub CreateHTMLMail() 
       'Creates a new e-mail item and modifies its properties. 
       Dim objMail As Outlook.MailItem 
       'Create e-mail item 
       Set objMail = Application.CreateItem(olMailItem) 
       With objMail 
        'Set body format to HTML 
        .BodyFormat = olFormatHTML 
        .HTMLBody = "Enter the message text here. " 
        .Display 
       End With 
     End Sub

The Word object model can be used for dealing with message bodies. See Chapter 17: Working with Item Bodies for more information. So, you can get the required information using the Word object model.

It is up to you which way is to choose. But I suppose the Word object model can help you with extracting the required information.

How to save dataframe value in excel/csv

2 Answers