Problem with sql loader control file date format

[anijan]

I have table with a column defined like
INSTATE_DATE DATE
I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
I tried to modify the control file like
INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
Even then im getting the error "ORA-01821: date format not recognized"
Please let me know if there are any other way to achive this.

[Sadik]

I am curious, why can't you just do a find and replace in excel?

[zargon]

I have table with a column defined like
INSTATE_DATE DATE
I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
I tried to modify the control file like
INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
Even then im getting the error "ORA-01821: date format not recognized"
Please let me know if there are any other way to achive this.

Do this in the .ctl file:

..
into table ...
WHEN instate_date = '00/00/0000'
(...
instate_date date "to_date('01/01/4000','MM/DD/RRRR')",
...)
into table ...
when instate_date <> '00/00/0000'
(...
instate_date date "to_date(:instate_date,'MM/DD/RRRR')",
...)

[anijan]

Still im getting the same error
ORA-01821: date format not recognized

is it becos of the invalid date 00/00/0000.?

[rajavu]

Try with boundfiller.. .

 
LOAD DATA
INFILE *
INTO TABLE date_table
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
seq INTEGER external,
xyz boundfiller,
dateq "NVL(Decode(:xyz,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:xyz,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy'))"
)
BEGINDATA
1,05/05/1980
2,
3,00/00/0000
4,01/01/2008

SQL> SELECT * FROM date_table;
 
       SEQ DATEQ
---------- ---------
         1 05-MAY-80
         2 01-JAN-10
         3 01-JAN-10
         4 01-JAN-08
 
SQL>

[anijan]

xyz boundfiller,
dateq "NVL(Decode(:xyz,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:xyz,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy'))"

can u explain what is dateq.? do i have to give a comma next to bounfiller.?

[rajavu]

Boundfiller is user to skip the column for time being, but they should be used in another field.
Filler and Boundfiller

Here Seq and Dateq are the column names in tables. Second field in the file is treated as boudfiller and used against Dateq for building a logic.

There is comma after Boundfiller.

[anijan]

Thanks for ur quick reply Raj.

Sorry to trouble you again.

if I frame my control file like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ASSET CHAR "decode(:ASSET,null,' ',:ASSET)",
INSTATE boundfiller,
NEW_INSATE CHAR "decode(:NEW_INSATE,null,' ',:NEW_INSATE)",
)
then the column INSTATE is skipped while loading.
but the invalid date 00/00/0000 will not be present in all the records of the csv file. so we cannot skip the entire column.

As you suggested i used decode like,
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ASSET CHAR "decode(:ASSET,null,' ',:ASSET)",
INSTATE DATE "NVL(Decode(:INSTATE_DATE,'00/00/0000',TO_DATE('01/01/9000','dd/mm/yyyy'),TO_DATE(:INSTATE,'dd/mm/yyyy')),TO_DATE('01/01/9000','dd/mm/yyyy'))",
NEW_INSATE CHAR "decode(:NEW_INSATE,null,' ',:NEW_INSATE)",
)

still im getting date format not recognised error.
Pls correct if im wrong..

[rajavu]

I assume your table has two columns .

ASSET &
INSTATE

And the file have two fields

ASSET &
INSTATE

Then the code should look like.

LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ASSET CHAR "NVL(:ASSET,' ')",
NEW_INSATE boundfiller,
INSTATE DATE "NVL(Decode(:NEW_INSATE,'00/00/0000',TO_DATE('01/01/9000','dd/mm/yyyy'),TO_DATE(NEW_INSATE,'dd/mm/yyyy')),TO_DATE('01/01/9000','dd/mm/yyyy'))"
)

[anijan]

Thanks a lot Raj..
It is working absolutely fine.

I have one more query.

I have a column ASSET in my CSV file. But the column is not defined in the table. how do i skip this column in CSV file while loading it to the table. should the control file be modified.?