如何在SQL Server中检测文件字符编码适配OPENROWSET读取

方法1：检查文件的字节顺序标记（BOM） #

Unicode编码文件通常带有BOM，可通过读取文件前几个字节判断：

• UTF-8 BOM：0xEF 0xBB 0xBF
• UTF-16LE（小端）BOM：0xFF 0xFE
• UTF-16BE（大端）BOM：0xFE 0xFF
• UTF-32LE BOM：0xFF 0xFE 0x00 0x00
• UTF-32BE BOM：0x00 0x00 0xFE 0xFF

若文件开头存在上述任意一种BOM，使用SINGLE_NCLOB；无BOM则先默认用SINGLE_CLOB，后续可结合内容校验。

方法2：检测文件内容中的非ASCII字符 #

针对无BOM的文件，可读取前几KB内容，检查是否存在ASCII范围（0x00-0x7F）之外的字符：

• 若发现超出ASCII范围的字节，判定为Unicode编码，改用SINGLE_NCLOB
• 若所有字节都在ASCII范围内，直接使用SINGLE_CLOB

方法3：程序中捕获错误并重试 #

因为是程序执行，可采用「尝试-捕获错误-重试」的逻辑：

1. 先使用SINGLE_CLOB执行查询
2. 若成功则直接返回结果；若抛出SINGLE_NCLOB requires a UNICODE (widechar) input file类错误，说明是Unicode编码，改用SINGLE_NCLOB重新执行

伪代码示例 #

def read_file_from_sqlserver(file_path):
    # 先尝试SINGLE_CLOB
    clob_query = f"SELECT BulkColumn FROM OPENROWSET(BULK N'{file_path}', SINGLE_CLOB) AS Contents;"
    try:
        return execute_sql(clob_query)
    except Exception as e:
        # 匹配目标错误则重试SINGLE_NCLOB
        if "SINGLE_NCLOB requires a UNICODE" in str(e):
            nclob_query = f"SELECT BulkColumn FROM OPENROWSET(BULK N'{file_path}', SINGLE_NCLOB) AS Contents;"
            return execute_sql(nclob_query)
        # 其他错误直接抛出
        raise e

注意事项 #

• 无BOM的UTF-8文件无法通过BOM检测识别，此时方法2或3更可靠
• 若涉及GBK等非Unicode的多字节编码，SINGLE_CLOB可能出现乱码，但如果仅需区分Unicode/ASCII类编码，以上方法可满足需求

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