SQLLOADER Format FIELD

[jestrada101]

Is there a method to do the following with SQL Loader?

I have a field column DURATION field in a flat file that is like the following.

24642300 which is H:MM:SS = 2:46:42

Is there a way to have SQL Loader convert this into seconds before it gets loaded?

I do not care to include the remaining three digits.

THANKS for any ideas...
JE

 

[ddrillich]

JE,

The following works for me:

create table tst ( tst NUMBER )

--- input file
24642300

--- ctl file
LOAD DATA
APPEND INTO TABLE tst
FIELDS TERMINATED BY ','
(
tst "substrtst,0,1)*60*60 + substrtst,2,2)*60 + substrtst,4,2)"
)

After running SQL*Loader the table holds the value 10002.

Regards,
Dan

 

[jestrada101]

That is exactly what I need to do, but i'm using positions to terminate each field... How can I implement that? This is how my control file looks...

DURATION position (108:115) CHAR(8),
EXAMPLE position (117:120) CHAR(4),
.....
.
.

Thanks!

 

[ddrillich]

JE,

The following control file is working for me:

LOAD DATA
APPEND INTO TABLE tst
(
tst position(1:8) char(8) "substrtst,0,1)*60*60 + substrtst,2,2)*60 + substrtst,4,2)"
)

After making the name and position adjustments it should work for you as well.

Regards,
Dan