Home » RDBMS Server » Server Utilities » SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help !
SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help ! [message #224704] Thu, 15 March 2007 03:40 Go to next message
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.
Re: SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help ! [message #224765 is a reply to message #224704] Thu, 15 March 2007 07:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
See if this helps:

SQLLDR, dates and null columns.

[Updated on: Thu, 15 March 2007 07:06]

Report message to a moderator

Re: SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help ! [message #224768 is a reply to message #224765] Thu, 15 March 2007 07:20 Go to previous message
jamescatania
Messages: 2
Registered: March 2007
Location: Malta
Junior Member

No it doesn't help , I have searched the forum and tried everythign I could find.. but I solved the problem I think..

I was doing FIELDS TERMINATED BY " " optionally enclosed by '"'

all I had to do was delimit with X'09' (the hex for TAB)
and then this worked

field_3 DATE 'dd/mm/yyyy' NULLIF field_3=''

and voila !
Previous Topic: sqlldr: what's wrong?
Next Topic: Exporting schema of complete database for all the users
Goto Forum:
  


Current Time: Sun Dec 04 14:42:02 CST 2016

Total time taken to generate the page: 0.17156 seconds