SQL loader not loading all the needed rows due to new line character and enclosement character
I have a problem with how SQL loader manage the end of a column value. I was hoping to manage CR LF, the enclosement character and the separator character but it seems I can't find a solution! The data I receive from the .csv file looks like this: <code>"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"</code> My ctl file is as follows: <code>Load Data infile 'C:\2020-07-29-03-04-48-TolCondition.csv' CONTINUEIF LAST != '"' 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)</code> Here is what I tried in the control file: CONTINUEIF LAST != '"' CONTINUEIF THIS PRESERVE (1:2) != '",' "str
SQL loader not loading all the needed rows due to new line character and enclosement character