Description of the Code
My python code downloads Email attachments, saves them in specific folders and then reads those csv files and merges them into one pandas dataframe in order to do some formating. Then it write the dataframe back to one combined excel file.
My function that does the main job has 4 arguments:
- EmailPath is the path where the email attachments are saved for archiving reasons
- TempPath is the path were the attachments are saved, then looped through and deleted after they have been merged to the masterfile.
- FilePath is the path were the masterfile is located. The data of all attachments is merged to this file.
- ArchivePath is the path were the previouse masterfile is moved to, so that no data can be accidentally lost.
Those four paths are saved as default, but the user can change them in the GUI if necessary. If no changes are needed, the user can simply click the button and the code will use the default paths.
The Problem
When running the code in PyCharm everything works without problems. If everything worked fine a window will open that says, that the transfer of data was successful. However, when I create the .exe (I explained how I do it here), and execute it the pop up window that should say "successful" says "none". I can't properly debug, because the problem only occurs when running the .exe, not the python code in pycharm.
Does anyone know how to fix this?
The Code
Here is my code. I know usually one should provide a shorter version of the code, but I don't know where the error is, therefore I provide all of it.
import PySimpleGUI as sg
import os.path
import os
import shutil
import pandas as pd
import win32com.client
from datetime import datetime
import xlsxwriter
def download(emailfolder, path, last_date):
outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
inbox = outlook.Folders(emailfolder).Folders('Posteingang')
# Access to the email in the inbox
messages = inbox.Items
messages = messages.Restrict("[ReceivedTime] >'" + format(last_date, "%d.%m.%Y") + "'")
messages.Sort("[ReceivedTime]", True)
# to loop through the email in the inbox
i = 0
for message in messages:
message_time = last_date
subject = "nothing"
try:
subject = message.subject # get the subject of the email
# if you use messages.GetFirst() earlier
# message = messages.GetNext()
message_time = message.ReceivedTime
message_time = message_time.date()
except:
# if you use messages.GetFirst() earlier
message = messages.GetNext()
if message_time > last_date and subject == "ÖWA Report" and message.Attachments.Count > 0:
try:
attachments = message.Attachments
attachment = attachments.Item(1)
# the name of attachment file
attachment_name = str(attachment).lower()
attachment.SaveASFile(path + '\\' + attachment_name)
attachment.SaveASFile("W:/Z_GMF/TABLEAU/ONLINE/ORFPlayer/Temp/Data/" + attachment_name)
except:
print('Problem at ' + str(i))
i += 1
def format_df(df_, filename):
df_["ID"] = filename.split(' - gesamt')[0].replace("GMFInternetMails", "")
if "tagesreport" in filename:
df_["Zeitraum"] = "Tag"
df_["Weekday"] = pd.to_datetime(df_["DATUM"], dayfirst=True).dt.day_name()
df_["Day"] = pd.to_datetime(df_["DATUM"], dayfirst=True).dt.day
df_["Month"] = pd.to_datetime(df_["DATUM"], dayfirst=True).dt.month
df_["Year"] = pd.to_datetime(df_["DATUM"], dayfirst=True).dt.year
return df_
def update_data(emailpath, temppath, filepath, archivepath):
Error_mess = []
EmailFolder = emailpath.split("/")[-2]
# read all_data file into pandas array
main_df = pd.read_excel(filepath)
main_df.to_excel(archivepath + str(len(main_df.index)) + ".xlsx", engine='xlsxwriter', index=False)
min_date = main_df['DATUM'].iloc[-1].split(" ")[0]
min_date = datetime.strptime(min_date, "%d.%m.%Y").date()
download(EmailFolder, emailpath, min_date)
# create initial dataframe that needs to be appended
for filename1 in os.listdir(temppath):
f = os.path.join(temppath, filename1)
if os.path.isfile(f) and f.endswith('.csv'):
df = pd.read_csv(f, sep=";", header=0)
df = format_df(df, filename1)
# temp file not needed anymore, therefore deleted
try:
os.unlink(f)
except Exception as e:
Text = 'Failed to delete %s. Reason: %s' % (f, e)
Error_mess.append(Text)
# read all files in Folder into Pandas Data array and merge
iLoop = 0
for filename in os.listdir(temppath):
f = os.path.join(temppath, filename)
if os.path.isfile(f) and f.endswith('.csv'):
df_temp = pd.read_csv(f, sep=";", low_memory=False)
df_temp = format_df(df_temp, filename)
df = pd.concat([df, df_temp], ignore_index=True)
try:
os.unlink(f)
except Exception as e:
Text = 'Failed to delete %s. Reason: %s' % (f, e)
Error_mess.append(Text)
iLoop += 1
if iLoop % 500 == 0:
df_tempsave = pd.concat([main_df, df]).drop_duplicates().reset_index(drop=True)
df_tempsave = df_tempsave.sort_values(by=["Year", "Month", "Day"])
df_tempsave.to_excel(temppath + str(len(df_tempsave)) + ".xlsx", engine='xlsxwriter', index=False)
# merge arrays
df_all = pd.concat([main_df, df]).drop_duplicates().reset_index(drop=True)
df_all = df_all.sort_values(by=["Year", "Month", "Day"])
df_all.to_excel(filepath, engine='xlsxwriter', index=False)
for filename in os.listdir(temppath):
file_path = os.path.join(temppath, filename)
try:
if os.path.isfile(file_path) or os.path.islink(file_path):
os.unlink(file_path)
elif os.path.isdir(file_path):
shutil.rmtree(file_path)
except Exception as e:
Text = 'Failed to delete %s. Reason: %s' % (file_path, e)
Error_mess.append(Text)
if len(Error_mess) == 0:
return 'Einschreiben der neuen Daten erfolgreich!'
else:
return " / ".join(Error_mess)
def open_modal_window(windowtext):
layout = [[sg.Text(windowtext, key="new")]]
window = sg.Window("Second Window", layout, modal=True)
while True:
event, values = window.read()
if event == "Exit" or event == sg.WIN_CLOSED:
break
window.close()
def main():
EmailPath = "W:/Z_GMF/TABLEAU/ONLINE/ORFPlayer/GMF Internet/"
TempPath = "W:/Z_GMF/TABLEAU/ONLINE/ORFPlayer/Temp/Data/"
FilePath = "W:/Z_GMF/TABLEAU/ONLINE/ORFPlayer/AllData.xlsx"
ArchivePath = "W:/Z_GMF/TABLEAU/ONLINE/ORFPlayer/AllData/AllData_"
# First the window layout in 2 columns
# column 1
layout = [
[sg.Text("Dieses Programm liest zunächst das Datum des neuesten Datensatzes aus dem angegebenen Dateipfad aus "
"und lädt neuere Dateianhänge von ÖWA Reports vom Email Server herunter. Dann werden diese Daten zur "
"bestehenden Liste hinzugefügt.", size=(70, 3), font=("Roboto", 11))],
[sg.Text("Standardpfade:", size=(70, 1), font=("Roboto", 11))],
[sg.Text("Pfad zu Email Anhängen: " + EmailPath + "\n"
"Pfad zu temporären Dateien: " + TempPath + "\n"
"Pfad zum Master-File: " + FilePath + "\n"
"Pfad zum Archiv: " + ArchivePath,
size=(70, 5), font=("Roboto", 10))],
[sg.Text("Bei Abweichungen wählen Sie bitte neue Pfade, andernfalls klicken Sie sofort auf \'Eintragen\'.",
size=(70, 2), font=("Roboto", 10))],
[
sg.Text("Neuer Dateipfad Email (optional):", size=(30, 1), font=("Roboto", 11)),
sg.Input(),
sg.FileBrowse(key="-Email-")
],
[
sg.Text("Neuer Dateipfad Temp (optional):", size=(30, 1), font=("Roboto", 11)),
sg.Input(),
sg.FileBrowse(key="-Temp-")
],
[
sg.Text("Neuer Dateipfad Masterfile (optional):", size=(30, 1), font=("Roboto", 11)),
sg.Input(),
sg.FileBrowse(key="-Master-")
],
[
sg.Text("Neuer Dateipfad Archiv (optional):", size=(30, 1), font=("Roboto", 11)),
sg.Input(),
sg.FileBrowse(key="-Archiv-")
],
[sg.Button("Eintragen")],
[sg.Text()],
[sg.Text("Developer: Carina Heinreichsberger, E-mail: carina.heinreichsberger@orf.at", font=("Arial", 9),
text_color='Slategray1')],
[sg.Text("last Update: October 2022", font=("Arial", 9),
text_color='Slategray1')],
]
window = sg.Window("Update ÖWA Report Data", layout)
while True:
event, values = window.read()
if event == sg.WIN_CLOSED or event == "Exit":
break
elif event == "Eintragen":
open_modal_window("Download und Aktualisierung der Datenliste wurde gestartet. "
"Der Prozess kann einige Minuten dauern. \n"
"Sie können dieses Fenster schließen.")
text = update_data(EmailPath, TempPath, FilePath, ArchivePath)
open_modal_window(text)
elif event == "-Email-":
EmailPath = values[event]
elif event == "-Temp-":
TempPath = values[event]
elif event == "-File-":
FilePath = values[event]
elif event == "-Archive-":
ArchivePath = values[event]
window.close()
if __name__ == '__main__':
main()
Research so far
There is not too much that I found that was useful, because most people ran into error messages - which I don't get. I found this question, however this solution has to do with one specific expression the person used, and they needed to sort their path in the end, which I already did. This is my PATH:
Based on another question I tried to execute it from the power shell to maybe get more insight on possible errors, but nothing is printed.
