How to extract and clean JSON payload inside CSV field from multipart data in MuleSoft DataWeave 2.0

Question

I am working in Mule 4 using DataWeave 2.0 and trying to transform a multipart/form-data payload that contains a CSV file.

Here is a simplified example of my input payload (multipart):

----------------------------974030822407525684705359
Content-Disposition: form-data; name="alert_name"
 
kle-process-api-netiq-hr
----------------------------974030822407525684705359
Content-Disposition: form-data; name="incident_description"
 
Please find attached the errors generated during the data sync from Workday to NetIQ \n\nCorrelation ID - 3243444-0e5b-4fbd-817a-d28365a3414a\nTransaction ID - dsdsdsddd-d09c-4be3-a676-e45c487ca106\nSystem Name - pro-netiq-hr\nTotal number of error messages - 2
----------------------------974030822407525684705359
Content-Disposition: form-data; name="file"; filename="error.csv"
Content-Type: text/csv
 
correlationId,applicationName,errorProviderName,messageObjectName,messageObjectKey,enumber,vnumber,createdDateTime,errorResponseStatusCode,errorResponsePayload
67ghg34f-7584-4439-8e62-d10d22070186,kle-process-api-netiq-hr,NetIQ API,worker-object,987654,E54321,V097654,2025-08-13T12:05:23,500,"{\""id\"": \""ID-test-90bd8d7c-fb64-4b5c-9585-fa8e94b19dc4\"",\""result\"": \""ERROR\"",\""code\"": \""500\"",\""application\"": \""kle-system-api-netiq-hr\"",\""provider\"": \""NetIQ API\"",\""payload\"": \""Internal Server Error : An internal error occurred. Please try again. : <html>\\r\\n<head><title>502 Bad Gateway</title></head>\\r\\n<body>\\r\\n<center><h1>502 Bad Gateway</h1></center>\\r\\n</body>\\r\\n</html>\\r\\n\""}"
 
----------------------------974030822407525684705359--

Expected output:

----------------------------974030822407525684705359
Content-Disposition: form-data; name="alert_name"
 
kle-process-api-netiq-hr
----------------------------974030822407525684705359
Content-Disposition: form-data; name="incident_description"
 
Please find attached the errors generated during the data sync from Workday to NetIQ \n\nCorrelation ID - 3243444-0e5b-4fbd-817a-d28365a3414a\nTransaction ID - dsdsdsddd-d09c-4be3-a676-e45c487ca106\nSystem Name - pro-netiq-hr\nTotal number of error messages - 2
----------------------------974030822407525684705359
Content-Disposition: form-data; name="file"; filename="error.csv"
Content-Type: text/csv
 
correlationId,applicationName,errorProviderName,messageObjectName,messageObjectKey,enumber,vnumber,createdDateTime,errorResponseStatusCode,errorResponsePayload
67ghg34f-7584-4439-8e62-d10d22070186,kle-process-api-netiq-hr,NetIQ API,worker-object,987654,E54321,V097654,2025-08-13T12:05:23,500,"{id: ID-test-90bd8d7c-fb64-4b5c-9585-fa8e94b19dc4,result: ERROR,code: 500,application: kle-system-api-netiq-hr,provider: NetIQ API,payload: Internal Server Error : An internal error occurred. Please try again. : <html>\\r\\n<head><title>502 Bad Gateway</title></head>\\r\\n<body>\\r\\n<center><h1>502 Bad Gateway</h1></center>\\r\\n</body>\\r\\n</html>\\r\\n}"
 
----------------------------974030822407525684705359--

Dataweave I tried

