Forum Stats

  • 3,826,625 Users
  • 2,260,682 Discussions
  • 7,897,041 Comments

Discussions

How to Change Jan to 001 using ODI

656860
656860 Member Posts: 41
edited Sep 29, 2008 10:11AM in Data Integrator
Hi,

I have source file (flat file) with tab delimited data:
Jan 15000
Feb 20000
Mar 10000

And I want to change the Jan to 001, Feb to 002, Mar to 003 using ODI. The target file is another flat file.

Can anyone help me to solve this problem?

Thanks all for the attention.

Regards,
Sumardi
Tagged:

Best Answer

  • geeo73
    geeo73 Member Posts: 54
    edited Sep 29, 2008 10:11AM Answer ✓
    Hi,

    just to follow-up, I have a SQL Server staging database and transferred from file-to-file and used the Staging area to do the SQL transformation using a CASE statement:

    CASE DAT.C1

    WHEN 'Jan' THEN '001'
    WHEN 'Feb' THEN '002'
    ELSE '000'

    END

    The KM's I used were:

    LKM File to SQL

    and

    IKM SQL to File Append

    Hope this helps,

    G

    Edited by: geeo on Sep 29, 2008 3:10 PM

Answers

  • 632265
    632265 Member Posts: 322
    Hi,

    Can you please use SUBSTITUTE by choosing Microsoft Excel Technology.... I havnt tried can you chk at your end .....

    Have u tried this ....


    Thanks
    Ananda
  • geeo73
    geeo73 Member Posts: 54
    What are you using as your Staging Area?

    If you are using an RDBMS or the SUNOPSIS Engine then you could do a SQL Transformation in the staging area before loading it into the target file

    Hope this helps

    G
    geeo73
  • geeo73
    geeo73 Member Posts: 54
    edited Sep 29, 2008 10:11AM Answer ✓
    Hi,

    just to follow-up, I have a SQL Server staging database and transferred from file-to-file and used the Staging area to do the SQL transformation using a CASE statement:

    CASE DAT.C1

    WHEN 'Jan' THEN '001'
    WHEN 'Feb' THEN '002'
    ELSE '000'

    END

    The KM's I used were:

    LKM File to SQL

    and

    IKM SQL to File Append

    Hope this helps,

    G

    Edited by: geeo on Sep 29, 2008 3:10 PM
This discussion has been closed.