ctl help 1

[ddiamond]

I have a comma delimited data file:

159.38,ADMWFCM,10058402,20060421
772.95,ADMWFCM,10058405,20060421
2312.70,ADMWFCM,10058471,20060421

My ctl file:

LOAD DATA
INFILE 'I:\Development\VendorPymt\ClearedChecks\042106a.txt'
APPEND
INTO TABLE CLEARED_CHECKS
FIELDS TERMINATED BY ','
(
CHECK_AMOUNT  CHAR, 
CHECK_TYPE    CHAR,
CHECK_NUMBER  CHAR,
CLEARED_DATE  DATE "YYYYMMDD"
)

This works fine, accept it loads the entire value ADMWFCM into the check_type. What I'd really like to do is load everything after the ADMWF. In sql you would do this with substr(check_type,6). Can I do this within a CTL file?

 

[Beilstwh]

Specify sizes for the fields in cleared_checks, for example

LOAD DATA
INFILE 'I:\Development\VendorPymt\ClearedChecks\042106a.txt'
APPEND
INTO TABLE CLEARED_CHECKS
FIELDS TERMINATED BY ','
(
CHECK_AMOUNT CHAR(10),
CHECK_TYPE CHAR(15),
CHECK_NUMBER CHAR(30),
CLEARED_DATE DATE "YYYYMMDD"
)

However, if you are creating a new table, I would STRONGLY suggest that your check_amount field be of type number in the cleared_checks table, not string.

Bill
Oracle DBA/Developer
New York State, USA

 

[ddiamond]

Beilstwh,

In my Cleared_Checks table, Check_Amount is a number. Oracle does not seem to care that the ctl file calls it a char. It converts it anyway. Now I'm not sure how specifying a field width helps. I want to remove the first 5 characters of check_type. So instead of "ADMWFCM", I would get "CM".

- Dan

 

[mpramods]

The following should work.
LOAD DATA
INFILE 'I:\Development\VendorPymt\ClearedChecks\042106a.txt'
APPEND
INTO TABLE CLEARED_CHECKS
FIELDS TERMINATED BY ','
(
CHECK_AMOUNT CHAR(10),
CHECK_TYPE CHAR(10) "SUBSTRCHECK_TYPE, 6)",
CHECK_NUMBER CHAR(30),
CLEARED_DATE DATE "YYYYMMDD"
)

let me know what happened.

Cheers,
Pramod

 

[ddiamond]

Thanks Pramod. I'll give it a try and let you know.

 

[ddiamond]

Pramod,

It worked perfectly!

- Dan