How can I load data conditionally using sqlldr in oracle

I have a txt file as follows. 1st machine_no,2nd emp_no, 3rd shift_type (1 for entry,3 for exit), 4th work_date, 4th is time.

001,0000000021,01,2011/06/21,06:50,            
001,0000000026,01,2011/06/21,14:00,         
001,0000000018,01,2011/06/21,07:00,         
001,0000000021,03,2011/06/21,14开发者_如何学JAVA:00,         
001,0000000018,03,2011/06/21,16:50,         
001,0000000026,03,2011/06/21,16:55,  

I want to load data in the table. The field time1 to have time if time_type is 1 and the field time2 to have time if time_type is 3. Please let me know how I can have this in the control file.

Thanks in advance for your help..Macky.

Below is the txt file and table in oracle.

The table as follows:

desc data_trans;

 Name                            Null?    Type
 ------------------------------- -------- ----
 MACHIAN                                  VARCHAR2(4)
 YEAR                                     NUMBER(4)
 MONTH                                    VARCHAR2(2)
 WDAY                                     VARCHAR2(2)
 TIME1                                    VARCHAR2(5)
 TIME2                                    VARCHAR2(5)
 TIME3                                    VARCHAR2(2)
 SHIFT_NO                                 NUMBER(1)
 TIME_TYPE                                NUMBER(1)
 WORK_DATE                                DATE
 EMP_NO                                   VARCHAR2(10)

Why don't you use an external table to read this file and then you can simply SELECT from it and perform any conditional transformation you want to easily in SQL.

N.B. There are a few assumptions, you seem to interchange "time_type" with what seems to be "shift_type" and there are table columns that are not present in your data file etc.

-- Create the Oracle directory
CREATE OR REPLACE DIRECTORY file_dir AS '<physical-server-directory-path>';

-- Grant privs on the directory
GRANT READ, WRITE ON DIRECTORY file_dir TO <username>;

-- Create the external table
CREATE TABLE ext_data_table
(machine_no                VARCHAR2(4),
 emp                       VARCHAR2(10),
 shift_type                VARCHAR2(2),
 work_date                 VARCHAR2(10),
 time                      VARCHAR2(5)
)
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY file_dir 
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
    BADFILE file_dir :'file.bad'
    LOGFILE file_dir :'file.log'
    DISCARDFILE file_dir :'file.dsc'
     FIELDS TERMINATED BY ','
     (
      machine_no                CHAR(4),
      emp                       CHAR(10),
      shift_type                CHAR(2),
      work_date                 CHAR(10),
      time                      CHAR(5)
     )
   LOCATION (
    FILE_DIR:'<filename>'
   )
  )
  NOPARALLEL;

-- Insert (and transform) your file data
INSERT INTO data_trans
(machian,
 year,
 month,
 wday,
 time1,
 time2,
 time3,
 shift_no,
 time_type,
 work_date,
 emp_no)
SELECT machine_no,              -- machian
       SUBSTR(work_date, 1, 4), -- year
       SUBSTR(work_date, 6, 2), -- month
       SUBSTR(work_date, 9, 2), -- wday
       (CASE TO_NUMBER(shift_type)
           WHEN 1
           THEN time
           ELSE NULL
         END),                  -- time1
       (CASE TO_NUMBER(shift_type)
           WHEN 3
           THEN time
           ELSE NULL
         END),                  -- time2
       NULL,                    -- time3    (You don't specify)
       NULL,                    -- shift_no (You don't specify)
       TO_NUMBER(shift_type),   -- time_type
       TO_DATE(work_date, 'YYYY/MM/DD'), -- work_date
       emp                      -- emp_no
  FROM ext_data_table;

From the info given this is a close approximation.

Hope it helps.

This is not possible in SQL*Loader. Use an External Table, then copy it into your real table using the decode function to pivot.