Skipping columns which loading data into an external table

Question

This is the external table I created:

CREATE TABLE emp_load 
(
  "ESN_NO" VARCHAR2 (200), 
  "MAKER" VARCHAR2 (30), 
  "HANDSET" VARCHAR2 (20), 
  "MODEL_NO" VARCHAR2 (20), 
  "OMH_TTSL_FLAG" VARCHAR2 (10), 
  "OFFER" VARCHAR2 (100), 
  "STATUS" VARCHAR2 (20), 
  "STATUS_UPDATED_DATE" DATE
) ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TAB_DIR" ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE skip 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    missing FIELD VALUES are NULL
    ) LOCATION ('BAU.csv')
) ;                         

The sample contents of BAU.csv file

ESN No          Make    Model   RI_value
A10000428A5348  Lava    C180    OFFER14
A10000428A5349  Lava    C180    OFFER14
A10000428A534A  Lava    C180    OFFER14
A10000428A534B  Lava    C180    OFFER14

It works fine but the data isn't appearing in the columns as I need; I want the column handset to be empty, but currently the model value from the file gets loaded into the handset column. So I currently get:

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER  STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava   C180     OFFER14                                                      
A10000428A5349  Lava   C180     OFFER14                                                      
A10000428A534A  Lava   C180     OFFER14                                                      
A10000428A534B  Lava   C180     OFFER14                                                      

I want the handset column to be skipped and the model to be inserted in model_no; and omh_ttsl_flag should be skipped and the ri_value from the file should be inserted in offer. So what I want to end up with is:

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER    STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava            C180                     OFFER14                               
A10000428A5349  Lava            C180                     OFFER14                               
A10000428A534A  Lava            C180                     OFFER14                               
A10000428A534B  Lava            C180                     OFFER14                               

How can I skip over those columns while loading the data into the external table?

Answer 1

You can do this by specifying the fields in the file as part of the fields clause, and specifying the ones that don't appear with the column transforms clause, e.g:

COLUMN TRANSFORMS ("HANDSET" FROM NULL, "OMH_TTSL_FLAG" FROM NULL)

So your statement becomes:

CREATE TABLE emp_load 
(
  "ESN_NO" VARCHAR2 (200), 
  "MAKER" VARCHAR2 (30), 
  "HANDSET" VARCHAR2 (20), 
  "MODEL_NO" VARCHAR2 (20), 
  "OMH_TTSL_FLAG" VARCHAR2 (10), 
  "OFFER" VARCHAR2 (100), 
  "STATUS" VARCHAR2 (20), 
  "STATUS_UPDATED_DATE" DATE
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TAB_DIR" ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE skip 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      "ESN_NO" CHAR (200), 
      "MAKER" CHAR (30), 
      "MODEL_NO" CHAR (20), 
      "OFFER" CHAR (100)
    )
    COLUMN TRANSFORMS
    (
      "HANDSET" FROM NULL,
      "OMH_TTSL_FLAG" FROM NULL,
      "STATUS" FROM NULL,
      "STATUS_UPDATED_DATE" FROM NULL
    )
  )
  LOCATION ('BAU.csv')
) ;  

I've included the other two columns, which isn't necessary but helps document what you expect to see.

With your sample data translated to CSV that loads as:

set sqlformat ansiconsole
column esn_no format a20
select * from emp_load;

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER    STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava            C180                     OFFER14                               
A10000428A5349  Lava            C180                     OFFER14                               
A10000428A534A  Lava            C180                     OFFER14                               
A10000428A534B  Lava            C180                     OFFER14