Parsing the data from excel file and send calendar invite using python script

Question

I have a input excel file with the below data :

Server Name Event   Deploy Dist Type    Engineer    CR Number   Env Deployment Status   Date (IT)   Start (IT)  End (IT)    Primary Application
A   X   X   X   X   X   X   1/11/2019   8:30    12:30   X
B   X   X   X   X   X   X   1/11/2019   8:30    12:30   X
C   X   X   X   X   X   X   1/13/2019   8:30    12:30   X
D   X   X   X   X   X   X   1/13/2019   8:30    15:30   X

I need to send calendar invite as below (Groupby with dates)

1.Date, start time (smaller value in column [start{IT]]), End time (Greater value in column (End (IT)))

Below should print in calendar invite body

Server Name Event   Deploy Dist Type    Engineer    CR Number   Env Deployment Status   Date (IT)   Start (IT)  End (IT)    Primary Application
A   X   X   X   X   X   X   1/11/2019   8:30    12:30   X
B   X   X   X   X   X   X   1/11/2019   8:30    12:30   X

2.Same as 1

Server Name Event   Deploy Dist Type    Engineer    CR Number   Env Deployment Status   Date (IT)   Start (IT)  End (IT)    Primary Application
C   X   X   X   X   X   X   1/13/2019   8:30    12:30   X
D   X   X   X   X   X   X   1/13/2019   8:30    15:30   X

I got the code to send calendar invite but not sure how to parse the inputs like the above and send multiple invites based on date in input file

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import COMMASPACE, formatdate
from email import encoders
from tabulate import tabulate
import os, datetime
import csv
import pandas

#path_to_file = "C:\Users\kj\Desktop\Jan\sample.csv"
path_to_file = "C:/Users/kj/Desktop/Jan/sample_upd.csv"
output_to_file= "C:/Users/kj/Desktop/Jan/output.csv"
df = pandas.read_csv(path_to_file)
grouped = df.groupby('Date (IT)')
text = """
"""

