| SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help ! [message #224704] |
Thu, 15 March 2007 03:40  |
jamescatania
Messages: 2
Registered: March 2007
Location: Malta
|
Junior Member |
|
|
Dear All, I would appriciate some help with this.
I have this set of data
which is TAB delimited,
XX XXXXXXXXXXX XXXXXX XXXXXXXX X XX XX XXX (empty date) XXXX X.XX XX.XX XX.XXX XXXX XXXXXX XX.XXX dd/mm/yyyy X.XXXX XX XXXXXXX X.XXX XXX XXX
Now the problem is that where there is 00/00/0000 t(I PUT THAT THERE) its a null value, its just empty space, I am attaching the control file
LOAD DATA
INFILE 'X:\$\$\$$$$.csv' BADFILE 'X:\$\$\$$$$.bad'
APPEND
INTO TABLE testtable.TABLE_NAME
FIELDS TERMINATED BY " " optionally enclosed by '"'
TRAILING NULLCOLS
(
FIELD_1,
FIELD_2,... (ETC)
FIELD_3 DATE 'dd/mm/yyy' NULLIF FIELD_3='',
FIELD_4,
FIELD_5 etc.
)
I also tried these
FIELD_3 "TO_DATE(:FIELD_3,'dd/mm/yyyy')" NULLIF(:FIELD_3=BLANKS)
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3=BLANKS,
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3="",
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3='',
The Log file shows
Record 1: Rejected - Error on table testtable.Table_name, column FIELD_3.
ORA-01843: not a valid month
The whole point is that its empty, and for sure its not a valid month, but its not recognizing it, when I push a NULL value as a constant on SQL*Loader it works.
.....
I also have another problem where some of my data instead of the date the clients fill in a "0" or a "3*, there is no way of changing the DATA file, as this is a data dump from the clients.
I also read that the BLANKS keyword does nto work on tabs, so I tried everythign, believe me, everythign, I spent about 8 hours on this already with no progress.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
