Python处理含分隔符的CSV邮箱列：自动修复格式异常方案咨询

Great question—this is a super common pain point with delimited files when a field accidentally contains the delimiter. Let's walk through two straightforward Python approaches to fix this, both optimized for large files so you don't run into memory issues.

Approach 1: Fix by Field Count (Simple & Intuitive) #

Your normal rows have exactly 4 fields (split by |), which means 3 delimiters total. The broken rows have 5 fields because the email contains an extra |. We can use this difference to target and fix the bad entries:

input_file = "large_data.csv"
output_file = "fixed_large_data.csv"

with open(input_file, 'r', encoding='utf-8') as infile, open(output_file, 'w', encoding='utf-8') as outfile:
    # Write the header first since it's always valid
    header = infile.readline()
    outfile.write(header)
    
    for line in infile:
        line_clean = line.rstrip('\n')
        fields = line_clean.split('|')
        
        # Normal row: 4 fields (3 delimiters)
        if len(fields) == 4:
            outfile.write(line_clean + '\n')
        # Broken row: 5 fields (extra | in email)
        elif len(fields) == 5:
            # Merge the split email parts (fields 2 and 3, 0-indexed)
            fixed_email = f"{fields[2].strip()}{fields[3].strip()}"
            # Reconstruct the valid row
            fixed_line = f"{fields[0].strip()}|{fields[1].strip()}|{fixed_email}|{fields[4].strip()}"
            outfile.write(fixed_line + '\n')
        # Catch unexpected cases for manual review
        else:
            print(f"Warning: Unhandled line format - {line_clean}")

How this works:

• We read the file line-by-line (critical for large CSVs to avoid loading everything into memory)
• We check the number of split fields to identify broken rows
• We merge the two split email segments, strip any extra whitespace, and rebuild a valid 4-field row
• Any unexpected line formats get flagged for manual check

Approach 2: Fix with Regular Expressions (Precise Matching) #

If you want more control over matching the exact row structure, regex works great. We'll target the exact pattern of broken rows where the email is split by an extra |:

import re

input_file = "large_data.csv"
output_file = "fixed_large_data.csv"

# Regex pattern to match broken rows: captures Name, Address, split email parts, Paid Value
broken_row_pattern = re.compile(r'^(.*?)\|(.*?)\|(.*?)\|(.*?)\|(.*)$')

with open(input_file, 'r', encoding='utf-8') as infile, open(output_file, 'w', encoding='utf-8') as outfile:
    header = infile.readline()
    outfile.write(header)
    
    for line in infile:
        line_clean = line.rstrip('\n')
        match = broken_row_pattern.match(line_clean)
        
        if match:
            # Extract all captured groups and clean up whitespace
            name = match.group(1).strip()
            address = match.group(2).strip()
            email_part1 = match.group(3).strip()
            email_part2 = match.group(4).strip()
            paid_value = match.group(5).strip()
            
            # Fix the email and rebuild the row
            fixed_email = f"{email_part1}{email_part2}"
            fixed_line = f"{name}|{address}|{fixed_email}|{paid_value}"
            outfile.write(fixed_line + '\n')
        else:
            # Write valid rows as-is
            outfile.write(line_clean + '\n')

How this works:

• The regex explicitly matches rows with 4 delimiters (5 fields)
• It captures each segment of the row, including the split email parts
• We merge the email parts and write back a valid row
• Valid rows pass through unchanged

Key Notes for Production Use: #

1. Test First: Run both scripts on a small sample of your data first to confirm the fixes work as expected
2. Log Unhandled Rows: For the first approach, consider writing unexpected lines to a separate log file instead of just printing them
3. Handle Edge Cases: If you ever see emails with multiple | characters, you can adjust the logic to count total delimiters and merge all middle segments for the email field
4. Encoding: Make sure to use the correct encoding for your file (e.g., latin-1 if needed) to avoid garbled text

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