Skip navigation
Contact Us
3146 Views 15 Replies Latest reply: Feb 19, 2013 10:28 PM by Ganesh gan RSS
Ganesh gan Novice 86 posts since
Nov 20, 2012
Currently Being Moderated

Feb 4, 2013 4:58 AM

How to load a invalid source flat file

Hi,

 

 

           In my scenario i have one invalid flat file, i will try to load a target oracle DB.

 

 

SOURCE

 

    EMP_ID|EMP_NAME|LOCATION|SALARY|COMMISION|DEPTNO

     001|JOHN|AAA|5000|200|A001

     002|VIMAL|BBB|10000|500|B001

     003|

     GANGA|CCC|6000|100|A001

     004|NANCY|AAA|

     7000|500|B001

 

this type file i have delimited with pipe symbol. its fine to delimited. i will assign emp_is is primary key in target table ,but its loads

 

TARGET:

 

EMP_IDEMP_NAMELOCATIONSALRYCOMMDEPTNO

001

JOHNAAA5000200A001
002VIMALBBB10000500B001
003

GANGA

CCC6000100A001
004NANCYAAA7000500B001

 

plz help how to load target table correctly.

  • sasi ramesh Master 1,110 posts since
    Oct 2, 2010
    Currently Being Moderated
    Feb 4, 2013 3:51 AM (in response to Ganesh gan)
    How to load a invalid source flat file

    Hi Ganesh,

     

    In the source file you have the carraige return and line feed character.Please remove that using scripts and process the file in informatica.

     

    You can achieve this in presession command task.Write some scripts to replace the carraige return and line feed .

     

    REgards

    Sasiramesh

  • prasad reddy Newbie 7 posts since
    Jul 6, 2010
    Currently Being Moderated
    Feb 4, 2013 5:23 AM (in response to Ganesh gan)
    How to load a invalid source flat file

    Hi

     

    As per my understanding

    we can use the delimited file with pipesymbol

    you need to do change file properties and change the comma delimited file to " | "symbol  in other option .in session properties. or  after importing the src .

     

    Thanks

    Prasadreddy

    9916779173.

  • lavan kumar Apprentice 169 posts since
    Apr 13, 2011
    Currently Being Moderated
    Feb 4, 2013 6:43 AM (in response to Ganesh gan)
    Re: How to load a invalid source flat file

    Hi Ganesh,

     

     

    Use the Replace Function in Expression Transformatiion using "CHR(13)"

     

    REPLACECHR(0,COULUMN_NAME,CHR(13),'')

    CHR(13) for Carriage Return

    CHR(10) for New line Character

    I hope it will works for you..

     

    Thanks,

    Lavan

      • lavan kumar Apprentice 169 posts since
        Apr 13, 2011
        Currently Being Moderated
        Feb 5, 2013 2:54 AM (in response to Ganesh gan)
        How to load a invalid source flat file

        Hi Ganesh,

         

        Yes we cannot identify where in particular column having this character, Mostly as per my knowledge in Description or STRING data type Columns only come this character ...

         

        So you have to do cleansing the data for all the columns by creating Variable ports with the expression with below expression

         

        Input column -- AA

        AA is the input columns here

        V_Input-- REPLACECHR(0,AA,CHR(13),'')

        O_port -- V_input

         

         

        Thanks,

        Lavan

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Feb 8, 2013 12:15 AM (in response to Ganesh gan)
    How to load a invalid source flat file

    Hi Ganesh ,

     

    • Put this Unix Script in Command Task in Workflow  :

     

     

    #!/bin/ksh

    cat Input_File.txt > temp6

    grep -n '|$' temp6 > temp

    for i in `cat temp`

    do

    echo $i > temp1

    m=`cut -c 1 temp1`

    m1=`expr $m + 1`

    sed -n ''$m1' p' temp6 > temp2

    o=`cat temp2`

    sed -n ''$m','$m1' p' temp6 > temp3

    tr -d '\n' < temp3 > temp4

    n=`cat temp4`

    sed 's/'$o'/'$n'/g' temp6 > temp5

    cat temp5 > temp6

    done

    cat temp6 > Input_File.txt

    rm temp temp1 temp2 temp3 temp4 temp5 temp6

     

    • Now the Input_File.txt will contain the text as :

     

    001|JOHN|AAA|5000|200|A001

    002|VIMAL|BBB|10000|500|B001

    003|

    003|GANGA|CCC|6000|100|A001

    004|NANCY|AAA|

    004|NANCY|AAA|7000|500|B001

     

    • Create a Session Task next with the mapping created. The Mapping shud contain following Transformations :
    • Create a Source Qualifier with Input_File.txt as Source. The Source shud contain oly one column.
    • Create an Expression Transformation with following ports :

    COL - Input / Output Port

    FLAG - Output Port with Expression IIF(INSTR(COL1,'|',-1,1) != LENGTH(COL1),1,0)


    • Create a Filter Transformation with condition FLAG=1
    • Now , The Output will be ,

     

    001|JOHN|AAA|5000|200|A001

    002|VIMAL|BBB|10000|500|B001

    003|GANGA|CCC|6000|100|A001

    004|NANCY|AAA|7000|500|B001

     

    • The Overall Flow is

     

    Start  -->  CMD_TASK  -->  SESSION_TASK

More Like This

  • Retrieving data ...

Bookmarked By (1)