topic Re: Split Column Into Multiple Rows in Talend Studio

Split Column Into Multiple Rows

Anonymous — Thu, 14 May 2015 20:28:03 GMT

Hello,
Here's the scenario. I have a string column which I need to parse(split) into potentially 0 -n parts and make each part a separate output row. So, for one input row, I may split my string column into 3 peices. The next row might not have what I'm looking in the string column, thus no output row. The parsing/splitting is not the problem I'm having trouble with. I can use the RegEx components or use Java string methods. It's the dynamic number of parts and forcing a separate output row for each.
Thanks for any help

Re: Split Column Into Multiple Rows

Anonymous — Thu, 14 May 2015 22:47:32 GMT

It sounds like you need to use the tNormalize component. If you place a regular separator in your string (for example a pipe "|" or semicolon ";") then you can use the tNormalize to create a new row for every value separated by that separator.
So......
"1,2,3,4,56,7,8,9" with a separator of "," becomes....
1
2
3
4
56
7
8
9
Is that what you are looking for?

Re: Split Column Into Multiple Rows

Anonymous — Mon, 18 May 2015 16:30:21 GMT

My strings to normalize aren't delimited/formatted as nicely. In the examples, below, I would want just the DataIDs returned.
]
should produce
1356381
1356382
1356383
or I could get

(null list)
but with some extra processing and experimentation, I might be able to make tNormalize work.
Thanks for the suggestion!

Re: Split Column Into Multiple Rows

Anonymous — Mon, 18 May 2015 17:06:08 GMT

If the String is in the format you have given below and it is consistent, you can use some code like below to put just the IDs into a String with a useful separator.
I am assuming the following is the format....
]
Put the following code into a tJavaRow and it will output a String like below....
1356381,1356382,1356383,

String value = input_row.data;
String[] values = value.split("\\[");
String returnVal = "";
for(int i = 0; i<values.length;i++){
   if(values.trim().length()>7){
   values = values.substring(7, values.indexOf(" "));
   returnVal = returnVal + values + ",";
   }
}
output_row.data = returnVal;

Re: Split Column Into Multiple Rows

Anonymous — Mon, 18 May 2015 21:06:09 GMT

Hi,
The component you're looking for to perform the Split from Column into Multiple rows is named : tNormalize.
Please see an example into the attached screenshot.

Best Regards

Re: Split Column Into Multiple Rows

Anonymous — Mon, 18 May 2015 21:57:14 GMT

Thanks rhall ! That's exactly the type of extra processing I was envisioning.

Re: Split Column Into Multiple Rows

KarthikGs — Tue, 11 Sep 2018 05:35:35 GMT

I want to store the output of tnormalise component. So, rather than logging it can i store it as a context variable?

Re: Split Column Into Multiple Rows

manodwhb — Tue, 11 Sep 2018 08:52:17 GMT

@KarthikGs,you can store it variables using tSetvariables, or you can load it to table also.

Re: Split Column Into Multiple Rows

Anonymous — Mon, 07 Jan 2019 12:25:42 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	John	p1,p2,p3	q1,q2,q3
2	Shane	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