Extracting Column data and splitting into multiple columns

[Anonymous]

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

[ankit7359]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

@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

[ankit7359]

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.

[ankit7359]

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.

[Anonymous]

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

[Anonymous]

Hi Nikhil,

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

Thanks,

Anurag Kumar