I am attempting to create a python script to iterate over all the rows of a specific column in an excel spredsheet. This column contains dates, I need to compare each of these dates in order to find and return the oldest date in the excel sheet. After which, I will need to modify the data in that row.
I have tried to append the dates into a numpy array as datetime objects, this was working but I cannot traverse through the array and compare the dates. I have also tried to reformat the dates in the excel sheet to datetime objects in python and then compare but I get the following error:
AttributeError: type object 'datetime.datetime' has no attribute 'datetime'
I have tried some other unsuccessful methods. These are the ones where I got closest to achieving what I want. I'm quite lost, please help!
import openpyxl
import numpy as np
import datetime
def main():
wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
sheet = wb.active
def menuSelection():
while True:
menuChoice = input("Please select one of the following options:\n1. Add User\n2.Delete User\n3.Modify User\n")
if menuChoice not in ('1', '2', '3'):
print("The input entered is invalid, please try again")
continue
else:
break
return menuChoice
def findOldestDate():
wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
sheet = wb.active
## startMult = np.empty((0,1000), dtype='datetime64[D]')
## value = datetime.date.strftime("%Y-%m-%d")
for rowNum in range(2, sheet.max_row+1):
status = sheet.cell(row=rowNum, column=5).value
d8 = sheet.cell(row=rowNum, column=6).value
d8_2 = sheet.cell(row=rowNum+1, column=6).value
d8.value = datetime.date.strftime(d8, "%Y-%m-%d")
d8_2.value = datetime.date.strftime(d8_2, "%Y-%m-%d")
d8.number_format = 'YYYY MM DD'
d8_2.number_format = 'YYYY MM DD'
if d8 < d8_2:
oldestDate = d8
elif d8 > d8_2:
oldestDate = d8_2
else:
continue
return oldestDate
## array.append(startMult, date)
##
## while counter < len(array)-1:
##
## if array[counter] < array[counter + 1]:
##
## oldestDate = array[counter]
## counter += 1
##
## elif array[counter] > array[counter + 1]:
##
## oldestDate = array[counter + 1]
## counter += 1
##
## else:
## oldestDate = array[counter]
## continue
##
## return oldestDate
def addUser():
wb = openpyxl.load_workbook("C:\\Users\\User\\Desktop\\Python Telecom Project.xlsx")
sheet = wb.active
dateTimeObj = datetime.date.today()
print("Please enter the following information:\n")
inputName = input("Name: ")
inputNTID = input("NTID: ")
inputRATSID = input("RATSID: ")
inputStatus = input("Status: ")
inputTaskNum = input("Task #: ")
for rowVal in range(2, sheet.max_row+1):
oldestDate = findOldDate()
phoneNum = sheet.cell(row=rowVal, column=1).value
name = sheet.cell(row=rowVal, column=2).value
ntID = sheet.cell(row=rowVal, column=3).value
ratsID = sheet.cell(row=rowVal, column=4).value
status = sheet.cell(row=rowVal, column=5).value
date = sheet.cell(row=rowVal, column=6).value
if date == oldestDate:
name = inputName
ntID = inputNTID
ratsID = inputRATSID
status = inputStatus
date = dateTimeObj
print("\nChanges have been implemented successfully!")
##def deleteUser():
##
##
##
##def modifyUser():
addUser()
This is the current error message:
AttributeError: type object 'datetime.datetime' has no attribute 'datetime'
Prior to this one, I was getting:
can't compare 'str' to 'datetime'
What I want is the oldest date in the column to be returned from this function.
Finding the oldest date can be achieved with a one-liner like the following:
from datetime import datetime as dt
from re import match
def oldest(sheet, column):
"""
Returns the tuple (index, timestamp) of the oldest date in the given sheet at the given column.
"""
return min([(i, dt.strptime(sheet.cell(row=i, column=column).value, '%Y %m %d').timestamp()) for i in range(2, sheet.max_row+1) if isinstance(sheet.cell(row=i, column=column).value, str) and match(r'\d{4}\s\d{2}\s\d{2}', sheet.cell(row=i, column=column).value)], key=lambda x:x[1])
The longer, slower but more readable version follow:
def oldest(sheet, column):
"""
Returns the tuple (index, timestamp) of the oldest date in the given sheet at the given column.
"""
format = '%Y %m %d'
values = list()
for i in range(2, sheet.max_row+1):
if isinstance(sheet.cell(row=i, column=column).value, str) and match(r'\d{4}\s\d{2}\s\d{2}', sheet.cell(row=i, column=column).value):
values.append((i, dt.strptime(sheet.cell(row=i, column=column).value, format).timestamp()))
return min(values, key=lambda x: x[1])
If you need that, you can convert the retrieved timestamp back in the date format you had as shown in this sample session at the python REPL:
>>> row, timestamp = oldest(sheet, 1)
>>> date = dt.utcfromtimestamp(timestamp[1]).strftime('%Y %m %d')
>>> date
'2019 10 31'
>>> row
30