topic Re: split comma separated values into different rows in Talend Studio

split comma separated values into different rows

Anonymous — Mon, 07 Jan 2019 12:36:47 GMT

Hi ,

I have a scenario , which contains comma separated values in 2 cells , I need to normalize both Fields .Like ,

ID	Name	Product	Qty
1	deep	p1,p2,p3	q1,q2,q3
2	subha	p11,p22,p33	q11,q22,q33

I need the output as :

1	deep	p1	q1
1	deep	p2	q2
1	deep	p3	q3
2	Subha	p11	q11
2	Subha	p22	q12
2	Subha	p33	q13

# I tried with tNormalize ( 2times for Product , Qty Fields) but rows are getting repeated with that.

Please give me a solution for that.

Thanks in advance

Regards

Subhadeep

Re: split comma separated values into different rows

akumar2301 — Mon, 07 Jan 2019 14:32:12 GMT

@ADee

Quest would be

1) 1st Normalise Product Col , add counter to each record number .

ID, Name , Product , Counter

1|John|p1|1
1|John|p2|2
1|John|p3|3

2|Shane|p11|1
2|Shane|p22|2
2|Shane|p33|3

2) Again for Quantity Col.

ID, Name , Quantity, Counter

1|John|q1|1
1|John|q2|2
1|John|q3|3

2|Shane|q11|1
2|Shane|q22|2
2|Shane|q33|3

3) Do inner join between three columns ID, Name , Counter

You should have your answer.

Re: split comma separated values into different rows

Anonymous — Tue, 08 Jan 2019 11:23:45 GMT

Hi ,

Thanks for the response @uganesh

In first Normalization , I did with "Product" , on 2nd one with "Qty" .

Then on each flow , I omitted Qty column( 1st flow) & Product column( 2nd flow) through tFilterColumn.

But , after that , in tJoin/tMap , I cannot map from 2 different filter Outputs , hence , cannot join 2 incoming flows.

Note : Solution without "Counter" column is desirable.

Re: split comma separated values into different rows

akumar2301 — Tue, 08 Jan 2019 11:34:41 GMT

You cannot do that please follow below link

https://help.talend.com/reader/~V1UYTyXpjBKjP5PCMy4EA/khJeOaSeNMnFt0o~DMx6qQ

tJoin/tMap might not work for your case without an a counter. as for same id and name , you will have more than one entry in table.

Re: split comma separated values into different rows

JRaut — Wed, 06 Jan 2021 11:21:43 GMT

That is possible using split( ) function in java

See the code below,

String array1[]=row1.CS_String .split(",");

for (int i=0 ; i<array1.length ; i++)

{

row2.Id = row1.Id;

row2.CS_String_Element= array1[i];

}

I tried with input:

col 1- Id : 1

col2- CS_String : "A,B,C,D,E,F,G"

Here is the output: