Python AUTOMATE Email Forward Remove Attachment & Relocate Email
In a previous video , Python create Daily Planets Sky Map for Epheremis, Email & Automate –(https://www.youtube.com/watch?v=1LgB5kyCA08) I use python to attach created pdf document and attach to email and send to myself and others.
Email sent to me with CSV attachment to Hotmail account
In this video I’m sent a CSV file with meter reading data as an attachment to my Outlook account. I want to detach the CSV attachment to a local folder and then process it and upload it to MySQL database on my server, so that I can use it in a Google Data Studio dashboard.
So first I need to move the email across to my gmail account so that can programmatically access my email to manipulate the emails inside For that I need a Gmail APP password to get around the 2 factor authentication ( see How to Send Emails with Python [New Method 2023]-https://www.youtube.com/watch?v=g_j6ILT-X0k).
I haven’t got this setup on my outlook account, so that is why I want to forward to my Gmail account.
I ran across a few issues trying to automate forwarding an email from outlook to gmail.
Forward Emails from Outlook/hotmail to Gmail.
The rason for moving the email to Gmail is that my Gmail account has a app password that allows me to access the account programatically. This does not work with your normal password as it hits the 2 factor authorisation and breaks.
I didn’t want to go through the rigmarole of doing that in my outlook/hotmail account when I already had that setup in Gmail.
It sounds a bit convoluted but I felt comfortable with the method.
Power Automate, Thunderbird Manage Filters & Outlook Live Rules
I initially looked to using Power Automate in Outlook.Live which is online browser access to outlook account to detach the attachment to a local folder- there were no free connections in Power Automate that would :
- Detatch the attachment to a local folder on my PC
- Forward an email to my Gmail account
This is why I think Power Automate is a bit of a waste of time. A lot of simple things you can do with tools like AutoHotKey, but Microsoft make you pay for the pleasure of using Power Automate, and its not that transferable either, unlike AutoHotKey and Python, both of the latter you can compile to .EXE file format and share around, Power Automate is sort of locked into its own environment, a bit like Honeycode and Glade. Glide is far more versatile.
So I looked to using Manage Filters in Thunderbird but Thunderbird is a desktop app and needs the computer on to run. As I may or may not have my computer on at a specific time this was not a fully automated process.
So I went back to Outlook Live and it has a RULES tool that allows you to forward emails, so I used that. As Outlook Live is cloud based it runs all the time so that will take care of that automation.
Detach Email Attachment with Python & Gmail
For the Detaching of the Attachment in Gmail I use a python script to download attachment to folder, then rename the file with today’s date at the front and then move the Email with attachment to a separate folder so that it wont slow down process of saving attachment for the next day’s email. In python Gmail script you cannot move a file from Inbox to another folder, you have to copy it to folder and then delete the email in the inbox.
Upload attachment to MySQL with Python & SQLAlchemy package
After this I use Pandas to modify the format of the attachment so that its in a suitable format to upload to my database using SQLAlchemy.
Excel date number for calculating date, and issues with python datetime package
One issue I came across was the meter reading dates in the Excel & CSV files was a single number, not come across that format before. In Excel you can reformat the number as a date. The number is like 45091. This is the number of days from 1/1/1900. It is a bit like Unix Time or Epoch time that is calculated in seconds units starting form 1/1/1970. You can reformat seconds into Year/Month/Day/Hour/Min/Sec to get timestamps. For DateTimes, the Excel format is only for Date format.
I came across an anomaly with Eccel conversion to Python datetime module conversion. They gave different dates.
Apparently Excel’s conversion assumes the year 1900 is a leap year, whereas the Datetime module manages leap years correctly. Weirdly you still need to deduct 2 days (not 1) for the python Datetime TimeDelta function to give you the same output as the Excel output.
Viewing data in Google Data Studio
After uploading data to MySQL database on my server I’m viewing the data in Google Data Studio. At this point I’ve only got the kWh usage from October 2020 until present on a daily basis at half hour intervals.
I need to also map final prices from Electricity Authority, and upload that into another table in the Database, so I can combine cost & kWh usage.
End Comment
This has been an interesting project and extended my ability to manage emails and attachments in Python. I was very pleased with the outcome.
A bit thrown by the Excel date number & conflict with how its processed in Python, but a great concept like Unix or Epoch time for datetime.
I’ve been working on getting final pricing into a table in my Database and just about have this automated for daily Data, updating using the WITS API to get the final price.
I’ll need to start doing some analysis on this information.
