Forum Stats

  • 3,826,862 Users
  • 2,260,714 Discussions
  • 7,897,105 Comments

Discussions

How to filter records of specific length from a Fixed Width flat file in ODI?

2628428
2628428 Member Posts: 8
edited Apr 2, 2014 2:24AM in Data Integrator

Hi Experts,

I am reading a fixed width flat file of 200 characters. The requirement is that if there are any records

of less than 200 -> reject it and load it in error file

of more than 200 -> reject it and  load it in error file

equal to 200  -> let it load to the target table.

Is there any way we can achieve it in ODI tool? Any quick help is highly appreciated!

Tagged:
FPonte

Answers

  • JeromeFr
    JeromeFr Data Integration Consulting Solutions Architect | A-Team Cloud Solution Architects Member Posts: 1,697 Gold Trophy

    Hello,

    If there is only one column per line, you can use define it as a delimited file, using the carriage return to define a new row and any character than will never be on a line as delimiter.

    Then if you use ODI 11g:

    - Create two interfaces, one with a filter LENGTH(column_name) = 200 that load the table and one with filter LENGTH(column_name) <> 200 to load the error file.

    - Or set a constraint on your datastore : LENGTH(column_name) = 200. Then run a static control on that datastore into a package before processing the interface to load all the error into an error table. You can then process this table to load a file. When processing the interface, don't forget to add a filter with the same condition.

    If you use ODI 12c:

    Use the split operator to load rows with LENGTH(column_name) = 200 into the target table and the others into the error file within the same mapping.

    Hope it helps.

    Regards,

    JeromeFr

    FPonteJeromeFr
  • 2628428
    2628428 Member Posts: 8

    Hi Jerome,

    Thank you for you response. The solution you gave is assuming that there is only one column. But actually it has multiple columns of different lengths in the file. I have tried the second option you suggested under 11G. But its not working. I created ODI condition  under constraint and captured the column length. But ODI is not able to calculate the correct length.  Had a bad day trying to get around it . Any help is greatly appreciated.

  • JeromeFr
    JeromeFr Data Integration Consulting Solutions Architect | A-Team Cloud Solution Architects Member Posts: 1,697 Gold Trophy

    If you have multiple columns of different length on the same row, it must be delimited.

    Do you have a character that acts as delimiter? You can you tell it's the end of a column and the start of a new one?

    You can set the table with the right delimiter, reverse-engineer the columns from the column tab and use LENGTH(column_name) = 200 as explained above.

    Don't hesitate to post a small sample of data and the error message you get so we can easily understand the problem.

    Regards,

    JeromeFr

  • 2628428
    2628428 Member Posts: 8

    Hi,

    So the data is like, in the below record  its known from position 1-3 is column 1, position 4-6 is column 2 and position 7-9 is column 4. This is the fixed width file. When I create the Data store for the file i select fixed width and and create the columns based on the known column positions.

    Based on your suggestion what i did is, created a filter something like LENGTH (Col 1)+LENGTH (Col 2)+LENGTH (Col 3)=9. so that only records with length 9 pass through. But that's when i realized  a weird problem with ODI. The ODI length function while calculating the length of a string in a column inherently trims it and then counts the length. So in the below example the third column of third record has the values as space. So even though the length of the whole record is 9..the value calculated for the third record is 6. In my requirement it is possible for a column to have space. If you see in oracle or Informatica, when they calculate the length , they do consider the spaces too.

    ABCKLMXYZ

    LKJASJOPU

    RTYPDH  

  • JeromeFr
    JeromeFr Data Integration Consulting Solutions Architect | A-Team Cloud Solution Architects Member Posts: 1,697 Gold Trophy

    This is strange because ODI doesn't actually check that condition itself, it pushes it down to the Oracle Database. So the result should take the spaces into account. So it might be the LKM that doesn't take the spaces into account.

    Could you check in the C$_ table if the Col3 contains spaces or null?

This discussion has been closed.