sqlldr and bad dates

From: Richard Drive <dotacion_at_excite.com>
Date: 21 Nov 2001 09:12:00 -0800
Message-ID: <78586940.0111210912.9f09390_at_posting.google.com>


I am using SQLLDR to load data from a fixed length file using this ctl file:

LOAD DATA
-- INFILE D:\jul1501a.txt
INSERT INTO TABLE CDDR51.CDDR_PEOPLE
TRAILING NULLCOLS

	(         CDDR_ID DECIMAL EXTERNAL "CDDR51.CDDR_PEOPLE_ID_S.NEXTVAL",
                  FILE_ID position (1:9) char, 
                LAST_NAME position (10:24) char,
	       FIRST_NAME position (25:33) char,
	               MI position (34:34) char,
	          ADDRESS position (35:54) char,
	             CITY position (55:69) char,
	       BIRTH_DATE position (70:74) DATE "MMDDYYYY" NULLIF (BIRTH_DATE
="some condition"),
            CREATION_DATE SYSDATE
	)

... but which won't work of course because the daet is evaluated in the data source, occasionally some of the date columns have invalid dates- not always null, just bad or incorrect data, e.g.:

772660018SMITH      SUSAN     691 N ROVER ROAD    SAN JOSE   06311952
There is no 6/31/1952                

772660018SMITH      GEOFF     691 N ROVER ROAD    SAN JOSE       1952
The data is invalid, year only

772660018SMITH JAMIE 691 N ROVER ROAD SAN JOSE It's null, but does not as often occur

I would prefer to just load these columns as null, but I'm not finding the correct syntax.

Any comments or suggestions?

I'd prefer not to edit the bad file - there are too many records.

telnet://towel.blinkenlights.nl

Appreciated

--
Dick
Received on Wed Nov 21 2001 - 18:12:00 CET

Original text of this message