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

Question

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"
}

Answer 1

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.