社区首页 >问答首页 >从具有特定主题行的所有outlook电子邮件中提取变量数据，然后从正文中获取日期

问从具有特定主题行的所有outlook电子邮件中提取变量数据，然后从正文中获取日期
EN

Stack Overflow用户

提问于 2021-11-05 08:36:07

回答 1查看 53关注 0票数 1

我每天都会收到一封电子邮件，上面写着当天售出的水果数量。虽然我现在已经想出了一些代码来记录未来的相关数据，但我一直无法向后执行。

数据存储在电子邮件正文中，如下所示：

Date of report:,01-Jan-2020
Apples,8
Pears,5
Lemons,7
Oranges,9
Tomatoes,6
Melons,3
Bananas,0
Grapes,4
Grapefruit,8
Cucumber,2
Satsuma,1

我想让代码做的是首先搜索我的电子邮件并找到与特定主题匹配的电子邮件，逐行迭代并找到我正在搜索的变量，然后将它们记录在一个数据帧中，并将“报告日期”记录在日期列中，并转换为格式："%m-%d-%Y“。

我认为我可以通过对我编写的代码进行一些修改来实现这一点，以便继续跟踪它：

# change for the fruit you're looking for
Fruit_1 = "Apples"
Fruit_2 = "Pears"

outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.GetDefaultFolder(6) 
messages = inbox.Items
messages.Sort("[ReceivedTime]", True)

# find data email
for message in messages:
    if message.subject == 'FRUIT QUANTITIES':
        if Fruit_1 and Fruit_2 in message.body: 
            data = str(message.body)
            break
        else:
            print('No data for', Fruit_1, 'or', Fruit_2, 'was found')
            break

fruitd = open("fruitd.txt", "w") # copy the contents of the latest email into a .txt file
fruitd.write(data)
fruitd.close()

def get_vals(filename: str, searches: list) -> dict:
    #Searches file for search terms and returns the values
    dct = {}
    with open(filename) as file:
        for line in file:
            term, *value = line.strip().split(',')
            if term in searches:
                dct[term] = float(value[0]) # Unpack value 
    # if terms are not found update the dictionary w remaining and set value to None
    if len(dct.keys()) != len(searches):
        dct.update({x: None for x in search_terms if x not in dct})
    return dct


searchf = [
    Fruit_1, 
    Fruit_2
] # the list of search terms the function searches for

result = get_vals("fruitd.txt", searchf) # search for terms 
print(result)

# create new dataframe with the values from the dictionary
d = {**{'date':today}, **result}
fruit_vals = pd.DataFrame([d]).rename(columns=lambda z: z.upper())
fruit_vals['DATE'] = pd.to_datetime(fruit_vals['DATE'], format='%d-%m-%Y')
print(fruit_vals)

我创建了一个名为'fruitd‘的.txt，因为我不确定如何才能以其他方式遍历电子邮件正文。不幸的是，我不认为为过去的每一封电子邮件创建一个.txt是真正可行的，我想知道是否有更好的方法来做到这一点？

任何建议或指示都将非常受欢迎。

**EDIT理想情况下希望获得搜索列表中的所有变量；因此，如果需要，Fruit_1 & Fruit_2可以将其扩展为Fruit_3 + Fruit_4 (等)。

python

pandas

numpy

outlook

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-11-05 09:47:11

#PREP THE STUFF
Fruit_1 = "Apples"
Fruit_2 = "Pears"
SEARCHF = [
    Fruit_1, 
    Fruit_2
]

#DEF THE STUFF
# modified to take a list of list of strs as `report` arg
# apparently IDK how to type-hint; type-hinting removed
def get_report_vals(report, searches):
    dct = {}
    for line in report:
        term, *value = line
        # `str.casefold` is similar to `str.lower`, arguably better form
        # if there might ever be a possibility of dealing with non-Latin chars
        if term.casefold().startswith('date'):
            #FIXED (now takes `date` str out of list)
            dct['date'] = pd.to_datetime(value[0])
        elif term in searches:
            dct[term] = float(value[0])
    if len(dct.keys()) != len(searches):
        # corrected (?) `search_terms` to `searches`
        dct.update({x: None for x in searches if x not in dct})
    return dct


#DO THE STUFF
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.GetDefaultFolder(6) 
messages = inbox.Items
messages.Sort("[ReceivedTime]", True)

results = []

for message in messages:
    if message.subject == 'FRUIT QUANTITIES':
        # are you looking for:
        #  Fruit_1 /and/ Fruit_2
        # or:
        #  Fruit_1 /or/  Fruit_2
        if Fruit_1 in message.body and Fruit_2 in message.body:
            # FIXED
            data = [line.strip().split(",") for line in message.body.split('\n')]
            results.append(get_report_vals(data, SEARCHF))
        else:
            pass

fruit_vals = pd.DataFrame(results)
fruit_vals.columns = map(str.upper, fruit_vals.columns)