Home > OS >  Dataweave - Transform CSV structure
Dataweave - Transform CSV structure

Time:11-12

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.

  • Related