MS SQL Server批量导入固定宽度文本文件错位问题解决

问题背景 #

将700行固定宽度文本批量插入MS SQL Server表时，出现每行字段首字符错位偏移的问题，相关信息如下：

原始数据格式 #

M1DR3QQ3XE954K76EC5VQ302017-06-172021-07-24            
M1DR3QQ3XE954K76EC5VQ302021-07-242021-07-24            
M1E14UR7TK502G16C89CK302017-06-172020-11-24            
M1E50UN7PH498CA8WJ1UN692017-06-172018-07-09            

建表语句 #

CREATE TABLE [TABLENAME](
    ROW1 [char](1) NULL,
    ROW2 [varchar](15) NULL,
    ROW3 [varchar](15) NULL,
    ROW4 [varchar](50) NULL,
    ROW5 [varchar](50) NULL,
    ROW6 [varchar](50) NULL
)

使用的BULK INSERT代码 #

BULK INSERT TABLENAME
FROM 'DATA FILE SOURCE'
WITH (
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n' ,
FORMATFILE = 'FORMAT FILE SOURCE'
);

格式文件内容 #

13.0
6
1       SQLCHAR       0       1       ""          1       Column name ""
2       SQLCHAR       0       11      ""          2       Column name ""
3       SQLCHAR       0       11      ""          3       Column Name ""
4       SQLCHAR       0       10      ""          4       Column Name ""
5       SQLCHAR       0       10      ""          5       Column Name""
6       SQLCHAR       0       12      ""          6       Column Name ""

当前错误结果 #

M   1AW9F20MH32 2EW4FU4FV82 2017-06-17  2018-07-09              
    M1C56YV4MN6 44KC5FE3MU9 42018-11-0  32019-11-1  3           
     M1CC5H01WD 822DF4GX8GM 722017-06-  172018-07-  09          
      M1CY2XQ9P W154U69YG3Y N452017-06  -172018-07  -09         
       M1D12M47 DA293MU7NK4 QU692017-1  0-182018-0  7-09        
        M1DF6U9 1UM628FU3JF 7RM122017-  06-172018-  07-09       
         M1DH5G 89HM836N94J V7FU732017  -10-252018  -07-09      
          M1DK2 PX7EQ195W55 T40NG69201  7-06-17201  8-07-09     
           M1DR 3QQ3XE951DR 3QQ3XE9520  21-07-2220  21-07-24    
            M1D R3QQ3XE954K 76EC5VQ302  017-06-172  021-07-24   
             M1 DR3QQ3XE954 K76EC5VQ30  2021-07-24  2021-07-24  
              M 1E14UR7TK50 2G16C89CK3  02017-06-1  72020-11-24 

期望正确结果 #

M   1AW9F20MH32 2EW4FU4FV82 2017-06-17     2018-07-09
M       1C56YV4MN64     4KC5FE3MU9      2018-11-03     2019-11-13
M       1CC5H01WD82     2DF4GX8GM72     2017-06-17     2018-07-09

注：第6列为临时添加的空列，仅用于维护完整性，后续会删除。

问题原因分析 #

1. 格式文件字段长度与实际数据宽度不匹配：
   格式文件中定义的各字段长度之和（1+11+11+10+10+12=55）远小于原始数据的实际行长度，导致SQL Server读取字段时位置偏移，后续行的读取起点不断错位。
2. 格式文件语法与配置错误：
   • 第5行的Column Name""缺少空格，语法不规范；
   • 格式文件中使用的列名（Column name）与表实际字段名（ROW1等）不对应，字段映射关系混乱；
   • 未准确匹配每行的总长度与各字段长度的分配。
3. 第6列不是直接原因：
   临时添加的空列不会直接导致错位，只要格式文件中正确定义该字段的长度（比如对应行尾剩余空格的长度），就不会影响其他字段的读取。

修复步骤 #

1. 重新确认每个字段的固定宽度 #

根据原始数据和期望结果，明确各字段的准确长度：

• ROW1：固定1位（如M）
• ROW2：固定11位（如1DR3QQ3XE95）
• ROW3：固定11位（如4K76EC5VQ30）
• ROW4：固定10位（如2017-06-17）
• ROW5：固定10位（如2021-07-24）
• ROW6：计算每行总长度减去前5列长度的差值（比如行尾空格的长度）

2. 修正格式文件 #

修正语法错误，调整字段长度，确保列名与表字段对应，示例如下（假设第6列长度为20，需根据实际行总长度调整）：

13.0
6
1       SQLCHAR       0       1       ""          1       ROW1 ""
2       SQLCHAR       0       11      ""          2       ROW2 ""
3       SQLCHAR       0       11      ""          3       ROW3 ""
4       SQLCHAR       0       10      ""          4       ROW4 ""
5       SQLCHAR       0       10      ""          5       ROW5 ""
6       SQLCHAR       0       20      ""          6       ROW6 ""

注意：所有字段长度之和需等于每行的实际总长度，避免读取偏移。

3. 调整BULK INSERT参数 #

固定宽度导入时，确认换行符配置（Windows系统文本文件需用'\r\n'）：

BULK INSERT TABLENAME
FROM 'DATA FILE SOURCE'
WITH (
    FIELDTERMINATOR = '',
    ROWTERMINATOR = '\r\n',
    FORMATFILE = 'FORMAT FILE SOURCE'
);

4. 验证与批量导入 #

先导入少量数据（如前3行）验证字段是否对齐，确认无误后再批量导入700行数据。后续可通过ALTER TABLE TABLENAME DROP COLUMN ROW6;删除临时列。

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