12 Replies Latest reply: Aug 30, 2010 10:09 AM by 769283 RSS

    external table skip rows and trim

    769283
      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
          Nawneet_Aswal
          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
            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
              Nawneet_Aswal
              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
                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
                  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
                    Nawneet_Aswal
                    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
                      Nawneet_Aswal
                      if you want to load the timestamp use

                      FIELD3 TIMESTAMP
                      • 8. Re: external table skip rows and trim
                        769283
                        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
                          Nawneet_Aswal
                          can you paste the create table script and the record having error.


                          Cheers
                          Nawneet
                          • 10. Re: external table skip rows and trim
                            769283
                            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
                              Nawneet_Aswal
                              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
                                It worked perfectly, Thanks alot for your time Nawneet.

                                Thanks,
                                Yeswanth