html = """
<html><body><p></p>
<p></p>
{table}
<p></p>
<p></p>
</body></html>
"""
for name, group in grouped:
    dates_grp = name
    group.to_csv(output_to_file)

    CRLF = "\r\n"
    attendees = ["test@test.com"]
    organizer = "ORGANIZER;CN=organiser:mailto:test@test.com"
    fro = "test@test.com <test@test.com>"

    ddtstart = datetime.datetime.now()
    dtoff = datetime.timedelta(days=1)
    dur = datetime.timedelta(hours=1)
    ddtstart = ddtstart + dtoff
    dtend = ddtstart + dur
    dtstamp = datetime.datetime.now().strftime("%Y%m%dT%H%M%SZ")
    dtstart = ddtstart.strftime("%Y%m%dT%H%M%SZ")
    dtend = dtend.strftime("%Y%m%dT%H%M%SZ")

    description = "DESCRIPTION: OS PATCHING" + CRLF
    attendee = ""
    for att in attendees:
        attendee += "ATTENDEE;CUTYPE=INDIVIDUAL;ROLE=REQ-    PARTICIPANT;PARTSTAT=ACCEPTED;RSVP=TRUE" + CRLF + " ;CN=" + att + ";X-NUM-GUESTS=0:" + CRLF + " mailto:" + att + CRLF
    ical = "BEGIN:VCALENDAR" + CRLF + "PRODID:pyICSParser" + CRLF + "VERSION:2.0" + CRLF + "CALSCALE:GREGORIAN" + CRLF
    ical += "METHOD:REQUEST" + CRLF + "BEGIN:VEVENT" + CRLF + "DTSTART:" + dtstart + CRLF + "DTEND:" + dtend + CRLF + "DTSTAMP:" + dtstamp + CRLF + organizer + CRLF
    ical += "UID:FIXMEUID" + dtstamp + CRLF
    ical += attendee + "CREATED:" + dtstamp + CRLF + description + "LAST-MODIFIED:" + dtstamp + CRLF + "LOCATION:" + CRLF + "SEQUENCE:0" + CRLF + "STATUS:CONFIRMED" + CRLF
    ical += "SUMMARY:test " + ddtstart.strftime(
        "%Y%m%d @ %H:%M") + CRLF + "TRANSP:OPAQUE" + CRLF + "END:VEVENT" + CRLF + "END:VCALENDAR" + CRLF

    with open(output_to_file) as csvfile:
        reader = csv.reader(csvfile)
        eml_body = list(reader)
    text = text.format(table=tabulate(eml_body, headers="firstrow", tablefmt="grid"))
    html = html.format(table=tabulate(eml_body, headers="firstrow", tablefmt="html"))
    eml_body_bin = "This is the email body in binary - two steps"
    msg = MIMEMultipart('mixed')
    msg['Reply-To'] = fro
    msg['Date'] = formatdate(localtime=True)
    msg['Subject'] = "pyICSParser invite" + dtstart
    msg['From'] = fro
    msg['To'] = ",".join(attendees)

    #part_email = MIMEText(eml_body, "html")
    part_cal = MIMEText(ical, 'calendar;method=REQUEST')

    #msgAlternative = MIMEMultipart('alternative')
    msgAlternative = MIMEMultipart("alternative", None, [MIMEText(text), MIMEText(html, 'html')])
    msg.attach(msgAlternative)

    ical_atch = MIMEBase('application/ics', ' ;name="%s"' % ("invite.ics"))
    ical_atch.set_payload(ical)
    encoders.encode_base64(ical_atch)
    ical_atch.add_header('Content-Disposition', 'attachment; filename="%s"' % ("invite.ics"))

    eml_atch = MIMEBase('text/plain', ' ')
    # encoders.encode_base64(eml_atch)
    encoders.encode_7or8bit(eml_atch)
    eml_atch.add_header('Content-Transfer-Encoding', "")

    #msgAlternative.attach(part_email)
    msgAlternative.attach(part_cal)

    mailServer = smtplib.SMTP('test.test.com')
    # mailServer = smtplib.SMTP('MSGEXSV2D3906',25)
    # mailServer = smtplib.SMTP(s)
    mailServer.ehlo()
    # mailServer.starttls()
    mailServer.ehlo()
    # mailServer.login(login, password)
    mailServer.sendmail(fro, attendees, msg.as_string())
    mailServer.close()
    break

Updated the code, now i am able to send invite using grouped data. But need help in parsing date and time from the output of groupy. Any suggestion please? — Kiran Joseph, Jan 31 '19 at 09:09
Do you think of this [how-to-get-values-from-a-cell-of-a-groupby-object](https://stackoverflow.com/questions/15002085/how-to-get-values-from-a-cell-of-a-groupby-object) — stovfl, Jan 31 '19 at 12:00

stovfl · Accepted Answer · 2019-01-08T07:56:20.323

1

Question: Parsing the data from excel file and send calendar invite

You have to modularize into the following, using function or class:

Module Data
- Group the Excel Data
- Parse Data Row by Row
Module ICAL
- Create a ICAL Record from Excel Row Data
Module Mail
- Prepare Mail using ICAL Record
- Send Mail

Implementation - 1. Module Data

Reading an Excel file using pandas

import pandas
df = pandas.read_excel(open('your_xls_xlsx_filename','rb'), sheetname='Sheet 1')

Loop over grouped Pandas dataframe?

grouped = df.groupby('Date (IT)')

for name, group in grouped:
    ...

edited Jan 08 '19 at 07:56

answered Jan 07 '19 at 13:30

stovfl

14,998
7
24
51

I am stuck at "Getting the inputs from excel file and further process" – Kiran Joseph Jan 08 '19 at 05:47
Updated the code, now i am able to send invite using grouped data. But need help in parsing date and time from the output of groupy. Any suggestion please? – Kiran Joseph Jan 31 '19 at 11:47

Parsing the data from excel file and send calendar invite using python script

1 Answers1