Python生成信件脚本读取.csv时Position列未识别的排查求助

问题场景 #

运行Python脚本读取CSV文件，结合模板为每行数据生成对应机构名称的TXT文件。但Company A对应的输出文件中，Position占位符未被替换（显示为空），其他字段（如Platform、Institution）替换正常。

相关代码与文件内容 #

Python代码 #

import csv
import os

# Define the path to the directory where you want to save the files
directory_path = r'C:\Users'

# Set the current working directory to the specified path
os.chdir(directory_path)

# Define the CSV and sample text file names
csv_file = "sample.csv"
sample_text_file = "sample_template.txt"

# Read the CSV file
with open(csv_file, 'r') as csv_file:
    csv_reader = csv.DictReader(csv_file)
    data = list(csv_reader)

# Read the sample text template
with open(sample_text_file, 'r') as template_file:
    template_text = template_file.read()

# Define the placeholders without delimiters
placeholders = ["Position", "Platform", "Institution", "Optional Paragraph"]

# Process each row from the CSV
for row in data:
    # Create a copy of the template text for this row
    modified_text = template_text

    # Replace placeholders with values from the CSV
    for placeholder in placeholders:
        value = row.get(placeholder, '')
        modified_text = modified_text.replace(f'"{placeholder}"', value)

    # Get the name for the output file from "Institution" column
    output_file_name = f"{row['Institution']}.txt"

    # Write the modified text to the output file
    with open(output_file_name, 'w') as output_file:
        output_file.write(modified_text)

    print(f"File '{output_file_name}' has been created.")

print("All files have been generated in the specified directory.")

模板文件（sample_template.txt） #

\begin{document}

%----------------------------------------------------------------------------------------
%   FIRST PAGE HEADER
%----------------------------------------------------------------------------------------

\vspace{-1em} % Pull the rule closer to the logo

\rule{\linewidth}{1pt} % Horizontal rule

\bigskip\bigskip % Vertical whitespace

%----------------------------------------------------------------------------------------
%   YOUR NAME AND CONTACT INFORMATION
%----------------------------------------------------------------------------------------

\hfill
\begin{tabular}{l @{}}
    \today \bigskip\\ % Date
    NAME \\
        INSTITUTION \\
    A1 \\ % Address
    A2 \\
\end{tabular}

Dear Members of the Recruiting Committee,
\bigskip % Vertical whitespace

%----------------------------------------------------------------------------------------
%   LETTER CONTENT
%----------------------------------------------------------------------------------------

I am writing to apply for the "Position" position that you have advertised on "Platform". 

I believe the skills and competencies I hold would be of great value to the "Institution".


"Optional Paragraph"

Thank you for your time and consideration. 

\bigskip % Vertical whitespace

Sincerely yours,

\vspace{50pt} % Vertical whitespace

\includegraphics[width=0.2\textwidth]{signature.png}

NAME

\end{document}

CSV样本（sample.csv） #

Position,Platform,Institution,Optional Paragraph
Waiter,Facebook,Company A ,
Coder,Twitter,Company B,I am cool.

问题输出（Company A对应文件片段） #

I am writing to apply for the  position that you have advertised on Facebook. 

I believe the skills and competencies I hold would be of great value to the Company A .

原因分析 #

核心问题是CSV文件带有UTF-8 BOM（字节顺序标记）：当CSV文件由Excel等软件以UTF-8格式保存时，会自动在文件开头添加BOM字符（\ufeff）。csv.DictReader读取表头时，第一个字段Position会被识别为\ufeffPosition（带有隐藏BOM字符的键），而非代码中指定的Position。

因此代码中row.get("Position", '')无法匹配到对应的键，返回空字符串，最终导致Position占位符替换后为空。

可以通过在代码中添加print(row.keys())验证：输出的第一个键会是\ufeffPosition，而非预期的Position。

解决方案 #

1. 去除CSV文件的BOM标记 #

修改读取CSV文件的代码，指定编码为utf-8-sig，该编码会自动识别并去除UTF-8 BOM：

with open(csv_file, 'r', encoding='utf-8-sig') as csv_file:
    csv_reader = csv.DictReader(csv_file)
    data = list(csv_reader)

2. 优化文件名（可选） #

CSV样本中Company A后面带有多余空格，会导致生成的文件名包含空格。可以用strip()去除首尾空格：

output_file_name = f"{row['Institution'].strip()}.txt"

内容的提问来源于stack exchange，提问作者Weierstraß Ramirez