Forum Discussion

andre_mangatal's avatar
andre_mangatal
New Contributor
7 years ago

HELP PLEASE - CSV Parser Advice Reading Delimiter In Data Fields

Good day SnapLogic Community

I would greatly appreciate your help with the following. We have a cloud pipeline reading data from a REST service and returns the data in a comma delimited string. It send the data via a pipeline execute with label “Store Transactions” and this child pipeline has a CSV Parser configured as below. A runtime error occurs because one field contains XML data as follows with also contains commas so an error is being thrown.

Is there any way that I can design the pipeline to deal with these extra commas before the line feed?

        <Policy>
            <PolicyId>5002919511</PolicyId>
            <PlanCode>LB85 1</PlanCode>
            <Status>UNDERWRITING INCOMPLETE, MISSING REQTS, ERROR ON POLICY</Status>
            <IssueDate>2019-03-15</IssueDate>
            <Mode>SEMI ANNUAL</Mode>
            <ModalPremium>6078.75</ModalPremium>
            <PremiumDueDate>1900-01-01</PremiumDueDate>
            <Quantity>1</Quantity>
        </Policy>

Thank you very much in advance!

Best regards

4 Replies

  • tstack's avatar
    tstack
    Former Employee
    7 years ago

    I would think this REST service should be doing some sort of quoting for the field with the XML in it so this wouldn’t be a problem. Is that not happening? Can you give an example of the full row of data?

    • andre_mangatal's avatar
      andre_mangatal
      New Contributor
      7 years ago

      Hi Tim

      Thanks for your reply. Attached is a row of data that is throwing the error. There is a custom XML field called “CustomXML”. I attached the file because it wasn’t displaying properly here.

      Sample_Data.txt (1.4 KB)

      • tstack's avatar
        tstack
        Former Employee
        7 years ago

        It looks like it’s not quoted, that’s too bad. If the <CustomXML> tag is pretty unique, you could try to add quoting by doing some string replacement using a mapper snap, like so:

        $content.replace(/(\n<CustomXML>)/g, '"$1').replace(/(<\/CustomXML>)/g, '$1"')

        This expression adds a double quote before the start tag and then another one after the end tag.

  • andre_mangatal's avatar
    andre_mangatal
    New Contributor
    7 years ago

    Hi Tim

    Thanks a lot man! This worked for me. I used a mapper before to remove any double quotes from the customXML and it work. It helped a lot,

    Regards