• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

sqlloader

My input file has a certain date field in this format - 01OCT2003:00:00:00,
and it sqlloader I have the date defined as :
LOAD DATA
INFILE 'test.csv'
   INSERT INTO TABLE s48.mytab
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ACCT_NBR,
 AT_NBR,
 AT_ACCT_NBR,
 ND_ACCT_NBR,
 JOIN_DT                     DATE 'mm/dd/yyyy',
 A_CLOSE_DT                  DATE 'mm/dd/yyyy',
 T_TYPE
  )

and I am getting several records rejected -log file shows this as reason --
Record 47: Rejected - Error on table INAL, column JOIN_DT.
ORA-01861: literal does not match format string

Record 48: Rejected - Error on table INAL, column JOIN_DT.
ORA-01843: not a valid month

PLEASE NOTE that I have some columns in the sqlloader without the dattype becuase this table does has some fields as number(8) and some other columns as number


0
Sara_j_11
Asked:
Sara_j_11
  • 3
  • 2
  • 2
1 Solution
 
tigin44Commented:
open the inpu file in excel and examine values in the column JOIN_DT. Its highly probably that some records have mulformed data in the month abbreviation part.. You may find out that by taking the substrig of the field.
0
 
Sara_j_11Author Commented:
what I tried to do is to correct the table and have everything in the right for- removed all the number nad replaced with number(10) or something that was relevat. But I get a different error this time
SQL*Loader-350: Syntax error at line 5.
Expecting "," or ")", found "NUMBER".
(ACCT_NBR                                     NUMBER(21),
0
 
tigin44Commented:
your input file has bad data... One another solution could be storing the JOIN_DT field to varchar field and examine the bad formated records in sql...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if data is like "01OCT2003:00:00:00" then why are you having the below format :

JOIN_DT                     DATE 'mm/dd/yyyy',
 A_CLOSE_DT                  DATE 'mm/dd/yyyy',

can you try :

JOIN_DT                     DATE 'dmonyyyy:hh24:mi:ss',
a_close_dt date 'dmonyyyy:hh24:mi:ss'

can you attach your data file and table structure and i can help to give you the control file for it.
0
 
Sara_j_11Author Commented:
the reason y I have that even though the input file has date like this :::"01OCT2003:00:00:00"
, when I load into table, I want to load it as 10/01/2003 - that is how it is stored in that table versus along with the time component. Is that the right approach... maybe that is the problem...
0
 
Sara_j_11Author Commented:
I am sure that there is no data issue with the input file. I have verified the rest of my control file. Just unsuire about the part of control file that has this date.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you try :

LOAD DATA
INFILE 'test.csv'
   INSERT INTO TABLE s48.mytab
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ACCT_NBR,
 AT_NBR,
 AT_ACCT_NBR,
 ND_ACCT_NBR,
 JOIN_DT           DATE "to_date(:join_dt, 'ddmonyyyy:hh24:mi:ss')",
 A_CLOSE_DT          DATE "to_date(:a_close_dt, 'ddmonyyyy:hh24:mi:ss')",
 T_TYPE
  )

Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now