none
Derived Column

    Question

  • "TestValue1,100,12/01/2007" this is the string coming from falt file sourse. i want convert this string into three columns in derived column. but i got error

    i have writen code like

    First column is : SUBSTRING(FlatOutputCol,1,FINDSTRING(FlatOutputCol,",",1) - 1)

    second col is :(DT_I4)(SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1),FINDSTRING(FlatOutputCol,",",2)))

    Thrid column is : (DT_DBTIMESTAMP)(SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",2) + 1,LEN(FlatOutputCol)))

    but i got error in second third column.

    plz clarify me .

     

    Friday, March 25, 2011 7:32 AM

All replies

  • Please check my blog for this and try to relate your case.

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Friday, March 25, 2011 7:39 AM
  • Hi ETL,

    I am geting only first column. remaining two  columns data not getting same time package execution also sucusess.

     I have done same thing


    (DT_I4)SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1) + 1,FINDSTRING(FlatOutputCol,",",2) - FINDSTRING(FlatOutputCol,",",1) - 1)
    Friday, March 25, 2011 8:59 AM
  • Hi,

    In your Flat File Source Connection configure the text qualifier as ". Once you configured Text Qualifier then goahead and use Comma DELIMETER. If you do this, your problem will be solved.

    Hope this will help!!

    Regards,

    Guru

    Friday, March 25, 2011 9:11 AM
  • Hi Guruprasath.

     

      i have done that one. but i am not able to get second and thrid column values

    Friday, March 25, 2011 9:28 AM
  • Can you use a data viewer to confirm that the column values? Also, check the column mappings in the destinaiton component.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, March 25, 2011 9:31 AM
  • Hi Guruprasath.

     

      i have done that one. but i am not able to get second and thrid column values

    check this for 2nd column...

    SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1) +1, FINDSTRING(FlatOutputCol,",",2) - FINDSTRING(FlatOutputCol,",",1)-1)

     

    for 3rd column

    SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",2) +1, LEN(FlatOutputCol) - FINDSTRING(FlatOutputCol,",",2))

     

    let me know your observation..

     

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Friday, March 25, 2011 9:34 AM
  • Hi ETL.

    same code i was written. . same problem.

     

     

    Friday, March 25, 2011 9:39 AM
  • hi, can we change any thing in flat file connection manager.
    Friday, March 25, 2011 9:50 AM
  • hi, can we change any thing in flat file connection manager.

    Please share your sample input data...

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Friday, March 25, 2011 10:00 AM
  • Hi etl,

    Value1,100,12/01/2007
    Value2,200
    Value3,300,12/01/2007
    Value4,400,12/01/2007
    Value5,500
    Value6,600,12/01/2007
    Value7,700,12/01/2007
    Value8,800
    Value9,900,12/01/2007
    Value0,1000,12/01/2007

    Friday, March 25, 2011 10:05 AM
  • Can you please paste the error message which you are getting?

    Thanks,

    Guru

    Friday, March 25, 2011 10:12 AM
  • i am not getting any error message.package executing succuess fully but not getting second and third columns.
    Friday, March 25, 2011 10:47 AM
  • why not you try "Delimited File Source".

    you can do this in derived column but its better if you use the above one..

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Friday, March 25, 2011 11:04 AM
  • no etl. i have to create the package with derived column as per doc
    Friday, March 25, 2011 11:14 AM
  • Hi etl,

    Value1,100,12/01/2007
    Value2,200
    Value3,300,12/01/2007
    Value4,400,12/01/2007
    Value5,500
    Value6,600,12/01/2007
    Value7,700,12/01/2007
    Value8,800
    Value9,900,12/01/2007
    Value0,1000,12/01/2007

    for getting 2nd value from the column ... its not able to find the next "," and fails. so for such type of files you have to use Script component as a source or as transformation next to your flat file source.

    for sample; you can check James's blog or Sudeep's Blog 

     

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Friday, March 25, 2011 11:17 AM
  • try these expression on your Derived column.. please replace the "C" to your actual Column name

    1st Column:

    SUBSTRING(C,1,FINDSTRING(C,",",1) - 1)

    2nd Column:

    FINDSTRING(C,",",2) != 0 ? SUBSTRING(C,FINDSTRING(C,",",1) + 1,FINDSTRING(C,",",2) - FINDSTRING(C,",",1) - 1) : SUBSTRING(C,FINDSTRING(C,",",1) + 1,LEN(C) - FINDSTRING(C,",",2))

     

    3rd Column:

    FINDSTRING(C,",",2) == 0 ? "NULL" : SUBSTRING(C,FINDSTRING(C,",",2) + 1,LEN(C) - FINDSTRING(C,",",2))

     

    I have tested above expression and its working perfectly...let me know your observation..

     

    Please check this link for your reference 


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    • Edited by ETL vs ELTL Friday, March 25, 2011 2:14 PM Adding link
    • Proposed as answer by ShobhitAwasthi Saturday, March 26, 2011 2:49 AM
    Friday, March 25, 2011 11:36 AM
  • In that case you can have a conditional split to split the records with 2 or 1 coma (,) and add a derived column on each output of the conditional split.

    Create 2 outputs from conditional split:

    • All3: FINDSTRING(FlatOutputCol,",",2) > 0
    • Only2: FINDSTRING(FlatOutputCol,",",2) == 0

    Add derived columns on both the outputs to create the columns and union all the derived column outputs.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, March 25, 2011 11:39 AM