Untitled
unknown
plain_text
a year ago
7.2 kB
4
Indexable
import os import pandas as pd import shutil import csv from datetime import datetime, date import win32com.client as win32 import re # Function to merge CSV files into a single XLSX file def merge_csv_to_xlsx(folder_path, output_file, log_file, encoding): # Find all CSV files modified today in the specified folder csv_files = [f for f in os.listdir(folder_path) if f.endswith('.csv') and date.fromtimestamp(os.path.getmtime(os.path.join(folder_path, f))) == date.today()] # If no CSV files are found, print a message and return if not csv_files: print(f"No CSV files found in the specified folder: {folder_path}") return # Create an empty DataFrame to hold merged data merged_data = pd.DataFrame() # Loop through the found CSV files and concatenate them into the merged_data DataFrame for csv_file in csv_files: file_path = os.path.join(folder_path, csv_file) data = pd.read_csv(file_path, encoding=encoding) merged_data = pd.concat([merged_data, data], ignore_index=True, sort=False) # Define the output path and create any necessary directories output_path = os.path.join('Z:\\ADEN Delays\\CARRIER', output_file) os.makedirs(os.path.dirname(output_path), exist_ok=True) # Save the merged data to an Excel file merged_data.to_excel(output_path, index=False) # Write the merge operation details to the log file with open(log_file, 'a') as f: f.write(f"Merged these CSV files from \"{folder_path}\":\n") for csv_file in csv_files: f.write(f"\"{os.path.join(folder_path, csv_file)}\"\n") f.write(f"into \"{output_file}\" at \"{output_path}\"\n\n") print(f"Merged files successfully. Output saved to {output_path}") def process_folder(folder_path, output_file, log_file, use_excel=False): # Find all files modified today in the specified folder files = [f for f in os.listdir(folder_path) if date.fromtimestamp(os.path.getmtime(os.path.join(folder_path, f))) == date.today()] # Loop through the found files for file in files: file_path = os.path.join(folder_path, file) output_path = os.path.join('Z:\\ADEN Delays\\CARRIER', output_file) # Create any necessary directories for the output path os.makedirs(os.path.dirname(output_path), exist_ok=True) # If the file is an Excel file, copy it to the output path if file_path.lower().endswith(('.xls', '.xlsx')): shutil.copy(file_path, output_path) elif file_path.lower().endswith('.csv') and use_excel: # If the file is a CSV and we want to use Excel, convert it excel = win32.gencache.EnsureDispatch('Excel.Application') excel.Visible = False wb = excel.Workbooks.Open(file_path) wb.SaveAs(output_path, FileFormat=51) # FileFormat=51 is for xlsx wb.Close() excel.Quit() # Write the file processing details to the log file with open(log_file, 'a') as f: f.write(f"Processed \"{file_path}\" to \"{output_path}\"\n") print(f"Processed {os.path.basename(folder_path)} folder. Latest file saved to {output_path}") # Define the base folder path for carriers folder_path_carriers = r"Z:\\ADEN Delays" # Update the log file name to include the date and time current_time = datetime.now().strftime("%Y%m%d_%H%M%S") log_file = f"log_{current_time}.txt" # Process the Cosco and OOCL files with Excel in the background process_folder(os.path.join(folder_path_carriers, 'COSCO'), "COSCO.xlsx", log_file, use_excel=True) process_folder(os.path.join(folder_path_carriers, 'OOCL'), "OOCL.xlsx", log_file, use_excel=True) # Copy the XLSX files from ONE and CMA as they are process_folder(os.path.join(folder_path_carriers, 'ONE'), "ONE.xlsx", log_file) process_folder(os.path.join(folder_path_carriers, 'CMA'), "CMA.xlsx", log_file) # Maersk is already being processed correctly with the existing merge_csv_to_xlsx function # Function to send an email with the report in the email body formatted with HTML def send_email_report(log_file, recipient_emails): # Initialize Outlook instance outlook = win32.Dispatch('outlook.application') mail = outlook.CreateItem(0) mail.To = recipient_emails # Set the recipient email # Read the log file and format its contents with HTML with open(log_file, 'r') as f: lines = f.readlines() # Initialize HTML email body html_body = "<html><body><p style='font-family:Calibri; font-size:12pt; color:black;'>Report:</p>" for line in lines: # Use regex to find text within quotes quoted_text = re.findall(r'"([^"]+)"', line) for text in quoted_text: # Replace the quoted text with a styled version styled_text = f"<span style='font-family:Consolas, monospace; font-size:8pt; color:#5A1E02;'>{text}</span>" line = line.replace(f'"{text}"', styled_text) # Add the rest of the text with standard Outlook font html_body += f"<span style='font-family:Calibri; font-size:12pt; color:black;'>{line.strip()}</span><br>" # ASCII Art placeholder ascii_art = r""" <pre style='font-family:Consolas, monospace; font-size:10pt;'> ___ ___ _______ ___ ________ ________ |\ \|\ \ |\ ___ \ |\ \ |\ ___ \ |\_____ \ \ \ \\\ \ \ \ __/| \ \ \ \ \ \\ \ \ \|___/ /| \ \ __ \ \ \ \_|/__ \ \ \ \ \ \\ \ \ / / / \ \ \ \ \ \ \ \_|\ \ \ \ \ \ \ \\ \ \ / /_/__ \ \__\ \__\ \ \_______\ \ \__\ \ \__\\ \__\ |\________\ \|__|\|__| \|_______| \|__| \|__| \|__| \|_______| </pre> """ html_body += ascii_art # Add the ASCII art to the email body html_body += "</body></html>" # Set the email body and subject mail.HTMLBody = html_body mail.Subject = 'Python log for Heinz carrier report conversion - test 2' # Display the email mail.Display(True) # Set to False if you want to send the email directly # Define the paths and output files folder_path_maersk = r"Z:\\Maersk" output_file_maersk = "MAERSK.xlsx" # Merge CSV files to XLSX for Maersk merge_csv_to_xlsx(folder_path_maersk, output_file_maersk, log_file, 'utf-8-sig') # Remove COSCO and OOCL from the dictionary carrier_encodings = { 'CMA': None, # Assuming no CSV files to decode or that they are already in utf-8 'ONE': None, # Assuming no CSV files to decode or that they are already in utf-8 } folder_path_carriers = r"Z:\\ADEN Delays" for carrier, encoding in carrier_encodings.items(): carrier_folder_path = os.path.join(folder_path_carriers, carrier) output_file = f"{carrier}.xlsx" # Use a default encoding if none is specified encoding = encoding if encoding is not None else 'utf-8' process_folder(carrier_folder_path, output_file, log_file, encoding) # Send an email with the report recipient_emails = " add emils here " send_email_report(log_file, recipient_emails)
Editor is loading...
Leave a Comment