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

Twelve 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

Twelve 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