使用SQL Loader导入表时配置skip=4后仍漏导入前2条有效记录问题咨询

• 待导入CSV文件共4181条记录，有效记录起始位置为Excel视图下的第5行
• 导入工具为Oracle SQLLoader

CREATE TABLE SQL_LOAD
(col1 number(10),
Col2  varchar2(4000),
Col3  varchar2(4000),
Col4  varchar2(4000),
Col5  varchar2(4000),
Col6  varchar2(4000),
Col7  varchar2(4000),
Col8  varchar2(4000),
Col9  varchar2(4000),
Col10  varchar2(4000),
Col11  varchar2(4000),
Col12  varchar2(4000),
Col13  varchar2(4000),
Col14  varchar2(4000),
Col15  varchar2(4000),
Col16  varchar2(4000),
Col17  varchar2(4000),
Col18  varchar2(4000),
Col19  varchar2(4000),
Col20  varchar2(4000),
Col21  varchar2(4000),
Col22  varchar2(4000),
Col23  varchar2(4000),
Col24  varchar2(4000),
Col25  varchar2(4000),
Col26  varchar2(4000),
Col27  varchar2(4000),
Col28  varchar2(4000),
Col29  varchar2(4000),
Col30  varchar2(4000),
Col31  varchar2(4000),
Col32  varchar2(4000),
Col33  varchar2(4000),
Col34  varchar2(4000),
Col35  varchar2(4000),
Col36  varchar2(4000),
Col37  varchar2(4000),
Col38  varchar2(4000),
Col39  varchar2(4000),
Col40  varchar2(4000),
Col41  varchar2(4000),
Col42  varchar2(4000),
Col43  varchar2(4000),
Col44  varchar2(4000),
Col45  varchar2(4000),
Col46  varchar2(4000),
Col47  varchar2(4000),
Col48  varchar2(4000),
Col49  varchar2(4000),
Col50  varchar2(4000),
Col51  varchar2(4000),
Col52  varchar2(4000),
Col53  varchar2(4000),
Col54  varchar2(4000),
Col55  varchar2(4000),
Col56  varchar2(4000),
Col57  varchar2(4000),
Col58  varchar2(4000),
Col59  varchar2(4000),
Col60  varchar2(4000),
Col61  varchar2(4000),
Col62  varchar2(4000),
Col63  varchar2(4000),
Col64  varchar2(4000),
Col65  varchar2(4000),
Col66  varchar2(4000),
Col67  varchar2(4000),
Col68  varchar2(4000),
Col69  varchar2(4000),
Col70  varchar2(4000),
Col71  varchar2(4000),
Col72  varchar2(4000),
Col73  varchar2(4000),
Col74  varchar2(4000),
Col75  varchar2(4000),
Col76  varchar2(4000),
constraint pk_sql_load primary key (col1)
);

options (
    skip = 4,
    DIRECT = TRUE
)
load data
infile 'I:\SQLLOADER\sqlloader.csv'
append
continueif last != ','
into sql_load
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18 CHAR(4000),
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
Col27,
Col28,
Col29,
Col30,
Col31,
Col32,
Col33,
Col34,
Col35,
Col36,
Col37,
Col38,
Col39,
Col40,
Col41,
Col42,
Col43,
Col44,
Col45,
Col46,
Col47,
Col48,
Col49,
Col50,
Col51,
Col52,
Col53,
Col54,
Col55,
Col56,
Col57,
Col58,
Col59,
Col60,
Col61,
Col62,
Col63,
Col64,
Col65,
Col66,
Col67,
Col68,
Col69,
Col70,
Col71,
Col72,
Col73,
Col74,
Col75,
Col76
)

执行导入后仅成功插入4179条记录，Excel视图下第5、第6行共2条有效记录未导入，第7行及之后所有记录均正常插入。

异常是由控制文件中continueif last != ','配置的行合并规则导致的：

1. 该配置的作用是：SQLLoader读取每一行后，会检查当前行的最后一个字符是否为逗号，如果不是，会自动将下一行内容拼接至当前行末尾，作为同一条记录处理，直到读取到某一行末尾为逗号时，才会将拼接后的完整内容作为一条记录解析。
2. 跳过前4行后读取到的第5、第6行末尾均不是逗号，第7行末尾为逗号，因此SQLLoader会将第5、6行合并为一条记录解析。合并后的记录字段总数远超过目标表的76个字段，会被判定为无效记录直接丢弃，最终就出现了少2条有效记录的现象，第7行及之后的行均满足末尾为逗号的规则，不会被合并，因此全部正常导入。

• 如果CSV不存在单条记录拆分为多行存储的场景，直接删除控制文件中的continueif last != ','配置即可。
• 如果确实存在行拆分场景，检查第5、第6行的末尾格式，确保单条完整记录的末尾为逗号即可。

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