如何用Python发送CSV邮件附件并保留数据前导零？

Hey, I’ve dealt with this exact headache before! The issue isn’t actually with your email-sending code—it’s Excel’s default behavior: when it opens a CSV, it automatically converts values that look like pure numbers into numeric types, which strips off any leading zeros. Here are two reliable ways to fix this:

Method 1: Preprocess the CSV to Force Excel to Recognize Text #

Before attaching the file to your email, modify the CSV to add special markers that tell Excel to treat those values as text. This is the most foolproof solution.

Submethod 1.1: Add a Single Quote Prefix #

Wrap the values with leading zeros in a single quote (like '0763808075). Excel will hide the quote but keep the leading zero intact. Use Python’s csv module to update your file:

import csv
import os
from email.mime.application import MIMEApplication

# Replace with your actual file path
resultsfile1 = "path/to/your/original.csv"
temp_file = "path/to/processed_temp.csv"

# Process the CSV to add single quotes to target columns
with open(resultsfile1, 'r', newline='', encoding='utf-8') as infile, \
     open(temp_file, 'w', newline='', encoding='utf-8') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    
    # Write the header (skip this if your CSV has no header)
    header = next(reader)
    writer.writerow(header)
    
    # Process each row: assume column index 1 is the one with leading zeros (adjust as needed)
    for row in reader:
        # Add single quote to preserve leading zero
        row[1] = f"'{row[1]}"
        writer.writerow(row)

# Now attach the processed file instead of the original
with open(temp_file, "rb") as attached_file: 
    part = MIMEApplication(attached_file.read(), Name=os.path.basename(temp_file)) 
part['Content-Disposition'] = f'attachment; filename="{os.path.basename(temp_file)}"' 
msg.attach(part)

# Optional: Delete the temp file after sending
# os.remove(temp_file)

Submethod 1.2: Use Excel-Compatible Text Formatting #

Another trick is to format the value as ="0763808075". Excel will display the full value with leading zeros without showing the surrounding syntax. Just modify the row processing line in the code above:

# Replace the row modification line with this
row[1] = f'="{row[1]}"'

Method 2: Adjust the Attachment's Content-Type (Complementary Fix) #

You can explicitly set the MIME type of the attachment to text/csv and add hints for Excel, though this doesn’t guarantee Excel will follow the instruction. It works best paired with Method 1:

with open(resultsfile1, "rb") as attached_file : 
    part = MIMEApplication(attached_file.read(), Name=os.path.basename(resultsfile1))
    # Set explicit text/csv type with charset
    part.set_type('text/csv')
    part.add_header('Content-Type', 'text/csv; charset=utf-8')
part['Content-Disposition'] = f'attachment; filename="{os.path.basename(resultsfile1)}"' 
msg.attach(part)

Pro Tip: Stick with Method 1 for consistent results—directly modifying the CSV content removes any dependency on Excel’s default settings or email client behavior.

内容的提问来源于stack exchange，提问作者Harry DSOUZA