Extracting Column data and splitting into multiple columns

Highlighted
Five Stars

Extracting Column data and splitting into multiple columns

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:

 

Input_File.png

 

Desire Output:

 

Desire_Output.png

 

Waiting for the reply.

 

Thanks,

Anurag Kumar

 

 


Accepted Solutions
Nine Stars

Re: Extracting Column data and splitting into multiple columns

Hello Ankit ,

 

I am asking it use in tExtractDelimiterFields.

 

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

 

Hope this will help.

Regards
Abhishek KUMAR

All Replies
Eight Stars

Re: Extracting Column data and splitting into multiple columns

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

Five Stars

Re: Extracting Column data and splitting into multiple columns

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.

 

Screenshot from 2018-12-20 10-23-19.png

 

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

 

Thanks,

Anurag Kumar

 

 

Employee

Re: Extracting Column data and splitting into multiple columns

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: Extracting Column data and splitting into multiple columns

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

Employee

Re: Extracting Column data and splitting into multiple columns

@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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Eight Stars

Re: Extracting Column data and splitting into multiple columns

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.

Eight Stars

Re: Extracting Column data and splitting into multiple columns

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...Output_Sample_Input.PNGOUTPUTJob_design_Sample_input.PNGJOB_DESIGNSource_Sample_Input.PNGINPUT

Pls reach out to the Community,if necessary.

Thanks,

Ankit.

Five Stars

Re: Extracting Column data and splitting into multiple columns

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

Five Stars

Re: Extracting Column data and splitting into multiple columns

Hi Nikhil,

 

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

 

Thanks,

Anurag Kumar

Eight Stars

Re: Extracting Column data and splitting into multiple columns

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

Five Stars

Re: Extracting Column data and splitting into multiple columns

Hi Ankit,

 

Do u get any new solution for the same?

 

Thanks,

Anurag Kumar

Eight Stars

Re: Extracting Column data and splitting into multiple columns

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

Six Stars

Re: Extracting Column data and splitting into multiple columns

Can you provide metadata for this components.

 

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

 

Regards,

Avinash

Nine Stars

Re: Extracting Column data and splitting into multiple columns

Try to use delimiter as "\\|". Will work as charm. Pipe is special char for Java
Regards
Abhishek KUMAR
Nine Stars

Re: Extracting Column data and splitting into multiple columns

Use "\\|"

Regards
Abhishek KUMAR
Eight Stars

Re: Extracting Column data and splitting into multiple columns

Hi @akumar2301,

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

Nine Stars

Re: Extracting Column data and splitting into multiple columns

Hello Ankit ,

 

I am asking it use in tExtractDelimiterFields.

 

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

 

Hope this will help.

Regards
Abhishek KUMAR
Eight Stars

Re: Extracting Column data and splitting into multiple columns

Hi @akumar2301,

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.

Five Stars

Re: Extracting Column data and splitting into multiple columns

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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download