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

[Anonymous]

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

[Anonymous]

what is the difference between string handling index and indexof

[Anonymous]

what is the meaning of substring

[ankit7359]

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

[Anonymous]

super could please elaborate your explanation like with any another example

[ankit7359]

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

[Anonymous]

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

[Anonymous]

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

[ankit7359]

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

[Anonymous]

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.