This discussion is archived
12 Replies Latest reply: Aug 30, 2010 8:09 AM by 769283 RSS

external table skip rows and trim

769283 Newbie
Currently Being Moderated
Hi,

I am using external table to load data from a file. The file structure is

Fiedl1 | Fiedl2 | Fiedl3 | Fiedl4 | Fiedl5 |
====================================
| X | X | X | X | X |
====================================
| X | X | X | X | X |
====================================
I need two things here
1. After every data record
====================================
line should be ignored.
2. i need to trim each field.

The delimiter for the file is '|'

Can any body please suggest me the external table syntax.

Many thanks in advance.
Yeswanth

Edited by: user12957398 on Aug 30, 2010 5:18 AM
  • 1. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    I need two things here 
    1. After every data record
    ====================================
    line should be ignored.
    I donot think you can do it from external table, you need to manual remove it.
    2. i need to trim each field. 
    Yes you can trim the data check this :-

    http://www.macs.hw.ac.uk/dept/facil/guides/oracle_9i/doc/server.901/a90192/ch12.htm


    Cheers
    Nawneet
  • 2. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    Hi Nawneet

    Regarding the trim option:

    Currently my external table is:

    CREATE TABLE "NRS_MIH_MIHUB_STAGING"."EXT_DELAYS_MINS_CANCELLATIONS"
    (
    FIELD1 varchar2(20),
    FIELD2 VARCHAR2(20),
    FIELD3 DATE,
    FIELD4 varchar2(20),
    FIELD4 varchar2(20),
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TABLES" ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE BADFILE BAD_DIR:'FILE.BAD' LOGFILE LOG_DIR:'FILE.LOG'
    DISCARDFILE DIS_DIR:'FILE.DSC' SKIP 1 FIELDS
    TERMINATED BY '|' MISSING FIELD VALUES ARE NULL
    (
    FIELD1,FIELD2,FIELD3 DATE 'DD/MM/YYYY HH24:MI:SS',FIELD4,FIELD5
    )
    ) LOCATION ( 'FILE.CSV' )
    )
    REJECT LIMIT UNLIMITED;

    Can you please tell me where should i include trim option.

    Many thanks in advance
    Yeswanth
  • 3. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    Try this
    CREATE TABLE "NRS_MIH_MIHUB_STAGING"."EXT_DELAYS_MINS_CANCELLATIONS"
    (
    FIELD1 varchar2(20),
    FIELD2 VARCHAR2(20),
    FIELD3 DATE,
    FIELD4 varchar2(20),
    FIELD4 varchar2(20),
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TABLES" ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE BADFILE BAD_DIR:'FILE.BAD' LOGFILE LOG_DIR:'FILE.LOG'
    DISCARDFILE DIS_DIR:'FILE.DSC' SKIP 1 FIELDS 
    TERMINATED BY '|' 
    LRTRIM
    MISSING FIELD VALUES ARE NULL 
    (
    FIELD1,FIELD2,FIELD3 DATE 'DD/MM/YYYY HH24:MI:SS',FIELD4,FIELD5
    )
    ) LOCATION ( 'FILE.CSV' )
    )
    REJECT LIMIT UNLIMITED;
    Cheers
    Nawneet
  • 4. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    Hi Nawneet,

    Thanks for immediate reply.

    I tried it. But data is not getting trimmed. Actually I have a datetime field in DD/MM/YYYY HH24:MI:SS format in my file

    _16/08/2010 08:12:24 _
    (note the space after 24)

    When i tried to load into external table with RTRIM
    i got the following error in the log file:
    KUP-04021: field formatting error for field FIELD3
    KUP-04026: field too long for datatype

    My System timestamp is 'DD-MON-YY HH.MI.SS'

    So is the error is with TRIM function or date format??

    Please advice me

    Thanks
    Yeswanth
  • 5. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    Hi Nawneet,

    Thanks for immediate reply.

    I tried it. But data is not getting trimmed. Actually I have a datetime field in the format DD/MM/YYYY HH24:MI:SS in my file
    Eg:
    "16/08/2010 08:12:24 "
    (note the space after 24)

    When i tried to load into external table with RTRIM
    i got the following error in the log file and no record got loaded:
    KUP-04021: field formatting error for field FIELD3
    KUP-04026: field too long for datatype

    here FIELD3 is TIMESTAMP(not date as mentioned previously)

    My System timestamp is '30-AUG-10 07.06.35.545000000 PM'

    I tried to load after removing the space charector after 24. Still the same error

    Please advice me

    Thanks
    Yeswanth
  • 6. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    LRTRIM will trim spaces form the left and right side but not _ (underscode)
    that why you got the the below row in bad file.
    and as error told the length is greater then expected.
    KUP-04026: field too long for datatype

    _16/08/2010 08:12:24 _


    Even if you want to load the data you need to make it varchar column.


    Cheers
    Nawneet
  • 7. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    if you want to load the timestamp use

    FIELD3 TIMESTAMP
  • 8. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    Nawneet,

    I mentioned '-' to indicate the space charector. In the next post I have corrected it. In my source file there is no '-'.

    I have used Timestamp for FIED3. But still the same error

    Please suggest some other way other than loading as varchar.

    Thanks,
    Yeswanth
  • 9. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    can you paste the create table script and the record having error.


    Cheers
    Nawneet
  • 10. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    Nawneet,

    Table script:

    CREATE TABLE Table
    (
    "Field1" VARCHAR2(20 BYTE),
    "Field2" NUMBER,
    "Field3" VARCHAR2(50 BYTE),
    "Field4" VARCHAR2(50 BYTE),
    "Field5" VARCHAR2(50 BYTE),
    "Field6" VARCHAR2(100 BYTE),
    "Field7" NUMBER,
    "Field8" VARCHAR2(25 BYTE),
    "Field9" VARCHAR2(50 BYTE),
    "Field10" TIMESTAMP,
    "Field11" TIMESTAMP,
    "Field12" VARCHAR2(100 BYTE),
    "Field13" VARCHAR2(50 BYTE),
    "Field14" VARCHAR2(100 BYTE),
    "Field15" VARCHAR2(100 BYTE),
    "Field16" VARCHAR2(100 BYTE),
    "Field17" VARCHAR2(100 BYTE),
    "Field18" VARCHAR2(50 BYTE),
    "Field19" NUMBER,
    "Field20" NUMBER
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TABLES" ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE BADFILE BAD_DIR:'FILE.BAD' LOGFILE LOG_DIR:'FILE.LOG'
    DISCARDFILE DIS_DIR:'FILE.DSC' SKIP 1 FIELDS TERMINATED BY ',' RTRIM MISSING FIELD VALUES ARE NULL
    (
    Field1,Field2,Field3,Field4,Field5, Field6,
    Field7,Field8,Field9, Field10 DATE "DD/MM/YYYY HH24:MI:SS", Field11 DATE "DD/MM/YYYY HH24:MI:SS",
    Field12,Field13,Field14,Field15, Field16,Field17,Field18,Field19,Field20))
    LOCATION ( 'FILE.CSV' )
    )
    REJECT LIMIT UNLIMITED;

    Source data without header:
    "2010/11_P01",606056.00,"London North Eastern","LNE",,"AmeyCOLAS HO",79.00,"112677","Lee Walker","16/08/2010 08:12:24 ",28/11/2009 00:00:00,"Loversall Carr Jn","Attribution Disputed","T90 D/F DONCLCJ","104A","JS","Condition of Track TSR Outside Rules of Route","COTTSR ORR",,,606056

    Thanks,
    Yeswanth
  • 11. Re: external table skip rows and trim
    682825 Expert
    Currently Being Moderated
    i changed the code a bit , now this is working
    CREATE TABLE Table_ext 
    (
    Field1 VARCHAR2(20 BYTE),
    Field2 NUMBER,
    Field3 VARCHAR2(50 BYTE),
    Field4 VARCHAR2(50 BYTE),
    Field5 VARCHAR2(50 BYTE),
    Field6 VARCHAR2(100 BYTE),
    Field7 NUMBER,
    Field8 VARCHAR2(25 BYTE),
    Field9 VARCHAR2(50 BYTE),
    Field10 TIMESTAMP,
    Field11 TIMESTAMP,
    Field12 VARCHAR2(100 BYTE),
    Field13 VARCHAR2(50 BYTE),
    Field14 VARCHAR2(100 BYTE),
    Field15 VARCHAR2(100 BYTE),
    Field16 VARCHAR2(100 BYTE),
    Field17 VARCHAR2(100 BYTE),
    Field18 VARCHAR2(50 BYTE),
    Field19 NUMBER,
    Field20 NUMBER
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY MTH_FILES_LOCATION ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE BADFILE MTH_FILES_LOCATION:'FILE2.BAD' LOGFILE MTH_FILES_LOCATION:'FILE2.LOG' 
    DISCARDFILE MTH_FILES_LOCATION:'FILE2.DSC' SKIP 1 FIELDS TERMINATED BY ',' RTRIM MISSING FIELD VALUES ARE NULL 
    ( 
    Field1,Field2,Field3,Field4,Field5, Field6,
    Field7,Field8,Field9, Field10 DATE 'DD/MM/YYYY HH24:MI:SS', Field11 DATE 'DD/MM/YYYY HH24:MI:SS',
    Field12,Field13,Field14,Field15, Field16,Field17,Field18,Field19,Field20)) 
    LOCATION ( 'FILE2.CSV' )
    )
    REJECT LIMIT UNLIMITED;
    File with header
    Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10,Field11,Field12,Field13,Field14,Field15,Field16,Field17,Field18,Field19,Field20
    2010/11_P01,606056.00,London North Eastern,LNE,,AmeyCOLAS HO,79.00,112677,Lee Walker,16/08/2010 08:12:24 ,28/11/2009 00:00:00,Loversall Carr Jn,Attribution Disputed,T90 D/F DONCLCJ,104A,JS,Condition of Track TSR Outside Rules of Route,COTTSR ORR,,,606056
    Removed the double quote from table definition and file also and change the location accordingly

    Cheers
    Nawneet
  • 12. Re: external table skip rows and trim
    769283 Newbie
    Currently Being Moderated
    It worked perfectly, Thanks alot for your time Nawneet.

    Thanks,
    Yeswanth

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points