topic Re: Data spiliting due to Pipe Delimiter in between Data in Talend Studio

Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 11:09:24 GMT

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, @vboppudi, @TRF, @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

Re: Data spiliting due to Pipe Delimiter in between Data

TRF — Thu, 26 Apr 2018 11:14:23 GMT

Text Enclosure should be "\"", no?

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 11:20:53 GMT

Hi @TRF,

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

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 11:27:13 GMT

Can anybody help in this

Re: Data spiliting due to Pipe Delimiter in between Data

TRF — Thu, 26 Apr 2018 11:29:02 GMT

Be patient...

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

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 11:32:14 GMT

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.

Re: Data spiliting due to Pipe Delimiter in between Data

manodwhb — Thu, 26 Apr 2018 13:08:42 GMT

@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"

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 13:34:30 GMT

Hi @manodwhb,

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

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

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 13:38:22 GMT

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

Re: Data spiliting due to Pipe Delimiter in between Data

manodwhb — Thu, 26 Apr 2018 13:40:54 GMT

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

Re: Data spiliting due to Pipe Delimiter in between Data

abhi90 — Thu, 26 Apr 2018 18:00:12 GMT

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. . Marking your solution as accepted. Hope to get this kind of help in future also