Python写入CSV遇编码及Excel导入异常问题求助

我来帮你排查下问题所在，你遇到的Excel导入异常和奇怪字符，主要是编码处理逻辑错误和**缺少UTF-16LE必需的字节顺序标记（BOM）**导致的，另外手动拼接TSV格式也容易出现细节问题。咱们一步步解决：

1. 核心问题分析 #

• 错误的编解码操作：你的convert_and_write函数里，input_value是字符串类型（tmpstr是拼接出来的str），却调用了decode('utf-8')——在Python3里str根本没有decode方法，Python2里这样做会把字符串当成UTF-8字节去解码，一旦原内容有非UTF-8的字符，就会产生乱码（就是你看到的奇怪标记）。
• 缺少UTF-16LE的BOM：Excel识别UTF-16编码文件时，必须要有开头的BOM（0xFFFE字节），否则会把编码识别错误，导致列分隔符解析混乱，出现覆盖文本的情况。
• 手动拼接TSV的冗余制表符：你循环每个字段加制表符，最后一列后面会多一个多余的制表符，这也会干扰Excel的列识别。

2. 修正后的解决方案 #

推荐用Python标准库的csv模块来生成规范的TSV文件，同时正确处理UTF-16LE编码和BOM，既避免手动拼接的错误，又能解决内存问题（逐行写入）。

方案一：二进制模式写入+手动添加BOM

import csv

filename = "your_output_file.tsv"

with open(filename, 'wb') as out_file:
    # 先写入UTF-16LE的BOM，让Excel正确识别编码
    out_file.write(b'\xff\xfe')
    # 初始化TSV写入器，指定制表符分隔，Windows风格换行
    writer = csv.writer(out_file, delimiter='\t', lineterminator='\r\n')
    
    for row in rs:
        processed_fields = []
        for value in row:
            # 处理特殊字符：替换引号、制表符、换行/回车
            field_str = str(value).replace('"', '').replace('\t', ' ').replace('\n', '-').replace('\r', ' ')
            processed_fields.append(field_str)
        # 将整行编码为UTF-16LE后写入
        encoded_row = '\t'.join(processed_fields).encode('utf-16le') + b'\r\n'
        out_file.write(encoded_row)

方案二：用codecs简化编码处理

import csv
import codecs

filename = "your_output_file.tsv"

with codecs.open(filename, 'w', 'utf-16le') as out_file:
    # 写入UTF-16的BOM字符
    out_file.write(u'\ufeff')
    writer = csv.writer(out_file, delimiter='\t', lineterminator='\r\n')
    
    for row in rs:
        processed_fields = []
        for value in row:
            field_str = str(value).replace('"', '').replace('\t', ' ').replace('\n', '-').replace('\r', ' ')
            processed_fields.append(field_str)
        writer.writerow(processed_fields)

3. 为什么这样能解决问题？ #

• csv模块：自动处理每行的字段分隔，不会在最后一列多写多余的制表符，保证TSV格式规范。
• BOM标记：0xFFFE（或u'\ufeff'）告诉Excel这是UTF-16LE编码的文件，避免解析错位。
• 正确的编码流程：直接将字符串编码为UTF-16LE，跳过了错误的decode步骤，不会产生乱码字符。

这样生成的文件，用Notepad++查看编码会显示UCS-2 Little Endian（和UTF-16LE等价），Excel导入时也能正确识别列分隔符，不会出现覆盖文本的问题。

内容的提问来源于stack exchange，提问作者Stefano De Rosso