HELP PLEASE - CSV Parser Advice Reading Delimiter In Data Fields

[andre_mangatal]

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

[tstack]

andre.mangatal:

We have a cloud pipeline reading data from a REST service and returns the data in a comma delimited string

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]

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]

andre.mangatal:

Attached is a row of data that is throwing the error. There is a custom XML field called “CustomXML”.

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]

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