title: Error while loading CSV file to Oracle DB using SQL Loader
body: <p>I have a CSV file in which their is a multiline data. Important thing to note in that file is that, each record ends with CRLF character, and multi line incomplete record ends with LF. Now if I use below .ctl file for SQL Loader, the records load successfully. Below is the CSV file snapshot(correct file.jpg) and the ctl file.
  <a href="https://i.stack.imgur.com/DKfff.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DKfff.jpg" alt="enter image description here"></a></p>
  
  <p>ctl File :</p>
  
  <pre><code>OPTIONS ( 
      ERRORS=1,
      SKIP=1
  )
  LOAD DATA
  CHARACTERSET 'UTF8'
  INFILE 'C:\Users\puspender.tanwar\Desktop\try_SQLLDR\csvfile.csv'
  BADFILE  'C:\Users\puspender.tanwar\Desktop\try_SQLLDR\badfile.bad' 
  DISCARDFILE 'C:\Users\puspender.tanwar\Desktop\try_SQLLDR\DSCfile.dsc' 
  CONTINUEIF LAST != '"'
  INTO TABLE  ODI_DEV_TARGET.CASE
  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  (
      ID  "REPLACE(:ID,'&lt;br&gt;',chr(10))",
      ISDELETED     "CASE WHEN :ISDELETED='true' then 'T' ELSE 'F' END",
      CASENUMBER  "REPLACE(:CASENUMBER,'&lt;br&gt;',chr(10))",
      DESCRIPTION CHAR(30000)
  )    
  </code></pre>
  
  <p>Now if I loaded another CSV file which contains exactly same data, the records fails to load. The only difference in this second csv file is that the records ends with LF and incomplete records ends with CRLF. I used the same ctl file but got the error : <code>Rejected - Error on table ODI_DEV_TARGET.CASE, column DESCRIPTION. second enclosure string not present</code> below is the  snapshot of second csv file.<a href="https://i.stack.imgur.com/7KWVb.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7KWVb.jpg" alt="enter image description here"></a></p>
  
  <p>I also noticed that if I change INFILE option of ctl file to <code>INFILE 'C:\Users\puspender.tanwar\Desktop\try_SQLLDR\csvfile.csv "str '\r\n'"'</code> the record then also got loaded for first CSV(only). So I though if I use <code>"str '\n'"</code> instead of <code>"str ' \r\n'"</code> then second CSV records should load, but unfortunately that not happane.<br>
  Please advise me how to handle this by doing some modification in .ctl file. Or any other way to resolve this.</p>