Data spiliting due to Pipe Delimiter in between Data

[abhi90]

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

[TRF]

Text Enclosure should be "\"", no?

[abhi90]

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.

[abhi90]

Can anybody help in this

[TRF]

Be patient...

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

[abhi90]

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.

[manodwhb · Accepted solution]

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

[abhi90]

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

[abhi90]

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

[manodwhb]

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