Split columns into multiple columns

Seven Stars

Split columns into multiple columns

Hi All,

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

Capture.JPG

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
Capture2.JPG


Accepted Solutions
Employee

Re: Split columns into multiple columns

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.

image.png

 

 

image.png

 

 

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


All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Split columns into multiple columns

tJavaRow + String.split() should be the way

TRF
Seven Stars

Re: Split columns into multiple columns

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

 

Employee

Re: Split columns into multiple columns

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

Seven Stars

Re: Split columns into multiple columns

is there a way to achieve this by avoiding tJavaRow?

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

Anyone please??

Employee

Re: Split columns into multiple columns

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

Seven Stars

Re: Split columns into multiple columns

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?

Employee

Re: Split columns into multiple columns

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.

image.png

 

 

image.png

 

 

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

Nine Stars

Re: Split columns into multiple columns

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.

SplitRow.PNGJob scenario..pls consider the active jobtrowgenrator.PNGtrowgenerator settingstmap.PNGconcatenatio/joining of names -> Fullnametsplitrow.PNGtsplitrow schema settings...tsplitrow1.PNGtsplitrow component viewend-result-logrow.PNGEnd-result..

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

Thanks

Seven Stars

Re: Split columns into multiple columns

@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. @nikhilthampi 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 Smiley Happy

Six Stars

Re: Split columns into multiple columns

hi nikhil could you explain this scenario please and what are the string handling functions you are using in this
Six Stars

Re: Split columns into multiple columns

what is the difference between string handling index and indexof
Six Stars

Re: Split columns into multiple columns

what is the meaning of substring
Nine Stars

Re: Split columns into multiple columns

Hi @ram2,

this scenario was about to split the delimited data in the Files into columns...

Now coming to the string handling functions and why are they used in this scenario.. is that in Talend we can implement any scenario/most of the scenarios in Tmap..as it has multiple uses...

also indexof gives the position of a certain character in the string...

and substring gives the necessary string as per the position..

lemme give an example....

example - > String - "Abcdef"

indexof('c') -> 3

substring("Abcdef",3) -> "def" -> the string is enclosed in double quotes while the position of a character is delimited by a comma.... where 3 gives the position of c..in the string.

substring("Abcdef",2,2) -> "bc" -> the string is enclosed in double quotes while the position of a character is given and it's length is also defined... so the index starts at b and moves forward with a length of 2 charcters inclusive of b...

Pls reach out to the Community in case you need anything...

 

Six Stars

Re: Split columns into multiple columns

super could please elaborate your explanation like with any another example
Nine Stars

Re: Split columns into multiple columns

hi @ram2,

thank you for comments...

pls if you dont mind ....As this topic is already closed can you pls create new topic with your necessary requirements... i shall explain accordingly to the requirements.... 

Will be waiting for a new topic to be created... 

Pls reach out to the community,if in case you need help....

thanks 

ankit

Five Stars

Re: Split columns into multiple columns

Hi,

 

While applying your logic to extract the second data, i am getting below error:

 

Exception in component tMap_1 (Sample_Jobs_SF)
java.lang.StringIndexOutOfBoundsException: String index out of range: -13
at java.lang.String.substring(String.java:1967)
at local_project.sample_jobs_sf_0_2.Sample_Jobs_SF.tFileInputExcel_1Process(Sample_Jobs_SF.java:2751)
at local_project.sample_jobs_sf_0_2.Sample_Jobs_SF.runJobInTOS(Sample_Jobs_SF.java:3333)
at local_project.sample_jobs_sf_0_2.Sample_Jobs_SF.main(Sample_Jobs_SF.java:3182)

 

PFA Screenshots of my tmap.

 

Please suggest what needs to be done to resolve this error.

 

 

Thanks,

AnuragScreenshot from 2018-12-19 12-32-43.png

 

Screenshot from 2018-12-19 12-36-10.png

 

Highlighted
Employee

Re: Split columns into multiple columns

@anurag13_

 

Could you please create a new topic for your query with sample file, your job flow screenshots, use case etc. so that community members can look into the issue?

 

You are trying to read a string from a position which is not available (may be due to length of string is less or the value you are giving is wrong).

 

This hint could identify the issue but if you need more details, my recommendation will be to start a new topic for your use case.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Nine Stars

Re: Split columns into multiple columns

hi @anurag13_,

Greetings of the day,

Glad to know that you are trying out this scenario,but I would like to request you to Create a new topic and then post your question. Also can you pls attach a sample input file, If not for me the Community would like see how your input is and how to solve it.

Pls reach out to the Community,if necessary.

Thanks,

Ankit

Five Stars

Re: Split columns into multiple columns

Hi,

 

I want split the string into two halves and then interchange it.

For ex: "ABCDEFGH" is my string.

I want to make it EFGHABCD.

 

How can i do that in tMap/expression builder.

Employee

Re: Split columns into multiple columns

@MNK_2019 

 

I have answered your query in the below post. Request you to lease keep your query to single post :-) 

 

https://community.talend.com/t5/Design-and-Development/Split-and-Interchange-string-text/m-p/164212#...

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog