Generate a Parsing Logic from Excel With Appian.

Today we will look at a way in which we can create a nested object structure similar to JSON from spreadsheet data with Appian.

First, we will create a logic to read the Excel data and extract the details. We will have a spreadsheet where the headers indicate the keys and the rows indicate the values.

Now we will create the logic to generate the keys and values using expression rules in Appian. The first rule is to get the Excel headers. T_GetExcelHeaders

index(
  index(
    index(
      todatasubset(
        readexcelimportfile(
          excelDocument: ri!doc,
          sheetNumber: 0,
          startRow: 0
        )
      ).data,
      "result"
    ),
    "values"
  ),
  1
)

Next, we will create an expression to get the values. T_GetExcelValues

index(
      todatasubset(
        readexcelimportfile(
          excelDocument: ri!doc,
          sheetNumber: 0,
          startRow: 1
        )
      ).data,
      "result"
    )

Next, create a rule to generate key-value pairs called T_CreateKeyValue, with rule inputs for keys and values of Any Type.

a!forEach(
    ri!values,
    if(count(ri!keys)=count(fv!item),a!update(
      data: a!map(),
      index: ri!keys,
      value: fv!item
    ),{})
  )

Note that the map will be updated dynamically, creating a list of maps in a loop similar to JSON.

Now, if we want to create nested objects, we can use the following expression T_CreatedNestedObjects. This allows you to pass another list of maps and another key to an existing list of maps. These rules can be used to create various expressions that are useful in multiple situations.

if(
  count(ri!ListOfMap)=count(ri!values),
  a!forEach(
    ri!ListOfMap,
    a!update(
      data: fv!item,
      index: ri!key,
      value: ri!values[fv!index]
    )
  ),
  {}
)

Now we can generate nested objects similar to the JSON format.

Using this, we have now constructed a simple structure similar to JSON. This can be passed into any plugin components to continue processing. These can be used in most places to create a JSON-like object as needed.

I hope this provides insight into generating parsing logic from a spreadsheet or other similar inputs. We will continue exploring more in upcoming posts.

Keep coding, cheers!!!