Help with SQLLDR - how to use a control file to process multiple date formats for the same column?

[Jim]

Hi,

In my control file I process dates with
RETURN_DATE date "YYMMDD" NULLIF RETURN_DATE = '0'. Similar code
exists for the other date related columns.
This does the trick for about 200 of the 20000 records. The date is
some times 6 digits and sometimes 5, 4 or 3 digits. As the date could
be 000101 (January 01, 2000) - but the text file just contains the
number 101 (the extract from the old DOS system drops the leading
zeros).

How can make the control file to process multiple date formats for the
same column?

[Chirag]

Hi,

Pl. try following format in control file.

RETURN_DATE DATE "LPAD(TO_CHAR(:RETURN_DATE),6,'0')"

With regards,

CHIRAG SHAH
Oracle Administator

jim_...@hotmail.com (Jim) wrote in message news:<f0e3dc0b.01060...@posting.google.com>...

[Jim]

Thanks!

I have tried it.

In my control file I have code like this:
...
START_DATE1 "to_date((lpad(:START_DATE1,'6','0')), 'YYMMDD')" ,
STATUS,
END_DATE "to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')" ,
...

But the bad thing is if one of the many date fields contain zeros that
row is ejected - I have nearly 10 date fields in each row, and one or
more of them is usally zero. The Above statements do work for the
cases when none of the date cells are zero (about 4 out of 20 000 will
pass)
I need to write a statement that will handle the null case and put the
correct value in - help?

I have tried code like this:
END_DATE date "to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')" nullif
END_DATE = '0'
and I have tried the suggestion of:

RETURN_DATE DATE "LPAD(TO_CHAR(:RETURN_DATE),6,'0')"

But I get an error saying that the date is not formated correctly.
Which I understand as the 'DATE' is looking for a mask. How can it
also handle the NULL case.

Any one?

ora...@rediffmail.com (Chirag) wrote in message news:<2706fcc3.01061...@posting.google.com>...

[Jim]

Hello,

I have worked out this so far in my control file:

...
START_DATE "decode(:START_DATE, '0', NULL,
(to_date((lpad(:START_DATE,'6','0')), 'YYMMDD')))",
STATUS NULLIF STATUS = blanks,
END_DATE "decode(:END_DATE), '0', NULL,
(to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')))",
...

I believe this would handle the case when the date is a little
different, and the date is zero, '0'. I can only believe this will
work as when I run SQLLDR I get a new error:
Rejected - Error on table MY_TESTING.
ORA-00947: not enough values

I have check the number of columns of MY_TESTING and stated in my
Control file and they are exactly the same. So I am not sure if this
error is exactly correct. (I have something like 85 columns I am
trying to load) Is there a Max to the number of columns that SQLLDR
can load in?

Jim

ora...@rediffmail.com (Chirag) wrote in message news:<2706fcc3.01061...@posting.google.com>...