BCP导出时部分记录出现额外列问题求助

问题根源 #

出现CITY和STATE字段之间多制表符的情况，大概率是这两条异常记录的CITY字段本身包含了制表符（ASCII码9）。BCP按指定的\t作为字段终止符分割内容，当字段内部存在相同的终止符时，就会被误判为字段分割点，导致行结构错乱。

排查与解决步骤 #

1. 定位异常数据
   执行以下SQL查询，找出CITY字段包含制表符的记录：

SELECT * FROM mytable 
WHERE CHARINDEX(CHAR(9), CITY) > 0;

2. 处理异常数据
   有两种处理方式，按需选择：

• 直接清理原数据：将CITY字段中的制表符替换为空格（或其他合适字符）

UPDATE mytable 
SET CITY = REPLACE(CITY, CHAR(9), ' ') 
WHERE CHARINDEX(CHAR(9), CITY) > 0;

• 导出时临时处理（不修改原数据）：修改BCP的查询语句，在导出前替换CITY字段内的制表符

DECLARE @bcpCommand varchar(2000)
DECLARE @FileName varchar(80)

SET @FileName = 'c:\data\output.txt'
SET @bcpCommand = 'bcp "SELECT PlanTo, DESCRIPTION, ADDRESS1, ADDRESS2, REPLACE(CITY, CHAR(9), '' ''), STATE, ZIPCODE, Status, PriceList, Broker FROM  mytable" queryout "' 
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -S Localhost -f c:\format\myTable.fmt '
      
EXEC master..xp_cmdshell @bcpCommand

3. 预防后续问题
   如果其他字段也存在包含制表符的可能，建议在导出查询中对所有字符串字段统一做替换处理；或者更换为不会出现在字段内容中的终止符（比如|），同时调整格式文件中所有TERMINATOR="\t"为新的终止符。

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