topic Re: JSON Parser output to CSV file in Workato Pros Discussion Board

JSON Parser output to CSV file

victor — Tue, 06 Feb 2024 17:02:51 GMT

Hello there,

I am getting a response from an API then I passed it through the JSON Parser and it got something as below. You will see that I have items/product that I would like to have in separated rows in a CSV file. I tried with a FOR EACH loop using products array, but it does not iterate, the input is empty, but I am taking the datapill from the JSON parser, what am I missing?

Thanks

{
"document": {
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "Product Claim Details",
"type": "object",
"properties": {
"products": {
"type": "array",
"items": [
{
"Vendor": "CANADA",
"BillingDate": "2022/09/02",
"InvoiceNumber": "SL1",
"productNumber": "14-1",
"description": "MUG SET",
"status": "FD",
"vendorPartNumber": "MUGCAN",
"units": 0,
"claimAmounts": 0,
"initialBillingAvailable": 0.14,
"shortfallSurplus": -0.14
},
{
"Vendor": "CANADA",
"BillingDate": "2022/09/02",
"InvoiceNumber": "SL2",
"productNumber": "14-2",
"description": "BOWL",
"status": "FD",
"vendorPartNumber": "29CN",
"units": 0,
"claimAmounts": 0,
"initialBillingAvailable": 2.25,
"shortfallSurplus": -2.25
}
]
}
},
"required": [
"products"
]
}
}

Re: JSON Parser output to CSV file

gary1 — Tue, 06 Feb 2024 17:06:21 GMT

Based on the sample, product is an object and items is an array. You need to iterate on items.

Re: JSON Parser output to CSV file

victor — Tue, 06 Feb 2024 17:33:09 GMT

Hello Gary,

thanks for your reply. I agree but it doesnt show me item as datapill, only products.

Re: JSON Parser output to CSV file

gary1 — Tue, 06 Feb 2024 17:43:15 GMT

I took another look and this is very interesting! I can't get the example you provided to work in the JSON parser as is.

I have to remove this line:

"required": ["products"]

I guess the one-dimensional array is throwing off the parser.

Once this is removed, I can access the items data pill without a problem.

Give this a shot, hopefully it will work.

Re: JSON Parser output to CSV file

victor — Tue, 06 Feb 2024 20:45:27 GMT

Hello Gary,

I played with the JSON schema and finally the datapill showed for items, from there, the loop was going throughout all items and then I append the record to the CSV file inside the loop, I tried to accumulate to a list inside the loop and outside the loop to append the list to the CSV, but it didn't work.

Thanks for your help

"items": {
"type": "array",
"properties": {
"Vendor": {
"type": "string",
"description": "Vendor"
},
"BillingDate": {
"type": "string",
"description": "Billing Date"
},
"InvoiceNumber": {
"type": "string",
"description": "The unique identifier for the product."
},
"productNumber": {
"type": "string",
"description": "The unique identifier for the product."
},
"description": {
"type": "string",
"description": "The description of the product."
},
"status": {
"type": "string",
"description": "The status of the product."
},
"vendorPartNumber": {
"type": "string",
"description": "The vendor's part number for the product."
},
"units": {
"type": "integer",
"description": "The number of units claimed."
},
"claimAmounts": {
"type": "number",
"description": "The total amount claimed for the product."
},
"initialBillingAvailable": {
"type": "number",
"description": "The initial billing amount available for the product."
},
"shortfallSurplus": {
"type": "number",
"description": "The shortfall or surplus resulting from the claim."
}
},
"required": ["Vendor", "BillingDate", "InvoiceNumber", "productNumber", "description", "status", "vendorPartNumber", "units", "claimAmounts", "initialBillingAvailable", "shortfallSurplus"]
}

Re: JSON Parser output to CSV file

gary1 — Tue, 06 Feb 2024 20:47:43 GMT

Can you send some screenshots of your recipe? Something must be misconfigured. Let's figure it out.