🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

Monarch v15.3 - Pipe Delimited File

User: "Todd Klemp"
Altair Community Member
Updated by Todd Klemp

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!

Find more posts tagged with

Sort by:
1 - 1 of 11
    User: "CPorthouse"
    Altair Employee
    Updated by CPorthouse

    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:

    1. Ask the file provider to surround the field(s) with a text qualifier
    • 1234567890|General|"This is an example|1456"|Address Line 1|06/07/2021|0
    • "1234567890"|"General"|"This is an example|1456"|"Address Line 1"|"06/07/2021|0"
  • If #1 is not possible, then import the file as a text file instead of a database and then use Monarch (or Data Prep) to extract the various fields using lsplit and rsplit functions.
    • rsplit(lsplit([RAWLINEDATA],3,"|",3),4,"|",4)
    • If I did the formula above correctly it will split the RAWLINEDATA field into 3 parts based on the pipe delimiter and only return the 3rd part: This is an example|1456|Address Line 1|06/07/2021|0
    • and then get passed to the rsplit function to split the rest into 4 parts and return the 4th part: This is an example|1456