13 Replies Latest reply: Apr 19, 2012 4:44 AM by 931777 RSS

    SQL Loader to load tab delimited data file with null fields

    415641
      Hi there,

      I am having problem doing the following:
      Load tab delimited data file with null fields to a table using SQL Loader. It seems that SQL Loader will only recognize one whitespace when there are in fact two or more whitespaces(one tab and one or more null fields), so the null fields are ignored and the next field that does have a value, is moved up, resulting data being inserted to wrong columns or rejections if the value in the next field is larger than the null fields.

      My control file:

      LOAD DATA
      INFILE 'invoice.csv'
      INTO TABLE TB__Load_Invoices
      FIELDS TERMINATED BY WHITESPACE
      OPTIONALLY ENCLOSED BY '"'
      TRAILING NULLCOLS
      (ID,
      NUMBER,
      TICKET,
      CODE
      )

      For example, here is my data file
      ID NUMBER     TICKET(VARCHAR2(3)) CODE(VARCHAR2(8))
      1 999     123               CC345999
      2 879     Null               AA999999
      3 Null 555                BB1
                     
      In this case, while loading the above file using the control file, the second record is rejected because there are two whitespaces between NUMBER and CODE (one is a tab and the other is a null field for TICKET. SQL Loader would recognize only one whitespace and try to insert CODE 'AA999999' to TICKET, resulting rejection because the value is too large. For the third record, the value of TICKET will be inserted to NUMBER and value of CODE inserted to TICKET.

      Are there any ways to make SQL Loader recognize that there are two whitespaces, and insert a null value to the null field, instead of trying to insert the value of the next field to the null field?

      Could anyone help on this one? Thanks a lot in advance.