含双引号与制表符的TXT文件BULK INSERT导入SQL Server异常修复

问题说明 #

现有TXT格式数据文件，部分记录的name字段内部包含制表符，导致使用BULK INSERT导入SQL Server时字段错位：双引号被错误识别到code列，country列被填充为原本的code值。

原始TXT数据 #

id name code country 
0  "hoa" 111 vietnam
1  "thang	 " 123 vietnam

注：第二条记录的name字段（"thang "）中包含一个制表符

导入后错误数据 #

当前BULK INSERT代码 #

SET @SQL = 'BULK INSERT #GL055_BRN
FROM ''F:\d.txt''
WITH (
DATAFILETYPE = ''char'',
FIRSTROW = 2,
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n''
)';
EXEC(@SQL)

期望导入结果 #

解决方案 #

方法1：使用XML格式文件指定文本限定符 #

通过创建格式文件，告诉SQL Server将双引号作为字段的限定符，忽略引号内部的制表符，确保字段正确识别。

1. 创建XML格式文件（例如F:\format.xml）：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" QUOTEDON="YES"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="50"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="code" xsi:type="SQLINT"/>
  <COLUMN SOURCE="4" NAME="country" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

2. 修改BULK INSERT代码，引入格式文件：

SET @SQL = 'BULK INSERT #GL055_BRN
FROM ''F:\d.txt''
WITH (
DATAFILETYPE = ''char'',
FIRSTROW = 2,
FORMATFILE = ''F:\format.xml''
)';
EXEC(@SQL)

关键说明：QUOTEDON="YES"参数会让SQL Server将双引号包裹的内容视为完整字段，自动忽略其中的制表符，避免字段错位。

方法2：预处理TXT文件，移除引号内的制表符 #

如果无法使用格式文件，可先对原始TXT文件做预处理，将引号内部的制表符替换为空格（或其他非分隔符）。

例如使用PowerShell脚本处理：

(Get-Content "F:\d.txt") -replace '(?<="[^"]+)\t(?=[^"]+")', ' ' | Set-Content "F:\d_fixed.txt"

处理完成后，使用d_fixed.txt作为导入文件即可。

方法3：使用OPENROWSET搭配格式文件 #

若BULK INSERT仍有问题，可尝试使用OPENROWSET替代：

SELECT * INTO #GL055_BRN
FROM OPENROWSET(
    BULK 'F:\d.txt',
    FORMATFILE = 'F:\format.xml',
    FIRSTROW = 2
) AS t;

内容的提问来源于stack exchange，提问作者Thắng Nguyễn