如何在ADF或SQL中识别并移除导致导入异常的特殊NUL字符？

核心原因 #

你遇到的NUL字符（\0）本质是UTF-16编码的产物——单字节ASCII字符在UTF-16中会被补一个空字节（比如ASCII的A在UTF-16LE里是41 00），按UTF-8读取时就会显示成字符间的“空格”间隔，直接导入SQL会因无效字符导致空行或失败。

解决方案 #

1. 先修正ADF的文件编码配置 #

这是最直接的解决办法，无需额外清洗：

• 打开ADF中对应文本数据集的配置页
• 将编码从默认的UTF-8改为UTF-16（优先选UTF-16LE，大部分Windows生成的UTF-16文件是小端格式）
• 预览数据，确认NUL间隔是否消失，文本恢复正常

2. ADF数据流动清洗残留NUL字符 #

如果编码修正后仍有零星NUL字符，用数据流动的派生列处理：
添加派生列，使用replace函数清除NUL：

replace(目标列名, unescape('\u0000'), '')

unescape('\u0000')会生成NUL字符的转义值，直接替换为空即可。

3. SQL端兜底处理 #

如果已经导入临时表或需要直接从SQL导入：

• 清理已导入数据中的NUL字符：

UPDATE 你的表名
SET 目标列名 = REPLACE(目标列名, CHAR(0), '')
WHERE CHARINDEX(CHAR(0), 目标列名) > 0;

• 导入时指定UTF-16编码（用OPENROWSET）：

SELECT *
FROM OPENROWSET(
    BULK '你的文件路径.txt',
    FORMATFILE='格式文件路径.fmt',
    CODEPAGE='1200' -- 1200对应UTF-16编码
) AS 临时表;

4. 脚本预处理文件（极端情况） #

如果上述方法都无效，用PowerShell批量预处理文件，清除所有NUL字符：

Get-Content "输入文件路径.txt" -Encoding Unicode | ForEach-Object { $_ -replace "`0", "" } | Set-Content "输出文件路径.txt" -Encoding UTF8

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