topic Re: Extracting Column data and splitting into multiple columns in Talend Studio

Extracting Column data and splitting into multiple columns

Anonymous — Wed, 19 Dec 2018 11:59:31 GMT

Hi All,

I am facing a problem in splitting and extracting a column data (Special Value) separated by "|" into multiple columns. Below are the screenshots of my input file and the desire output.

Request you to suggest me the solution for the same :

Input File:

Desire Output:

Waiting for the reply.

Thanks,

Anurag Kumar

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Wed, 19 Dec 2018 13:12:39 GMT

hi @anurag13_,

Greetings of the Day,

As per your scenario, your JOB Design flow must be like tfileinputexcel --->> transformation logic -->>tfileoutputexcel... welll considering the input and output as excel.

your scenario can be achieved by tmap(substring method) or textractdelimitedfields -> specify the field(Special values) and specify the field seperator.

Pls reach out to the community,if necessary.

Thanks,

Ankit

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 04:54:03 GMT

Hi,

I tried with "textractdelimitedfields" but didn't get the desire output in excel. Also, i can not use substring method in tmap as the column data separated by ' | ' is not fixed per row. I want some logic which works automatically at run time and get me desire output.

It would be great , if you could reply with the demo screenshot for better understanding.

Thanks,

Anurag Kumar

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 05:38:26 GMT

Hi Anurag,

If you can share the sample input file, we can show how it can be achieved using tExtractDelimitedFields component.

Warm Regards,

Nikhil Thampi

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 05:57:58 GMT

Hi Nikhil,

As requested by you, i am attaching the sample input file for your reference.

Please let me know in case of any further information.

Thanks,

Anurag Kumar

Sample_Input.xlsx

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 07:19:06 GMT

@anurag13_

When I tried to parse the below record with |, it is picking one character at a time instead of entire string.

"group_id=13|Nomor HP=08111909058|Kode Referal= |Nama Cabang= |Cabang Bank= |Referer Number= |Penghasilan Per Hari= |Akumulasi penghasilan perbulan= |Agunan yang digunakan= |Plafond yang dibutuhkan= |Autosweep=false|Rekening Autosweep= |Batas Autosweep= |Partner Id= |CIFCOBA="

But if I am using ";" or any other separator, the textractdelimitorfields component is able to give the data in right manner.

Hi @xdshi,

Could you please advise on this scenario?

Warm Regards,

Nikhil Thampi

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Thu, 20 Dec 2018 07:22:53 GMT

hi @anurag13_,

Jus as Nikhil pointed it out even i m able to see single character of the first delimited field.

I have tried with textractdelimitedfields but no use.

Pls do have patience as Community experts(excludes me) are looking and trying on your scenario.

Thanks,

Ankit.

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Thu, 20 Dec 2018 08:14:23 GMT

hi @anurag13_,

There is one method you can try..but this is a bit longer workaround and may not be suggested.. i have tried it and i got the output..

Well the job design would be like -

INPUT(EXCEL)---->>>OUTPUT(DELIMITED_FILE)------>>>OnSubJOBOK(Trigger)---->>>INPUT(DELIMITED_FILE)------->>>OUTPUT(EXCEL).

Have a look at the attached screenshots,maybe you will be able to get an idea on this..

this doesnt have to be such big workaround, as you know we can use textractdelimitedfields component but there seems to be some issue with this.. and we have to exclude using Tmap as we cant use substring method or array(String Array object)method(Complex Expression Logic -> performance will be hit).

Pls do give it a try...OUTPUTJOB_DESIGNINPUT

Pls reach out to the Community,if necessary.

Thanks,

Ankit.

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 10:25:42 GMT

Hi Ankit,

Thanks for the reply.

But, i would like to some easy method for resolution of this problem.

Please let me know if you get any other solution for the same

Thanks,

Anurag

Re: Extracting Column data and splitting into multiple columns

Anonymous — Thu, 20 Dec 2018 10:26:47 GMT

Hi Nikhil,

Please let me know once you get any best solution for the same.

Thanks,

Anurag Kumar

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Thu, 20 Dec 2018 10:40:58 GMT

hi @anurag13_,

the easy solution is to use that extractdelimitedfields component...

I dont know what is the issue with the source file you are handling...???

i m however trying to solve the scenario.

Thanks,

Ankit

Re: Extracting Column data and splitting into multiple columns

Anonymous — Wed, 26 Dec 2018 05:59:59 GMT

Hi Ankit,

Do u get any new solution for the same?

Thanks,

Anurag Kumar

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Wed, 26 Dec 2018 07:33:22 GMT

Hi @anurag13_,

Greetings of the Day,

Wish u a merry Christmas,

Well i m sorry to say this but,I couldnt find any other solution for your scenario and when i tried with the usual ones(textractdelimitedfields or substring) -> it did not help as it was retrieving single character...

Well lets not lose hope, give it a try and i m sure we will get a solution.

Thanks,

Ankit

Re: Extracting Column data and splitting into multiple columns

Anonymous — Wed, 26 Dec 2018 11:03:18 GMT

Can you provide metadata for this components.

NPUT(EXCEL)---->>>OUTPUT(DELIMITED_FILE)------>>>OnSubJOBOK(Trigger)---->>>INPUT(DELIMITED_FILE)------->>>OUTPUT(EXCEL).

Regards,

Avinash

Re: Extracting Column data and splitting into multiple columns

akumar2301 — Wed, 26 Dec 2018 12:03:17 GMT

Try to use delimiter as "\\|". Will work as charm. Pipe is special char for Java

Re: Extracting Column data and splitting into multiple columns

akumar2301 — Wed, 26 Dec 2018 12:04:09 GMT

Use "\\|"

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Wed, 26 Dec 2018 12:47:31 GMT

Hi @uganesh,

Greetings of the Day,

The input file is in Excel format where - in we cant use Extended CSV options like escape characters and text enclosing characters.That being the reason i had tested out a workaround process of dumping the excel content to CSV and then extracting the content to various fields.

Well i dont see any other workaround process, but if anyone has any ideas pls do share such that, we all might come to know where we might have missed and what we might missed.

Thanks,

Ankit

Re: Extracting Column data and splitting into multiple columns

akumar2301 — Wed, 26 Dec 2018 13:10:11 GMT

Hello Ankit ,

I am asking it use in tExtractDelimiterFields.

Field Separator as "\\|" instead of "|".

Hope this will help.

Re: Extracting Column data and splitting into multiple columns

ankit7359 — Wed, 26 Dec 2018 13:28:42 GMT

Hi @uganesh,

Superb!!!!...It works... Kudos to you man..!!!

I was completely ignoring that in textractdelimitedfields we can give escape character.!!!.. that was my mistake..

Maybe you can give a reply and ask @anurag13_ to try this out as well...

Thanks,

Ankit.

Re: Extracting Column data and splitting into multiple columns

Anonymous — Mon, 31 Dec 2018 08:10:37 GMT

Hi,

I have tried with your provided solution but the didnt get the desire output.
It would be great if you could share the screenshots of your solution for better understanding.

Thanks,
Anurag Kumar