%dw 2.0
output application/csv header=true
---
payload.parts.file.content map ((item, index) -> {
  correlationId: item.correlationId,
  applicationName: item.applicationName,
  errorProviderName: item.errorProviderName,
  messageObjectName: item.messageObjectName,
  messageObjectKey: item.messageObjectKey,
  enumber: item.enumber,
  vnumber: item.vnumber,
  createdDateTime: item.createdDateTime,
  errorResponseStatusCode: item.errorResponseStatusCode,
  errorResponsePayload: do {
    var payloadStr = item.errorResponsePayload default ""
    var extractedMessage =
      if (payloadStr contains "\"payload\"") 
      do {
        var parsedJson = read(payloadStr, \application/json)
        var payloadContent = parsedJson.payload default payloadStr
        var cleanedContent = payloadContent 
                              replace /<[^>]*>/ with "" 
                              replace /\\r\\n/ with " "
        var normalizedContent = cleanedContent replace /\s+/ with " "
        ---
        trim(normalizedContent)
      } 
      else payloadStr
    ---
    extractedMessage
  }
})

Problem

When I test this in the DataWeave Playground, I get this error:

Unable to resolve reference of: application

Unable to resolve reference of: json

I have tried multiple variations (like escaping the content type, or using single quotes), but I am still not getting the desired transformation.

Question

What is the correct way in DataWeave to:

Read and parse the JSON string inside a CSV field?
Clean up HTML and newline characters?

Any help or example script would be appreciated.

What exactly do you want to do with the JSON string? And what do you mean by clean up HTML and newline characters? You need to be more detailed on the objective but I'm getting the impression that this is two different questions into one. If that's the case you should separate them in two questions. — aled
– aled, Commented Oct 6 at 13:45
There are some syntax errors in the script. See the missing quotes in read(). Are you sure you copied the same version used? Also copy the errors verbatim using code block format. — aled
– aled, Commented Oct 6 at 16:30
Hi @aled, requirement is to just remove the \"" from the input payload, - "{\""id\"", we need to later convert the cleaned output to CSV but these newline characters are creating problems. If you observer the input and expected payload you can understand what I am trying to say. DWL is not working at the moment — Sunny85
– Sunny85, Commented Oct 7 at 2:29
I see. You should add that to the question. Don't expect others to analyze the samples to understand the requeriments. Help others to help you. — aled
– aled, Commented Oct 7 at 3:37
The problem seems to be that the JSON field inside the CSV has been escaped multi times. One to fit as a JSON string and another for the CSV format. That is probably confusing the CSV parser in DataWeave. In general mixing escaping of two different languages or formats can cause this kind of issues. — aled
– aled, Commented Oct 7 at 14:25

Harshank Bansal · Accepted Answer · 2025-10-07 16:43:44Z

As pointed by aled, the escaping within the CSV is not what you would expect usually. It looks like the double quotes within JSON is escaped with two characters \" instead of single \ or ". Therefore you would need to do some string manipulation on "raw" format of the file and essentially replace the \" with single \.

The cleaning of HTML is fine, I have added some more replace operations for further removing additional whitespaces, you can delete them them you do not need them.

To get the raw format you can use the .^raw Metadata Selector

Additionally you can avoid remapping all the fields by using update operator map only the required fields. It will significantly reduce the length of the dataweave

%dw 2.0
output application/json
fun fixMessyCsv(messyCsvStr) =
    read(
        messyCsvStr replace '\""' with '\\"' // eg: will replace {\""id\"".. to {\"id\".. 
        , "application/csv"
    )

fun cleanErrorResponse(str) = 
    read(str, "json")  update {
        case .payload -> $ replace /<[^>]*>/ with "" 
            replace /(\\r)?(\\n)/ with " "
            replace /\s+/ with " " // replace multiple spaces with single
            replace /\s$/ with "" // remove last space if required
    }
---
fixMessyCsv(payload.parts.file.content.^raw)
map ((item, index) -> 
    item  update {
        case .errorResponsePayload -> cleanErrorResponse($)
    }
)

As the output format is not clear, this may not be the exact dataweave you need, however it should help you proceed and read the required JSON as you need in next transformers.

Note: The string manipulation to read CSV is not ideal but that is what I can think of. If you have control of the source from where it is coming from, I would recommend to see if you can fix this within that itself.

Collectives™ on Stack Overflow

How to extract and clean JSON payload inside CSV field from multipart data in MuleSoft DataWeave 2.0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related