Split columns into multiple columns

[joe86]

Hi All,

I have a situation here. I have a data set like below:

This data set was obtained using

MsSQLInput-->tmap-->tSortRow-->tDeNormaliseSortedRows-->tMap-->tExcelOutput.

Now as per the new requirement, i have to split this "EmpID" & "EmpName" columns to multiple columns.

In this case I should have 3 EmpID columns and 3 EmpName columns. It basically depends on how many.

Can some one please suggest me to do this.

I tried doing with tNormalise, but it didnt work. Alos tried my luck with "tExtractDelimitedFiles" as below:

MsSQLInput-->tmap-->tSortRow-->tDeNormaliseSortedRows-->tMap-->tExtractDelimitedFiles-->tExcelOutput.

Here i was able to select only 1 column to split and this also didn't get me the exact answer.

My final output should be like below

[TRF]

tJavaRow + String.split() should be the way

[joe86]

can you please help me with the flow? I am new to Talend.

should tJavaRow + String.split()  be used in after tMap or where should be implemented??

[Anonymous]

I agree with @TRF

If you are looking for a sample scenario a slightly different version can be referred from below link.

https://community.talend.com/t5/Design-and-Development/2-parts-to-a-string-find-replace-issue/m-p/13...

Warm Regards,

Nikhil Thampi

[joe86]

is there a way to achieve this by avoiding tJavaRow?

I can change my existing flow as well if needed.??

Anyone please??

[Anonymous]

Hi,

    My recommendation will be to use tjavarow for your use case. Any specific reason why you are hesitant to use it?

     You can even try tmap for that matter.

Warm Regards,

Nikhil Thampi

[joe86]

I am not familiar with tJava is the main reason & i am new to talend.

Also i have 2 more columns like this that needs to split. So in total, 4 columns needs to be split into 16 columns.

Can you please help on this? any sample pls?

[Anonymous · Accepted solution]

Hi,

     You can also use tmap with below functions. I have divided the string to three parts as per the example string given in your query. If you need to break it into four, you can do that way also by following the same coding pattern with some minor changes.

Employee Id data split

row1.Emp_id.substring(0,row1.Emp_id.indexOf("|")) 
row1.Emp_id.substring(row1.Emp_id.indexOf("|")+1,row1.Emp_id.indexOf("|", row1.Emp_id.indexOf("|") + 1)) 
row1.Emp_id.substring(row1.Emp_id.indexOf("|", row1.Emp_id.indexOf("|") + 1)+1) 

Employee Name data split

row1.Emp_Name.substring(0,row1.Emp_Name.indexOf("|")) 
row1.Emp_Name.substring(row1.Emp_Name.indexOf("|")+1,row1.Emp_Name.indexOf("|", row1.Emp_Name.indexOf("|") + 1)) 
row1.Emp_Name.substring(row1.Emp_Name.indexOf("|", row1.Emp_Name.indexOf("|") + 1)+1) 

If my answer has helped to resolve the query, could you please mark the topic as answered? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[ankit7359]

Hi,

There is a component in talend by name tsplitrow.... you can try this out..

i shall share one of the scenario what i have done... as screenshots.

As per my scenario... i m generating employee name(first and last name) and ids. In tmap i m joinin the names via simple Java concatenation and then in tsplitrow i m spliting the fullname as first and last name as usual...

pls be advised of the schema settings of the tsplitrow and the properties of tsplitrow.

Job scenario..pls consider the active jobtrowgenerator settings Fullname">concatenatio/joining of names -> Fullnametsplitrow schema settings...tsplitrow component viewEnd-result..

I shall alsoo try your scenario and get back to you on this.. 

Thanks

[joe86]

@ankit7359

I tried using tSplitRow and that does not satisfy my need. That is used to split rows into rows.

I want to split one column into multiple columns. tExtracDelimited also does not work here since i can split only one column. I have 4 columns that needs to split into 16 columns (1:4). Since i am not familiar with Java, i did not prefer using tJavaRow. @nthampi solution was good for my need.

I would appreciate if you find something and post here which will help me and others (for secondary option).

Thanks again