DSXchange

Hi All,

I have csv file as given below and I need to make csv file like example below, so could you please provide me idea how to get it using DataStage job stages.

CSV File:

EmpId EmpName KeyWords
100 Rick "Update,Delete"

Basically KeyWords needs to be put into separate rows as shown in the example given below. Key words may be 2 or more, we never know. I just given example with two key words to get idea.

EmpId EmpName KeyWords
100 Rick Update
100 Rick Delete


Thank you in advance.



Thanks,
Ven

You can achieve this by using transformer stage. In transformer stage use loop variables to loop though each line and extract the keywords one at a time, using comma as delimiter in field function. Then create multiple rows for each loop value of keyword.

Thank you very much for your reply.

Do you have any sample code for Transformer loop and Field function for your suggested solution.




Thanks,
Ven

Transformer
Loop Variables :
lvcnt : count(KeyWords,',')
lvfield : field(KeyWords,',',lvcnt+1)
lvLoop : if left(lvfield,1) <> '"' or right(lvfield,1) <> '"' then lvfield else if left(lvfield,1) = '"' then lvfield[2,len(lvfield)] else if right(lvfield,1) = '"' then lvfield[1,len(lvfield)-1] else SetNull()
Loop While : @ITERATION <= lvcnt + 1

Column Derivation :
Empid, EmpName, Keywords
Empid, EmpName, lvLoop

Thank you very much. the sample code is working fine for me.


Thanks,
Ven