Skipping first two records while loading into the table using sql loader-CodePudding

I have one CSV file that contains 4181 total records and records starting from the 5th row in excel.

Table :

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)
);

Control file :

    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
    )

I could see 4179 records got inserted but not inserting only the first two records that are row num 5th and 6th from excel and from 7th row onwards it has inserted all the records. Can someone help me with this why it is skipping only the first two records?

CSV file content link

https://drive.google.com/file/d/1wKchp3y1Uir2hxuXS29rX5GAQdHU6LUd/view?usp=sharing

As I am unable to upload the file here or paste the data

CodePudding user response：

Thank you for test case.

First, the table:

SQL> CREATE TABLE SQL_LOAD
  2  (col1 number(10),
  3  Col2  varchar2(4000),
  4  Col3  varchar2(4000),
<snip>
 77  Col76  varchar2(4000),                       --> comma was missing here
 78  constraint pk_sql_load primary key (col1)
 79  );

Table created.

SQL>

I downloaded sample data (just to illustrate what the file contains, here's a screenshot):

Control file, slightly modified (rearranged lines, removed continueif):

options (
    skip = 4,
    DIRECT = TRUE
    )
load data
infile 'c:\temp\sqlloader.csv'
replace into table sql_load
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
Col1,
Col2,
Col3,
<snip>
Col75,
Col76
)

Loading session:

SQL> select count(*) From sql_load;

  COUNT(*)
----------
         0

SQL> $sqlldr scott/tiger control=test7.ctl log=test7.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Stu 2 20:54:08 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 10.

SQL>

10 rows were loaded; that's what we expected as first 4 rows had to be skipped, while COL1 values from 1 to 10 represent rows we need.

So, what's the table contents?

SQL> select count(*) From sql_load;

  COUNT(*)
----------
        10

SQL> select col1, col2, col3 from sql_load;

      COL1 COL2                                     COL3
---------- ---------------------------------------- ----------
         1 LINE LEVEL IRE CAN DV01 -DD-MMM-YYYY     High
         2 Debt Adjustment Working file             High
         3 350125_USG_DEFTAX                        High
         4 3516_USGAPP_Adjustment                   High
         5 FRO_12_Q                                 High
         6 4.FRO                                    High
         7 SC45667                                  High
         8 410_qqret                                High
         9 Sv and PA_OEE                            High
        10 FAU                                      High

10 rows selected.

SQL>

Apparently, no problems at all, everything was successfully loaded.

Therefore, either you didn't post representative sample data, or you have problems I can't reproduce. I ran that example in my Oracle 11gXE database.