如何从Outlook邮件正文提取复制粘贴表格并保留原格式？

问题描述 #

需要从Outlook邮件正文中提取由Excel复制粘贴而来的表格数据（发件人未以HTML格式发送表格，表格包含空列）。目前已成功提取数据，但提取后无法保留原表格格式，首行之外的每行均被拆分为多行，请求解决。

（原提问附邮件表格截图，此处省略外链）

当前实现代码 #

# Import necessary libraries
import pandas as pd
import os
import pathlib
import win32com.client
import zipfile
import shutil 
from io import StringIO
from datetime import datetime, timedelta
import re
from bs4 import BeautifulSoup

os.chdir(r"D:\Output")

#<<<<<<<<<<<<<<<<<<<<<<< Mail data Fetching>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# Define a set to store unique IDs of processed emails
processed_emails = set()

# Define the output directory path for saving email attachments
output_dir = pathlib.Path.cwd() / "D:\Output"

# Set up Outlook client
outlook = win32com.client.Dispatch('Outlook.Application').getNamespace('MAPI')
inbox = outlook.getDefaultFolder(6)
messages = inbox.items


# Define the time range for emails to be processed (usually within the last 24 hours)
start_date = datetime.now() - timedelta(days=1)
start_date1 = start_date.replace(hour=16, minute=0, second=0).strftime('%d/%m/%Y %H:%M %p')
end_date = datetime.now()
end_date1 = end_date.replace(hour=23, minute=0, second=0).strftime('%d/%m/%Y %H:%M %p')
messages = messages.Restrict("[ReceivedTime] >= '" + start_date1 + "' And [ReceivedTime] <= '" + end_date1 + "'")


# Create a directory to save subject-specific data
base_output_dir = pathlib.Path.cwd() / "Output_Folders"
base_output_dir.mkdir(parents=True, exist_ok=True)

import os
from datetime import datetime

# List of subjects to process
subjects_to_process = ["NEW SLOT",]

# Function to create a unique subfolder for each ZIP file
def create_unique_subfolder(folder, filename):
    base_name, ext = os.path.splitext(filename)
    timestamp = datetime.now().strftime('%Y%m%d%H%M%S')
    subfolder_name = f"{base_name}_{timestamp}"
    subfolder_path = os.path.join(folder, subfolder_name)
    os.makedirs(subfolder_path)
    return subfolder_path

# Function to delete subfolders and files within a folder
def delete_folder_contents(folder):
    for item in os.listdir(folder):
        item_path = os.path.join(folder, item)
        if os.path.isfile(item_path):
            os.remove(item_path)
        elif os.path.isdir(item_path):
            shutil.rmtree(item_path)


import pandas as pd
from io import StringIO

def extract_table_data_from_body(email_body):
    # Find the line that contains the table structure 
    start_index = email_body.find('Merchant')

    # Find the last occurrence of 'Physical Installation' to identify the end of the table
    end_index = email_body.rfind('--')
    
    # Check if both start and end indices are found
    if start_index != -1 and end_index != -1:
        # Extract the substring containing the table structure
        table_structure = email_body[start_index:end_index]

        # Use pandas to read the table
        try:
            df = pd.read_csv(StringIO(table_structure), sep='\t')
            
            # Create a CSV string from the DataFrame
            csv_data = df.to_csv(index=False)
            
            return csv_data
        except Exception as e:
            print(f"Error reading table: {e}")
            return None
    else:
        print("Start or end index not found.")
        return None


