Ignore empty string when mapping CSV to JSON using Dataweave

0
votes

I am mapping from CSV to JSON and some of the CSV fields are showing as "" in the JSON mapping. How can I get Dataweave to ignore "" and only populate when there is a value?

I have tried skipNullOn but this doesn't work. Is there another way to do it or do I have to add an when condition around each field?

Seeing this error with the recursive solution:

enter image description here

Thanks

3
jsoncsvmuleanypoint-studiodataweave
Your situation is very similar to this one. Try using that approach, and modifying the acceptable function. - Ryan Hoegg
Hi Ryan. I am seeing an error with the code in the recursive solution. Is there something missing? - user3165854
It worked when I tried it :) What's the error? - Ryan Hoegg
I've added a screenshot above. All looks like it should work but dataweave doesn't seem to like it - user3165854
The filterKeyValue function needed to return an object instead of a tuple. I updated the other answer. This seems like a change in the language in the past couple of months. I'll add an answer below. - Ryan Hoegg

3 Answers

1
votes

Try this solution, adapted from this answer. We recursively decide if we want to remove a field from the model, using match in the acceptable function to remove empty strings, nulls, and empty objects.

%dw 1.0
%output application/json

%function acceptable(value) (
    value match {
        :null -> false,
        o is :object -> o != {},
        s is :string -> s != "",
        default -> true
    }
)

%function filterKeyValue(key, value) (
    {(key): value} when acceptable(value) otherwise {}
)

%function removeFields(x)
    x match {
        a is :array -> a map removeFields($),
        o is :object -> o mapObject
            (filterKeyValue($$, removeFields($))),
        default -> $
    }
---
removeFields(payload)
1
votes

here is the sample logic I built (guess, it is what you are looking for). If it doesn't work, give me a sample CSV and the output JSON format you are expecting, I will try to get the logic for you.

Sample CSV Input (with one of the value missing in line 3 and 4)

header1,header2
1,value1
2,value2
3,
,value4

Dataweave

%dw 1.0
%output application/json
---
payload map {
    ("headerValue1": $.header1) when $.header1 != '',
    ("headerValue2": $.header2) when $.header2 != ''
}

Result

[
  {
    "headerValue1": "1",
    "headerValue2": "value1"
  },
  {
    "headerValue1": "2",
    "headerValue2": "value2"
  },
  {
    "headerValue1": "3"
  },
  {
    "headerValue2": "value4"
  }
]
1
votes

Considering you have four fields and you want to skip city field if null or empty, you can also modify the dataweave script to below:

%dw 1.0

%output application/json

{ person: payload map ((payload01 , indexOfPayload01) -> { firstname: payload01.fname, lastname: payload01.lname, address: payload01.address, (city: payload01.city) when payload01.city !=null and payload01.city !='' }) }