Data spiliting due to Pipe Delimiter in between Data

Eight Stars

Data spiliting due to Pipe Delimiter in between Data

Hi All,

 

I have File where a Row is present which is having Pipe delimiter inside Data. Now the File is itself Pipe delimited. and The Data is enclosed by Double Quotes. Now I am loading the file Data using tfileinputdelimited. where Rowseparator is "\n" and Field Separator is "|". If I am checking CSV Options and giving Escape Character as """ and Text Enclosure as "/"" Then 7 Rows is spliting badly. Can anybody please help?

@TRF@rhall_2_0@vboppudi@TRF1@manodwhb. Data is like "Abhijit is walking|9P.M". Database Column is String.So  Abhijit is walking is going to intended Column and 9P.M is going to Next Column


Accepted Solutions
Thirteen Stars

Re: Data spiliting due to Pipe Delimiter in between Data

@abhi90, you should get data for each and every coulmn in " " then you will not face any issue.if you have same delimiter as part of column value,you will not extract as per your requirement.

i am just showing,this will be help to you to understand.

 

Input data:-

col1,col2
"Hello"|"ABC"
"aass"|"CDF|AC"

 

Untitled.pngUntitled.png

Manohar B

All Replies
Forteen Stars TRF
Forteen Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Text Enclosure should be "\"", no?

TRF
Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Hi @TRF,

If I am using that Then Other Rows are hampering. And messing up the Data. Smiley Sad I can't discard that | in between Data as Other ETL of Our Project is loading that Without Any Issue.

Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Can anybody help in this

Forteen Stars TRF
Forteen Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Be patient...

Also, give a more complete sample for your input and expected output.


TRF
Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Hi @TRF,

Below is my Input

"RT @GreysABC: From the minds behind #GreysAnatomy and the rest of #TGIT comes @ABC_TheCatch, premiering tonight at 10|9c on ABC!"|7.13084E+17|VIDEO_VIEWS

Now RT @GreysABC: From the minds behind #GreysAnatomy and the rest of #TGIT comes @ABC_TheCatch, premiering tonight at 10|9c on ABC! will go to One Column.7.13084E+17 will go to next and VIDEO_VIEWS will go to Next. Now problem is coming is in the column 1 RT @GreysABC: From the minds behind #GreysAnatomy and the rest of #TGIT comes @ABC_TheCatch, premiering tonight at 10 is loading and in the column where 7.13084E+17 supposed to load 9c on ABC! is been loading.

Thirteen Stars

Re: Data spiliting due to Pipe Delimiter in between Data

@abhi90, you should get data for each and every coulmn in " " then you will not face any issue.if you have same delimiter as part of column value,you will not extract as per your requirement.

i am just showing,this will be help to you to understand.

 

Input data:-

col1,col2
"Hello"|"ABC"
"aass"|"CDF|AC"

 

Untitled.pngUntitled.png

Manohar B
Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Hi @manodwhb,

Thanks for Your reply. I tried with your solution exactly as per your snapshots. But 7 Rows got rejected. Smiley Sad 

Giving Escape Character as "\"" and Text Enclousure as "\"".Field Separator as "|". Still No Luck

Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

That Row is loading perfectly. But 7 rows of another File is getting rejected @manodwhb

Thirteen Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Hi @abhi90,might be the other file format having issue?can you check?

Manohar B
Eight Stars

Re: Data spiliting due to Pipe Delimiter in between Data

Hi @manodwhb,

Thanks for your awesome help.Yess. With your solution I handled | in between Data. And Other ETL of our project was loading data incorrectly. After I corrected that Data is accurate between Talend and Informatica. Smiley Happy. Marking your solution as accepted. Smiley Happy Hope to get this kind of help in future also Smiley Happy

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.