How to ignore , while reading pipe delimited dump csv file

How to ignore comma(,) while reading pipe delimited csv dump file where data is not coming columnwise. Which we also need to delimite using pipe. But while using Read Csv as pipe delimited throwing error that line 3 contain more data( format error).
This is due to while opening file by default excel application consider , as seperator and add next data after , to next cell.

For e.g.
Data is like without delimited with pipe

ID|Name|Address|DOB|Status
21|Abc|Nerul|10 oct 92|pass
22|Bbc|Mumbai, MH|2 jan 93| Fail
33|xyc|Pune|9 june 94|pass

But while opening file it showing like
Column A. Column B
1 ID|Name|Address|DOB|Status
2 21|Abc|Nerul|10 oct 92|pass
3 22|Bbc|Mumbai. MH|2 jan 93| Fail
4 33|xyc|Pune|9 june 94|pass

So while using Read CSV activity with pipe delimited getting error as Line 3 contains more data. Format error.

Any idea how to ignore , while opening or before opening file can we do something.

please share with us

  • a screenshot of the text file of this data and also
  • the read csv activity properties used for this data

Thanks

Based on your dataset, you will probably want to Read the full CSV, then Join the additional columns by a Comma.
Then you can start your split based on the | Pipe Delimeter.

It might sound a bit back to front, but it’s just due to your datasouce - as it is a CSV (Comma Seperated Value document)

But how can i read this file without getting error. As it is showing same error for comma delimited.

If is there any that can read csv file directly then we can do some manipulation on it.

So how can we read csv only without any error due to delimiter.

Remove your Delimeter in the Options section (put back to Comma). It will allow you to read the Data then you can go and manipulate the Data table after being read.

No. It is giving same error with Comma separated and also with tab seperated.

Line 2 contains more values than the header line.

Very strange! Could you upload the source file?
Obviously, remove or anonymize any Personal Identifiable Data etc.

No actually I also don’t have access to send file to my pc. But you can check above sample screenshot it is getting like that.

Issues are-

  1. For pipe header row last column doesn’t end with pipe while other transaction data end with pipe delimiter

  2. If comma is occured in file then it is splitting that cell to different cells.

Header row part is fixed for all files but issue occurred due to main data which ends with pipe and sometimes comma is present.