using sqlldr in oracle to import data

I have a txt file as follows. 1st machine_no,2nd emp_no, 3rd shift_type (1 for entry,3 for exit, can also be 4 for entry(multiple entry), 5 for exit(multiple exit), 5th work_date, 6th 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:00,        
001,0000000018,03,2011/06/21,16:50,        
001,0000000026,03,2011/06/21,16:55,        
001,0000000023,01,2011/06/21,07:20,        
001,0000000023,03,2011/06/21,16:30,        
001,0000000023,01,2011/06/22,07:20,        
001,0000000023,03,2011/06/22,16:30,        
001,0000000023,01,2011/06/23,07:20,        
001,0000000023,03,2011/06/23,16:30, 

I want to load the data from the txt file into an oracle table to be as follows:

EMP_NO WORK_DATE         START_TIME          END_TIME            
------ ------------- ----------------------- -------------------
    26 21-06-2011 00:00:00   21-06-2011 14:00:00 21-06-2011 16:55:00
    18 21-06-2011 00:00:00   21-06-2011 07:00:00 21-06-2011 16:50:00
    23 21-06-2011 00:00:00   21-06-2011 07:20:00 21-06-2011 16:30:00
    23 22-06-2011 00:00:00   22-06-2011 07:20:00 22-06-2011 16:30:00
    23 23-06-2011 00:00:00   23-06-2011 07:20:00 23-06-2011 16:30:00
    21 21-06-2011 00:00:00   21-06-2011 06:50:00 21-06-2011 14:00:00  

Please advise what is the most simplest 开发者_开发百科& fastest way to do it with the code. Appreciate your help.

Looking at the data snippet you supplied you should use external tables and this should be close:

CREATE TABLE file_table
(machine_no     VARCHAR2(3),
 emp_no         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
     FIELDS TERMINATED BY ','
     (
      machine_no     CHAR(3),
      emp_no         CHAR(10),
      shift_type     CHAR(2),
      work_date      CHAR(10),
      time           CHAR(5)
     )
    )
    LOCATION 
    (
     file_dir:'<file_name_including_extension>'
    )
  )
  NOPARALLEL;

You'll have to create a logical oracle directory that points to a physical server directory where you can put the data file (and by default the external table will write its logs into that directory too).

CREATE OR REPLACE DIRECTORY file_dir AS '<server-directory-path>';

Once this is done you can then select from the file_table and move or transform the data however you choose.

N.B. I have kepts the fields as VARCHAR2 but you can to_date or to_number them as you wish. I prefer transforming data once it is in the DB, you might prefer to do it in the external table...

See Kevin Burton's answer for the link to External Table how-to.

Hope this helps...

SQLLDR + SQL script is an excellent way to load data into your database.

However if you are using Oracle 9i and above, I think , the easiest solution has to be to use external tables, but you need the get the file on to some storage the database can access, (a samba share or NFS mounted file system)

There is little performance difference between sqlldr & external tables. (although there is a difference in the way they both handle constraint violations)

also see: http://orafaq.com/node/848