I'm trying to transform the structure of a .CSV using Dataweave 2.0
Here is my input csv example:
gaID,gender,age,city,state
GA1.3.332,male,20-30,,
GA1.3.1041d,female,30-40,Sao Paulo,Sao Paulo
GA1.3.1041d,,,Sao Paulo
and I need to create this output:
GA1.3.332^gender:male;age:20-30
GA1.3.1041d^gender:female;age:30-40;city:Sao Paulo;state:Sao Paulo
GA1.3.1041d^state:Sao Paulo
Notice that when the input attributes are null they should not be present in the output and that's the problem I have.
So far I have this code but does not fully comply with my need because its writing all the attributes even if are null or not.
%dw 2.0
output application/csv header=false, separator=';'
---
payload map {
c1: $.gaID "^" "gender:" $.gender,
c2: "age:" $.age,
c3: "city:" $.city,
c4: "state:" $.state,
c5: "maritalStatus:" $.martitalStatus,
c6: "householdIncome:" $.householdIncome,
c7: "bradSegCustomer:" $.bradSegCustomer,
c8: "bradCustomer:" $.bradCustomer,
c9: "BankClientSegment:" $.BankClientSegment,
c10: "main_account:" $.main_account,
c11: "occupation:" $.occupation,
c12: "presenceofChildren:" $.presenceofChildren,
}
output from my code:
GA1.3.332^gender:male;age:20-30;city:;state:
GA1.3.1041d^gender:female;age:30-40;city:Sao Paulo;state:Sao Paulo
CodePudding user response:
I converted it to be dynamic, except the special transformation for the gaID field, then added the condition to filter the elements that have an empty value using filterObject().
%dw 2.0
output application/csv header=false, separator=';'
---
payload map
($
filterObject ((value, key, index) -> !isEmpty(value))
mapObject((value, key, index) ->
(key): if (key as String != "gaID")
(key as String ":" value)
else (value "^")
)
)
Input:
gaID,gender,age,city,state
GA1.3.332,male,20-30,,
GA1.3.1041d,female,30-40,Sao Paulo,Sao Paulo
GA1.3.1041d,,,Sao Paulo
Output:
GA1.3.332^;gender:male;age:20-30
GA1.3.1041d^;gender:female;age:30-40;city:Sao Paulo;state:Sao Paulo
GA1.3.1041d^;city:Sao Paulo
With a CSV format you can't remove the separator between fields. If the first ';' is a problem then we need to use a String output and concatenate the fields as needed though we lose some of the simplicity of the CSV solution:
%dw 2.0
output application/java
---
payload map
($
filterObject ((value, key, index) -> !isEmpty(value))
mapObject((value, key, index) ->
(key): if (key as String != "gaID")
(key as String ":" value)
else (value "^")
)
pluck ($)
reduce ((item, acc="") -> acc item ";")
) reduce ($$ "\n" $)
This scripts gives an extra ;
at the end of each line. If that is a problem it should not be difficult to add a function to remove it.