How to create a map of Excel file key-value without using column name in Dataweave?

0
votes

I am reading an excel file (.xlsx) into a json array and I am creating into a map because I want to apply validations to each of the column individually. I am able to access it using the column name like so, Excel file is :

column A,          column B
value of Column A, value of column B

I am accessing it like this : payload map(item, index) -> "Column Name A" : item."Column Name A", "Column Name B" : item."Column Name B"

Where column A and B are the excel column header.

What I want to do is to create the same map but using the column index like

payload map(item, index) ->
        item[0].key : item[0],
        item[1].key : item[1]

So that I do not have to hard code the excel header name and I can rely on the index of the excel columns.

I have tried using pluck $$ to create a map of Keys but I cannot create a map of keys-value, I am not able to use item[0] as key in a map.

How can I achieve above without using excel column header name?

Expected output should be like this :

{ 
  "Column A " : "value of Column A",
  "Column B" : "value of Column B",
  "Errors" : "Column A is not valid"
}
3
mulemule-studiodataweaveanypoint-studiomulesoft
The keys should be a excel column header in the same order that is in the excel file. I do not think what you have will achieve that, right? Since it does not look at the json array (create from excel file) at all? – anxiousAvocado
Could you please provide an example of the expected output? Thank you. – olamiral
@olamiral yes I added that. Thanks – anxiousAvocado
In this case, you don't need to do nothing, since your original payload would be something like [{"col1": "val1.1", "col2": "val1.2"}, {"col1": "val2.1", "col2": "val2.2"}]. If you want to get a specific item, you can simply use the index: payload[0]will return {"col1": "val1.1", "col2": "val1.2"} – olamiral
I want to actually access the inside of each of those key value pair. like payload[0][0] : payload[0][0].value, something like this? – anxiousAvocado

3 Answers

0
votes

Assuming that you'd like to validate each payload item loaded from an Excel file, you could use the following DataWeave expression:

%dw 2.0
output application/json
fun validate(col, val) = 
  if (isEmpty(val)) {"error": col ++ ": value is null or empty"}
  else {}
fun validateRow(row) = 
  "Errors": 
    flatten([] << ((row mapObject ((value, key, index) -> ((validate((key), value))))).error default []))
---
payload map (item, index) -> item ++ validateRow(item)

Using the following input payload:

[
    {"col1": "val1.1", "col2": "val1.2", "col3": "val1.3"},
    {"col1": "val2.1", "col2": "val2.2", "col3": null}
]

would result in:

[
  {
    "col1": "val1.1",
    "col2": "val1.2",
    "col3": "val1.3",
    "Errors": [
      
    ]
  },
  {
    "col1": "val2.1",
    "col2": "val2.2",
    "col3": null,
    "Errors": [
      "col3: value is null or empty"
    ]
  }
]

The expression will result in an output slightly different than the one you're expecting, but this version will allow you to have an array of error messages that can be easier to manipulate later on in your flow.

One thing to keep in mind is the possibility to have more than one error message per column. If that's the case, then the DataWeave expression would need some adjustments.

0
votes

Try just using the index. It should work just fine.

%dw 2.0
output application/json
---
({ "someKey": "Val1", "lksajdfkl": "Val2" })[1]

results to

"Val2"

And if you want to use a variable as a key you have to wrap it in parentheses.

EG, to transform { "key": "SomeOtherKey", "val": 123 } to { "SomeOtherKey": 123 } you could do (payload.key): payload.val

0
votes

Try this:

%dw 2.0
output application/json

var rules = {
    "0": {
        key: "Column A",  
        val: (val) -> !isEmpty(val),
    },
    "1": {
        key: "Column B",
        val: (val) -> val ~= "value of Column B"
    }
}

fun validate(v, k, i) = 
    [
        ("Invalid column name: '$(k)' should be '$(rules[i].key)'") if (rules[i]? and rules[i].key? and k != rules[i].key),
        ("Invalid value for $(rules[i].key): '$(v default "null")'") if (rules[i]? and rules[i].val? and (!(rules[i].val(v))))
    ]

fun validate(obj) =
    obj pluck { v: $, k: $$ as String, i: $$$ as String } reduce ((kvp,acc={}) -> 
    do {
        var validation = validate(kvp.v, kvp.k, kvp.i)
        ---
        {
            (acc - "Errors"),
            (kvp.k): kvp.v,
            ("Errors": (acc.Errors default []) ++
                (if (sizeOf(validation) > 0) validation else [])
            ) if(acc.Errors? or sizeOf(validation) > 0)
        }
    }
)

---
payload map validate($)

Output:

[
  {
    "Column A": "value of Column A",
    "Column B": "value of Column B"
  },
  {
    "Column A": "",
    "Column B": "value of Column B",
    "Errors": [
      "Invalid value for Column A: ''"
    ]
  },
  {
    "Column A": "value of Column A",
    "Column B": "value of Column C",
    "Errors": [
      "Invalid value for Column B: 'value of Column C'"
    ]
  },
  {
    "Column A": null,
    "Column C": "value of Column D",
    "Errors": [
      "Invalid value for Column A: 'null'",
      "Invalid column name: 'Column C' should be 'Column B'",
      "Invalid value for Column B: 'value of Column D'"
    ]
  }
]