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>
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 1952The 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
-- DickReceived on Wed Nov 21 2001 - 18:12:00 CET