为何CSV转TXT的Python脚本在Windows 2019服务器报Traceback错误？

问题描述 #

Python脚本可在本地工作站正常将指定目录下的CSV文件转换为ASCII文本，但在Windows 2019服务器运行时触发以下错误：

C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin>python convert_csv_to_txt.py
Traceback (most recent call last):
  File "C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin\\convert_csv_to_txt.py", line 49, in <module>
    main()
  File "C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin\\convert_csv_to_txt.py", line 44, in main
    txt_file = csv_to_txt(os.path.join(directory, file))
  File "C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin\\convert_csv_to_txt.py", line 20, in csv_to_txt
    for row in reader:
_csv.Error: line contains NUL

原脚本代码：

import os
import csv


def csv_to_txt(csv_file):
  """Converts a CSV file to a TXT file.

  Args:
    csv_file: The path to the CSV file.

  Returns:
    The path to the created TXT file.
  """
  txt_file = csv_file[:-4] + ".txt"


  with open(csv_file, "r") as csvfile:
    reader = csv.reader(csvfile)
    with open(txt_file, "w") as txtfile:
      for row in reader:
        txtfile.write(", ".join(row) + "\n")

  return txt_file

def remove_old_txt_files(directory):
  """Removes all old CSV files from the specified directory.

  Args:
    directory: The directory to remove the CSV files from.
  """
  for file in os.listdir(directory):
    if file.endswith(".txt"):
      os.remove(os.path.join(directory, file))

def main():
  directory = "C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin"

  # Remove all old TXT files.
  remove_old_txt_files(directory)

  # Convert all CSV files to TXT files.
  for file in os.listdir(directory):
    if file.endswith(".csv"):
      txt_file = csv_to_txt(os.path.join(directory, file))
      print("Converted {} to {}".format(file, txt_file))


if __name__ == "__main__":
  main()

问题原因 #

• NUL字符（ASCII码0）属于二进制数据，说明服务器上的CSV文件并非纯文本格式：可能是带BOM的UTF-16编码，或者文件生成/传输过程中混入了二进制数据，也可能是文件损坏
• 本地工作站的CSV文件为纯ASCII/UTF-8无BOM格式，csv模块可正常解析，而服务器文件中的NUL字节触发了csv模块的错误

修复后的代码 #

import os
import csv


def csv_to_txt(csv_file):
    """将CSV文件转换为TXT文件"""
    txt_file = csv_file[:-4] + ".txt"

    # 读取时过滤NUL字符，同时指定编码（utf-8-sig适配带BOM的UTF-8文件，若为UTF-16则改为"utf-16"）
    with open(csv_file, "r", encoding="utf-8-sig") as csvfile:
        # 用生成器过滤每行的NUL字符
        filtered_lines = (line.replace('\x00', '') for line in csvfile)
        reader = csv.reader(filtered_lines)
        with open(txt_file, "w", encoding="utf-8") as txtfile:
            for row in reader:
                txtfile.write(", ".join(row) + "\n")

    return txt_file

def remove_old_txt_files(directory):
    """删除指定目录下的旧TXT文件"""
    for file in os.listdir(directory):
        if file.endswith(".txt"):
            os.remove(os.path.join(directory, file))

def main():
    directory = "C:\\Program Files\\SplunkUniversalForwarder\\etc\\apps\\TRENDMICRO_CSV_TO_TXT_SCRIPT\\bin"

    # 删除旧TXT文件
    remove_old_txt_files(directory)

    # 转换所有CSV文件，添加异常捕获避免单个文件错误中断脚本
    for file in os.listdir(directory):
        if file.endswith(".csv"):
            try:
                txt_file = csv_to_txt(os.path.join(directory, file))
                print(f"已转换 {file} 到 {txt_file}")
            except Exception as e:
                print(f"转换 {file} 失败: {str(e)}")


if __name__ == "__main__":
    main()

额外说明 #

1. 编码适配：如果CSV是UTF-16编码，将encoding="utf-8-sig"替换为encoding="utf-16"
2. 异常捕获：新增的try-except块确保单个文件转换失败时，脚本会继续处理其他文件
3. NUL过滤：通过生成器实时过滤每行的NUL字符，避免csv模块解析报错

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