Three Stars

Splitting Comma delimited Column in multiple columns. The index of the element in the string has to be the column number.

Hello Everyone,

 

I have started to work with Talend around two-three weeks ago and I have encountered a small issue that I can't really overcome. I have a column in an Excel file which looks like: "Word1, Word2, Word3" which I want to split up in Talend in three column. All nice and good here, very easy. But sometimes the column data could look like this: "Word1, Word3" or "Word2, Word3" and I have not found a way to put those values in the right column.

 

Input:

|Word_Input                 |

|Word1, Word2, Word3|

|Word2                         |

|Word2, Word3             |

|Word1, Word3             |

|Word3                          |

 

Desired Output:

Column1|Column2|Column3

Word1    |Word2    |Word3

"blank"    |Word2    |"blank"

"blank"    |Word2    |Word3

Word1     |"blank"   |Word3

"blank"     |"blank"   |Word3

 

Actual Output:

Column1|Column2|Column3

Word1    |Word2    |Word3

Word2    |"blank"    |"blank"

Word2    |Word3     |"blank"

Word1    |Word3     |"blank"   

Word3    |"blank"     |"blank"

 

And so, my question is that if this can be achieved with Talend functions( as I definitely could not achieve what I wanted by myself)  or should I go all Raw Java coding into this ?

 

Thank you for your time.

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

Re: Splitting Comma delimited Column in multiple columns. The index of the element in the string has to be the column number.

@LoA02,are you looking for the below output?Untitled.pngUntitled.png

Manohar B
2 REPLIES
Twelve Stars

Re: Splitting Comma delimited Column in multiple columns. The index of the element in the string has to be the column number.

@LoA02,are you looking for the below output?Untitled.pngUntitled.png

Manohar B
Three Stars

Re: Splitting Comma delimited Column in multiple columns. The index of the element in the string has to be the column number.

Hello and thank you for the solution. It works perfect with the "tFixedFlowInput" but when I am trying to use it with the "tInput_File_InputExcel" it gives me a strange error which I could not debug still. I am working into that now. Thank you again for the solution, really works like a charm.

 

EDIT: I have solved the error. Everything works like a charm. Thanks a lot for the solution, never knew that there is a "contains" around there. 

 

Best regards, Alex.