Monarch v15.3 - Pipe Delimited File
I have a file that is pipe delimited with a header row. The header row indicates that there should be 5 fields of data. However, there are instances where the data in the 3rd field contains a pipe. Therefore, data that should stay in the 3rd field is pushed to fields 4 and 5. Meanwhile the data that is meant for fields 4 and 5 are truncated as there is not an additional field for the data to be pushed to. Here is an example:
Header Row:
Account_number|Comment_code|Description|Post_date|Amount
Sample Data:
1234567890|General|This is an example|1456|Address Line 1|06/07/2021|0
AAAAAA|Insurance|Contact Us|FAQs|Logout My Home Eligibility Claims Resources Switch Provider...|01/08/2021|0
What I would like to do is basically use the pipe delimiter identify each field to determine how many fields need to be created. I haven't been able to figure out how to do that just yet and not sure if this can be done. Any feedback is appreciated. Thanks!
Normally in situations like this, the field in question should be surrounded by a text qualifier (double or single quotes, for example) so any delimited file parser would understand that the delimiter is part of the field and should not be considered a delimiter.
A couple of possibilities to consider: