如何从不同编码的文本文件正确导入数据至数据库？

手里有NetApp XCP生成的*.txt文件，还有一份存着文件名列表的Excel文档。需要把这两份数据导入数据库，然后对比两张表的file_name列。麻烦的是这些文件里有非UTF-8字符，而且必须保留原文件路径和文件名（要用来访问文件），不能随便忽略或替换。之前试过用chardet测编码，也试了utf-8、ASCII、MacRoman这些，但中文文件名导入数据库后全变成“?”了。现在要解决的是：从不同编码的文件里把数据导入数据库，能匹配文件名，还得保留原路径和文件名能正常访问。

1. 先准确检测文件编码 #

别盲目试编码，用chardet先测NetApp XCP生成的txt文件的真实编码：

import chardet

def detect_file_encoding(file_path):
    with open(file_path, 'rb') as f:
        raw_data = f.read(10240)  # 读取前10KB数据足够完成编码检测
    return chardet.detect(raw_data)['encoding']

NetApp XCP生成的文件常见编码可能是cp1252、gbk（中文环境下）或MacRoman，但必须实际检测才靠谱。

2. 读取文件时保留原始信息 #

根据数据库支持的字段类型，选两种稳妥的处理方式：

方式一：存储原始字节（最可靠，保证能还原原路径） #

如果数据库支持bytea（PostgreSQL）或BLOB（MySQL）类型字段，直接存储原始路径字节：

# 先定义Django模型示例
from django.db import models
class XcpFile(models.Model):
    file_path_bytes = models.BinaryField()  # 用于存储原始路径字节

# 批量导入函数
def upload_xcp_files(file_path):
    XcpFile.objects.all().delete()
    bulk_group = []
    with open(file_path, 'rb') as f:
        for line in f:
            raw_path = line.rstrip(b'\r\n')  # 去掉行尾的换行符
            bulk_group.append(XcpFile(file_path_bytes=raw_path))
            # 每攒100条批量插入一次
            if len(bulk_group) >= 100:
                XcpFile.objects.bulk_create(bulk_group)
                bulk_group = []
    # 处理剩余的条目
    if bulk_group:
        XcpFile.objects.bulk_create(bulk_group)

后续访问文件时，直接把字节转成对应编码的字符串即可。

方式二：转成UTF-8存储（需保留还原能力） #

如果数据库只能存字符串，用surrogateescape处理转码错误，确保不会丢失字符：

# 定义模型示例
class XcpFile(models.Model):
    file_path = models.CharField(max_length=1024, db_collation='utf8mb4_general_ci')  # 指定utf8mb4编码

# 批量导入函数
def upload_xcp_files_utf8(file_path):
    encoding = detect_file_encoding(file_path)
    XcpFile.objects.all().delete()
    bulk_group = []
    with open(file_path, 'r', encoding=encoding, errors='surrogateescape') as f:
        for line in f:
            path_str = line.rstrip('\r\n')
            bulk_group.append(XcpFile(file_path=path_str))
            if len(bulk_group) >= 100:
                XcpFile.objects.bulk_create(bulk_group)
                bulk_group = []
    if bulk_group:
        XcpFile.objects.bulk_create(bulk_group)

surrogateescape会把无法转成UTF-8的字符用代理对暂存，后续需要还原原编码时可以转回去，不会变成“?”。

3. 处理Excel文件的文件名 #

Excel读取时容易踩编码坑，用pandas多试几种编码：

import pandas as pd

def upload_excel_file(excel_path):
    LeakedFile.objects.all().delete()
    bulk_group = []
    # 先试默认编码，失败则换gbk
    try:
        df = pd.read_excel(excel_path, sheet_name=0)
    except UnicodeDecodeError:
        df = pd.read_excel(excel_path, sheet_name=0, encoding='gbk')
    # 遍历导入数据库
    for _, row in df.iterrows():
        file_name = str(row['file_name']).strip()
        bulk_group.append(LeakedFile(file=file_name))
        if len(bulk_group) >= 100:
            LeakedFile.objects.bulk_create(bulk_group)
            bulk_group = []
    if bulk_group:
        LeakedFile.objects.bulk_create(bulk_group)

4. 文件名匹配要点 #

• 如果存的是原始字节：直接对比字节数据，完全一致才判定匹配
• 如果存的是UTF-8字符串：确保Excel里的文件名也用相同的转码规则处理，比如Excel读取时的编码要和txt文件的转码逻辑一致，避免因编码差异导致匹配失败

5. 数据库关键配置 #

必须把存储文件名的字段设为utf8mb4编码（MySQL）或完整UTF-8编码（PostgreSQL），比如MySQL字段修改语句：

ALTER TABLE leaked_file MODIFY COLUMN file VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE xcp_file MODIFY COLUMN file_path VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

utf8mb4支持所有Unicode字符，不会把特殊字符替换成“?”。

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