使用sqlloader加載到表中時跳過前兩條記錄-有解無憂

我有一個 CSV 檔案，其中包含 4181 條記錄和從 excel 中的第 5 行開始的記錄。

桌子：

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 行的前兩條記錄，并且從第 7 行開始，它已插入所有記錄。有人可以幫我解決為什么它只跳過前兩條記錄嗎？

CSV 檔案內容鏈接

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

由于我無法在此處上傳檔案或粘貼資料

uj5u.com熱心網友回復：

謝謝你的測驗用例。

一、表：

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>

我下載了示例資料（只是為了說明檔案包含的內容，這是一個螢屏截圖）：

控制檔案，稍作修改（重新排列行，洗掉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
)

加載會話：

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 行；這就是我們所期望的，因為必須跳過前 4 行，而COL11 到 10 之間的值代表我們需要的行。

那么，表的內容是什么？

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>

顯然，沒有任何問題，一切都已成功加載。

因此，要么您沒有發布具有代表性的樣本資料，要么您遇到了我無法重現的問題。我在我的 Oracle 11gXE 資料庫中運行了該示例。

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346914.html

標籤：甲骨文 sql-loader

上一篇：Oracle：查詢對不可見列的評論

下一篇：在Oracle中使用MODEL子句并回傳以前的值