Five Stars

Split Column Into Multiple Rows

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 
5 REPLIES
Employee

Re: Split Column Into Multiple Rows

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?
Five Stars

Re: Split Column Into Multiple Rows

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!
Employee

Re: Split Column Into Multiple Rows

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;
Employee

Re: Split Column Into Multiple Rows

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
Five Stars

Re: Split Column Into Multiple Rows

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