topic Re: Split columns into multiple columns in Talend Studio

Split columns into multiple columns

joe86 — Thu, 08 Nov 2018 05:21:01 GMT

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

Re: Split columns into multiple columns

TRF — Thu, 08 Nov 2018 06:52:43 GMT

tJavaRow + String.split() should be the way

Re: Split columns into multiple columns

joe86 — Thu, 08 Nov 2018 06:59:28 GMT

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

Re: Split columns into multiple columns

Anonymous — Thu, 08 Nov 2018 10:21:20 GMT

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/139947#M86525

Warm Regards,

Nikhil Thampi

Re: Split columns into multiple columns

joe86 — Fri, 09 Nov 2018 04:57:31 GMT

is there a way to achieve this by avoiding tJavaRow?

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

Anyone please??

Re: Split columns into multiple columns

Anonymous — Fri, 09 Nov 2018 05:11:29 GMT

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

Re: Split columns into multiple columns

joe86 — Fri, 09 Nov 2018 05:29:20 GMT

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?

Re: Split columns into multiple columns

Anonymous — Fri, 09 Nov 2018 06:29:07 GMT

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

Re: Split columns into multiple columns

ankit7359 — Fri, 09 Nov 2018 06:42:32 GMT

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

Re: Split columns into multiple columns

joe86 — Fri, 09 Nov 2018 09:39:45 GMT

@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

Re: Split columns into multiple columns

Anonymous — Fri, 09 Nov 2018 13:21:44 GMT

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

Re: Split columns into multiple columns

Anonymous — Sun, 18 Nov 2018 13:30:11 GMT

what is the difference between string handling index and indexof

Re: Split columns into multiple columns

Anonymous — Sun, 18 Nov 2018 13:30:38 GMT

what is the meaning of substring

Re: Split columns into multiple columns

ankit7359 — Sun, 18 Nov 2018 15:25:43 GMT

Hi @rchinta,

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

Re: Split columns into multiple columns

Anonymous — Tue, 20 Nov 2018 05:34:36 GMT

super could please elaborate your explanation like with any another example

Re: Split columns into multiple columns

ankit7359 — Tue, 20 Nov 2018 06:20:42 GMT

hi @rchinta,

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

Re: Split columns into multiple columns

Anonymous — Wed, 19 Dec 2018 07:06:53 GMT

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,

Anurag

Re: Split columns into multiple columns

Anonymous — Wed, 19 Dec 2018 11:18:00 GMT

@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 🙂

Re: Split columns into multiple columns

ankit7359 — Wed, 19 Dec 2018 11:28:33 GMT

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

Re: Split columns into multiple columns

Anonymous — Wed, 12 Jun 2019 17:30:13 GMT

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.