for subject in subjects_to_process:
    subject_output_dir = base_output_dir / subject  # Create a folder for the current subject
    subject_output_dir.mkdir(parents=True, exist_ok=True)

    found_email = False  # Flag to check if an email with the current subject is found

    for message in messages:
        # Get the unique ID of the email
        email_id = message.EntryID

        # Check if this email has already been processed
        if email_id in processed_emails:
            continue  # Skip this email

        # Check for emails with the current subject (case-insensitive and partial matching)
        if subject.lower() in message.Subject.lower() and message.SenderEmailAddress != 'rxxxx@gmail.com':
            found_email = True
            body = message.body
            attachments = message.Attachments
            print(f"Found email with subject: {subject}")
            
            # Handle 'Pine Labs SLOT' subject
            if subject.lower() == 'new slot':
                # Extract table data from the email body
                table_data = extract_table_data_from_body(body)
            
                if table_data:
                    # Print a statement if table data is found in the email body
                    print("Table data found in the email body.")
            
                    # Create a unique CSV file with subject name and timestamp
                    csv_filename = f"{subject}_{datetime.now().strftime('%Y%m%d%H%M%S')}.csv"
                    csv_filepath = subject_output_dir / csv_filename
            
                    # Save table data to the CSV file
                    with open(csv_filepath, 'w') as csv_file:
                        # Write the table data to the CSV file
                        csv_file.write(table_data)
            
                    print(f"Table data stored in CSV: {csv_filename}")


            for attach in attachments:
                if attach.FileName.endswith('.xlsx') or attach.FileName.endswith('.xls') or attach.FileName.endswith('.csv'):
                    try:
                        attach.SaveAsFile(str(subject_output_dir / attach.FileName))
                        print(f"Saved attachment: {attach.FileName}")
                    except Exception as e:
                        print(f"Unable to save attachment '{attach.FileName}' due to error: {e}")
                elif attach.FileName.endswith('.zip'):
                    # Create a unique subfolder for this ZIP file
                    subfolder_path = subject_output_dir / f"{attach.FileName}_{datetime.now().strftime('%Y%m%d%H%M%S')}"
                    subfolder_path.mkdir(exist_ok=True)

                    # Save the .zip file within the subfolder
                    zip_file_path = subfolder_path / attach.FileName
                    attach.SaveAsFile(str(zip_file_path))
                    print(f"Saved attachment: {attach.FileName}")

                    # Extract the contents of the .zip file within the subfolder
                    with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:
                        for file_info in zip_ref.infolist():
                            extracted_file_name = subfolder_path / file_info.filename
                            zip_ref.extract(file_info, subfolder_path, extracted_file_name)

                    # Delete the original zip file after extracting
                    os.remove(zip_file_path)

                else:
                    print(f"Skipped attachment '{attach.FileName}' as it's not in the expected format.")
    
    # Check if no email with the current subject was found
    if not found_email:
        print(f"No email with subject '{subject}' received in the specified time period.")

解决方案 #

问题原因 #

Excel表格复制到纯文本邮件后，列分隔符是连续空格而非制表符（\t），原代码用sep='\t'读取无法识别正确列边界，导致行被错误拆分；部分单元格内的换行也会加剧格式混乱。

修改后的核心函数 #

重点调整extract_table_data_from_body函数，适配纯文本表格的格式：

def extract_table_data_from_body(email_body):
    # 定位表格起始和结束位置
    start_index = email_body.find('Merchant')
    end_index = email_body.rfind('--')
    
    if start_index == -1 or end_index == -1:
        print("未找到表格的起始或结束标识")
        return None
    
    table_structure = email_body[start_index:end_index]
    
    # 处理表格文本：将连续空格替换为制表符，过滤空行
    processed_lines = []
    for line in table_structure.splitlines():
        if not line.strip():
            continue
        # 把连续2个及以上空格替换为制表符，模拟列分隔
        cleaned_line = re.sub(r'\s{2,}', '\t', line.strip())
        processed_lines.append(cleaned_line)
    
    cleaned_table = '\n'.join(processed_lines)
    
    try:
        # 读取时强制字符串类型，保留空列
        df = pd.read_csv(StringIO(cleaned_table), sep='\t', dtype=str, keep_default_na=False)
        csv_data = df.to_csv(index=False)
        return csv_data
    except Exception as e:
        print(f"读取表格出错: {e}")
        return None

关键改进点 #

1. 文本预处理：用正则把连续空格转为制表符，还原表格列分隔逻辑
2. 过滤空行：移除无内容的空行，避免干扰表格结构识别
3. 保留空列：通过dtype=str, keep_default_na=False确保空列不被识别为缺失值

额外优化建议 #

如果Outlook邮件同时存在HTML格式正文，可优先解析HTML表格，格式保留更准确：

# 替换原body获取逻辑
body_html = message.HTMLBody
soup = BeautifulSoup(body_html, 'html.parser')
tables = soup.find_all('table')
# 取第一个表格转为DataFrame
if tables:
    df = pd.read_html(str(tables[0]), dtype=str)[0]
    csv_data = df.to_csv(index=False)

内容的提问来源于stack exchange，提问作者Ranjith Kumar