1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with sql loader control file date format

Discussion in 'Other Development Tools' started by anijan, Mar 3, 2010.

  1. anijan

    anijan Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    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.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,889
    Likes Received:
    246
    Trophy Points:
    1,155
    I am curious, why can't you just do a find and replace in excel?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,131
    Likes Received:
    303
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    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')",
    ...)
     
  4. anijan

    anijan Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Still im getting the same error :(
    ORA-01821: date format not recognized

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

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try with boundfiller.. .


    Code (SQL):


    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
     

    Code (SQL):

    SQL> SELECT * FROM date_table;

           SEQ DATEQ
    ---------- ---------
             1 05-MAY-80
             2 01-JAN-10
             3 01-JAN-10
             4 01-JAN-08

    SQL>
     
     
  6. anijan

    anijan Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    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.?
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.
     
  8. anijan

    anijan Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    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..
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I assume your table has two columns .

    ASSET &
    INSTATE

    And the file have two fields

    ASSET &
    INSTATE

    Then the code should look like.

    Code (SQL):

    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'))"
    )
     
     
  10. anijan

    anijan Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks a lot Raj.. :hurray
    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.?