sql*loader error

[syncdba]

Hi,
I have learner.csv file which has date columns..
ID Hire_on Term_on
3 2/12/2004 9/1/2005
7 12/23/2004 10/30/2005
And my .ctl is like

LOAD DATA
INFILE 'C:\TestingScripts\estalearner.csv'
APPEND
INTO TABLE stage_learners_esta
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ID "LTRIM(RTRIMID))" ,
HIRE_ON DATE,
Term_on DATE
)

It gives error:
Record 1: Rejected - Error on table STAGE_LEARNERS_ESTA, column HIRE_ON.
ORA-01840: input value not long enough for date format.

The stucture of stage_learners_esta
is
ID NUMBER
Hire_on DATE
Term_on DATE

Thanks IN Advance..

 

[ddiamond]

Without specifying your date format, your CSV file needs to store dates in the format dd-mmm-yyyy.

3,"12-feb-2004","01-sep-2005"
7,"23-dec-2004","30-oct-2005"

If you want to store the dates as mm/dd/yyyy, you need to specify that in the CTL file:

LOAD DATA
INFILE 'C:\TestingScripts\estalearner.csv'
APPEND
INTO TABLE stage_learners_esta
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ID "LTRIM(RTRIMD))" ,
HIRE_ON DATE "MM/DD/YYYY",
Term_on DATE "MM/DD/YYYY"
)

 

[syncdba]

Hi ,
When I have
(
Hire_date DATE
)
I got error: ORA-01840: input value not long enough for date format
&
If I add HIRE_DATE as
(
HIRE_DATE DATE "MM/DD/YYYY"
Gives error "Invalid Month"..

& I tried to change the format of HIRE_DATE as

(
HIRE_DATE DATE "DD/MM/YYYY"
)
I'm getting error:
ORA-01840: input value not long enough for date format

Any Help..Thanks in Advance..

 

[Beilstwh]

If your input file is truly like you specified, then your control file will not work. You specified that the data is variable length and is seperated by commas, your data (as displayed) is fixed length with no delimiters.

ID Hire_on Term_on
3 2/12/2004 9/1/2005
7 12/23/2004 10/30/2005

Is this actually what your flatfile looks like? If it is not, then cut and paste an actual sample so that we can write a proper control file for you.

Bill
Oracle DBA/Developer
New York State, USA