The problem lies within the value column as the data within this column varies from time values, names, other ids. The problem is when coming across a time value the hh:mm are being entered correctly but for some reason the seconds get rounded of to 00.
OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1) load data infile 'c:/My Files/Daily Feeds/20130128/data_20130128.txt' "STR '\n'" APPEND into table DATA_2 WHEN (RID <> RID') fields terminated by "~|~" optionally enclosed by '"' TRAILING NULLCOLS ( RID, SEQ, NAME, VALUE, CONVERTED_TIMESTAMP timestamp 'DD-MON-RR HH:MI:SS.FF3 PM' )
but the database shows this as:
rid~|~seq~|~name~|~value~|~converted_timestamp 0VPH5V2GQ15W29QE~|~71~|~str_TimestampDelivery~|~2012-12-01 08:30:58~|~01-DEC-12 08.30.58.000 AM
Other note to bear in mind, is the next row of data will not show time values hence the datatype in the control file has been left blank.
0VPH5V2GQ15W29QE 71 str_TimestampDelivery 01/12/2012 08:30:00 01-DEC-12 08:30:00