#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0

    Getting error in the Control File Format !!


    Hi,

    I am having a datafile like this.

    T1Col1`T1Col2`T1Col3`T2Col1`T2Col2`T2Col3`T2Col4`T2Col5`T3Col1`T3Col2`T3Col3`T3Col4
    ```T2Col1`T2Col2`T2Col3`T2Col4`T2Col5`T3Col1`T3Col2`T3Col3`T3Col4
    ````````T3Col1`T3Col2`T3Col3`T4Col4

    and so on.

    I have just taken a single table and tried to write control file like this.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

    OPTIONS (SKIP=1)
    Load Data
    INFILE 'C:\DataFiles\DataFile.dat'
    BADFILE 'C:\BadFiles\BadRecord.bad'
    APPEND
    INTO TABLE TABLE1
    WHEN T1Col1 !=' '
    FIELDS TERMINATED BY "`"
    TRAILING NULLCOLS
    (
    T1Col1
    ,T1Col2
    ,T1Col3
    )

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I am removing first line from the data file as it is of no use. The data is arranged like Table1 data followed by Table2Data Followed by Table3 Data and if table3 is having many rows then in the next line all other columns of all othe tables will be null and only delimiter will be there. I have shown till table3 here for simplicity, i am having more tables like that in the original file.

    And to get all the table's data I have written INTO TABLE clause for other tables like this.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    OPTIONS (SKIP=1)
    Load Data
    INFILE 'C:\DataFiles\DataFile.dat'
    BADFILE 'C:\BadFiles\BadRecord.bad'
    APPEND
    INTO TABLE TABLE1
    WHEN T1Col1 !=' '
    FIELDS TERMINATED BY "`"
    TRAILING NULLCOLS
    (
    T1Col1
    ,T1Col2
    ,T1Col3
    )
    INTO TABLE TABLE2
    WHEN T2Col1 !=' '
    FIELDS TERMINATED BY "`"
    TRAILING NULLCOLS
    (
    T2Col1
    ,T2Col2
    ,T2Col3
    ,T2Col4
    ,T2Col5
    )
    INTO TABLE TABLE3
    WHEN T3Col1 !=' '
    FIELDS TERMINATED BY "`"
    TRAILING NULLCOLS
    (
    T3Col1
    ,T3Col2
    ,T3Col3
    ,T4Col4
    )
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

    I am getting WHEN clause failed error. I didn't put WHEN clause in one of my test and still i got error like this

    " 0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.
    "

    Can someone please tell me where i am making mistake ?

    My second question is if the names of the columsn in the datbase are not in the order as given in the Control file will that causes error. I am not having my columns in the order described above. So is that causing error ?

    Any help and hint to solve this problem would be highly appreciated.

    Thanks in the anticipation that I will get some reply.

    Regards,
    Dipesh
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    About the syntax in the control file:
    APPEND INTO TABLE TABLE1
    WHEN T1Col1 !=' '
    FIELDS TERMINATED BY "`"

    T1Col1 should probably be :T1Col1
    And about FIELDS TERMINATED BY "`". I always use single quotes as in FIELDS TERMINATED BY '`'

    To answer your second question: The order of the columns in the control file is independent of the order of the columns in the table.

    Good luck,
    Dan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    Hi Dan,

    Thanks for replying and informing me about the syntax usage.

    I am able to load the data properly now. But getting error in loading data into the database.
    I am getting date in this format.

    MM/DD/YYYY HH:MM.

    What should i write as my datatype for such column in the control file so that i will get properly loaded into the oracle database. In the database the datatype for that column is described as Date of length 7.

    So what should i mention in the control file ?
    Any help would be highly appreciated.

    Eagerly waiting for reply.

    Regards,
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    Let's say T3Col1 is the date field. Then the following should work:

    TRAILING NULLCOLS
    (
    T3Col1 DATE "MM/DD/YYYY HH:MM" ,
    ....


    Cheers,
    Dan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    Hi,

    Thanks it worked well. I gave something like this

    DATE "MM/DD/YYYY HH:MI:SS AM"

    Thanks for your help.

    Regards,

IMN logo majestic logo threadwatch logo seochat tools logo