Disclaimer: I'm relatively new to Python. I am attempting to write a script that will go through a CSV, check if certain columns match Outlook calendar items (by subject, organizer, and date match), and then have the script note that there was a successful match in a new column (I stole heavily from this question). Below is my whole script.
import win32com.client, datetime, re, os, csv, shutil
# set cwd, create copy of original CSV, and access outlook API
os.chdir('C:\\')
shutil.copy('masterCheck.csv', 'inspectCheck.csv')
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inspectors = {'ASMITH': 'Smith, Aaron', 'JDOE': 'Doe, Jane', 'KZEEBOP': 'Zeebop, Kim'}
#access csv and put it into a list
with open('inspectCheck.csv', 'r', newline = '', encoding='utf-8') as csvAppointments:
reader = csv.reader(csvAppointments)
masterList = list(reader)
del masterList[-1] # delete blank nested list
del masterList[1] #delete header
for i in masterList: # switch out names so they can match Outlook descriptors later
for key, value in inspectors.items():
if i[3] in key:
i[3] = value
# create another list for appending later in the script
finalList = []
finalList += masterList
# access the inspectors' calendars
x = 0
try:
for inspector in inspectors.values():
recipient = outlook.createRecipient(inspector)
resolved = recipient.Resolve()
sharedCalendar = outlook.GetSharedDefaultFolder(recipient, 9)
codeAppointments = sharedCalendar.Items
#restrict to items in the next year
begin = datetime.date.today()
end = begin + datetime.timedelta(days = 365);
restriction = "[Start] >= '" + begin.strftime("%m/%d/%Y") + "' AND [End] <= '" +end.strftime("%m/%d/%Y") + "'"
restrictedItems = codeAppointments.Restrict(restriction)
# loop through inspectors' appointments and match
for appointmentItem in restrictedItems:
for i in masterList:
addressSearch = i[1]
if re.search(r'%s' % addressSearch, appointmentItem.Subject, re.IGNORECASE)\
and i[3] in appointmentItem.Organizer\
and i[4] in appointmentItem.Start:
finalList[x].append(appointmentItem.Subject)
x += 1
except IndexError:
pass
# update spreadsheet
with open('inspectCheckFinal.csv', 'w', newline = '') as csvAppointments:
appointmentsWriter = csv.writer(csvAppointments)
appointmentsWriter.writerows(finalList)
I have had success matching columns from my CSV to Outlook items. For example, I can get this to match.
if re.search(r'%s' % addressSearch, appointmentItem.Subject, re.IGNORECASE)
However, as soon as I try to match it to my date column (i[4]), it throws the error: TypeError: argument of type 'pywintypes.datetime' is not iterable. The dates in my CSV look like 2/11/2018 but the dates (when printed) in Outlook look like 2017-11-16 11:00:00+00:00. I'm at a loss on how to match these two.
Moreover, I am having trouble marking the CSV with successful matches. While the script will append a value to the end of each nested list (and then write it to a CSV), it will not append the value to the matched row of the CSV. For example, my output looks like:
Inspector |Address |Date |Success?(print Address)
ASMITH |212 Clark St|11/21/18 |Yes. 33 Blart Ave
ASMITH |33 Blart Ave|11/20/18 |Yes. 212 Clark St
My guess is that my script finds a match in Outlook, and then appends that value to the end of a nested list. What I would like it to do is to match it in the row/nested list where it was actually matched. Apologies for the long post and thank you to those who read it.
