Oracle 由于新行字符和封闭字符，SQL加载程序不会加载所有需要的行

我对SQL loader如何管理列值的结尾有问题。我希望管理CR LF，封闭字符和分隔符，但似乎找不到解决方案!

我从。csv文件看起来像这样:

"C","I","FLAGS","LASTUPDATEDATE","BOEVERSION","C_OSUSER_UPDATEDBY","I_OSUSER_UPDATEDBY","C_OSUSER_PWF","DESCRIPTION","DURATION","ENDDATE","I_OSUSER_PWF","LASTSTATUSCHA","STARTDATE","DURATIONUNIT","TYPE","STATUS","C_BNFTRGHT_CONDITIONS","I_BNFTRGHT_CONDITIONS","C_CNTRCT1_CONDITION","I_CNTRCT1_CONDITION","EXTBLOCKTYPE","EXTBLOCKDURATIONUNIT","EXTBLOCKDURATION","EXTBLOCKDESCRIPTION","PARTITIONID"
    "7680","423","PE","2015-07-06 11:42:10","0","1000","1506","","No benefits are payable for a Total Disability period during a Parental or Family-Related Leave, for a Total Disability occurring during this period.
    ","0","","","","","69280000","69312015","71328000","7285","402","","","","","","","1"
    "7680","426","PE","2015-07-06 11:42:10","0","1000","1506","","""Means to be admitted to a Hospital as an in-patient for more than 18 consecutive hours.
    
    
    
    ""
    ","0","","","","","69280000","69312021","71328000","7285","402","","","","","","","1"

我的ctl文件如下:

Load Data
infile 'C:\2020-07-29-03-04-48-TolCondition.csv'
持续下去!= '"'
into table TolCondition
REPLACE
FIELDS TERMINATED BY "," ENCLOSED by '"'
(
C,
I,
FLAGS,
LASTUPDATEDATE DATE "YYYY-MM-DD HH24:MI:SS",
BOEVERSION,
C_OSUSER_UPDATEDBY,
I_OSUSER_UPDATEDBY,
C_OSUSER_PWF,
DESCRIPTION CHAR(1000),
DURATION,
ENDDATE DATE "YYYY-MM-DD HH24:MI:SS",
I_OSUSER_PWF,
LASTSTATUSCHA DATE "YYYY-MM-DD HH24:MI:SS",
STARTDATE DATE "YYYY-MM-DD HH24:MI:SS",
DURATIONUNIT,
TYPE,
STATUS,
C_BNFTRGHT_CONDITIONS,
I_BNFTRGHT_CONDITIONS,
C_CNTRCT1_CONDITION,
I_CNTRCT1_CONDITION,
EXTBLOCKTYPE,
EXTBLOCKDURATIONUNIT,
EXTBLOCKDURATION,
EXTBLOCKDESCRIPTION,
PARTITIONID)

这里是我在控制文件中尝试的:

持续下去!= '"'
继续如果这个保护区 (1:2) != '"，'
“str x'220d0a '”
Here is the result I currently have with "持续下去!= '"'

Record 2: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column DESCRIPTION.
second enclosure string not present
Record 3: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column C.
no terminator found after TERMINATED and ENCLOSED field

Table FNA_FNTFO2.TOLCONDITION:
  1 Row successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

有没有办法在SQL Loader中管理换行符和封闭字符？我不明白为什么我们不能改变它对行的看法。而不是看到一个新的行，当有一个CR LF，我们可以告诉它连接值，直到最后一个封闭字符 (chr34在我的情况下) 分隔符 (y，在我的情况下) 已经看到。

我真的希望找到一种无需更改即可解决此问题的方法。csv文件。

谢谢

我认为您将不得不以某种方式对CSV进行预处理。

如果我正确理解了您的文件，则您在自己的行中有两个双引号。这些应该是上面几行描述的一部分。

因此，检查最后一个字符是引号以外的东西不起作用，因为这是带有两个引号的行上的最后一个非空白字符。

这将检查当前记录，以决定是否将下一行添加到记录中。数据中没有任何东西可以标记哪个是新记录的开始。

但是，即使有，在第二条记录的描述中有几个空行，所以通过这些操作，这个条件在某个时候是假的，所以SQL * Loader认为它是一个新记录。

当下一行的前两个字符不是引号时，尝试继续-逗号也不起作用。新记录以quote-notcomma开头，因此这将尝试连接所有行except描述的结尾。

所以我认为你的选择是:

-预处理CSV，因此没有只有双引号的行。这允许你使用CONTINUEIF LAST!= '"'
-将文件作为外部表读取，将每一行作为单独的行加载。然后使用SQL或PL/SQL将行粘合在一起，并根据需要提取列