Like it or not, Excel is THE tool mankind is managing our planet with.

When talking about Excel in an Appian context, we typically talk about importing and exporting data from, or into, an Excel file. For importing, my go-to approach is to dump the Excel into a text-only staging table, then use a stored procedure to transform the data into its final form and place. For exporting, I try to come away with the export feature of records. If this is not enough, I use the most direct way from the database into the Excel file.

But, I think there is another use case, I want to talk about.

Reference Data

Think of data in an Excel file, you only want to use as a reference in your application. It might be updated daily using a fresh Excel file generated by some other system.

What is the most simple way to make this data available in Appian? Well, create a record using that Excel as a data source. Not possible? Let’s see!

API-Record Data Loopback

While talking to some folks in the Appian community, regards to Mat, I had the idea to expose the data in an uploaded Excel file through a web API. Then consume this API to sync data into an Appian record. 30 minutes later, I had a working prototype.

Let’s dive right in!

The Web API

a!localVariables(
  local!fields: {
    "ACTIVE",
    "F1_1037_LIC_NO",
    "F1_1013_ENTITY_NAM",
    "F1_1002_ENTITY_TYPE",
    "F1_1046_LIC_EXP_DATE",
    "F8_1037_LIC_NO",
    "F8_8008_DMV_PLATE",
    "F8_8012_DMV_VIN",
    "TYPE",
    "F8_8008_CERT_DATE",
    "F8_8011_HACK_DATE",
    "F8_8021_YEAR",
    "FP_1940_AGNT_NUM",
    "F1_AGT_ENTITY_NAM",
    "BASE_TYPE",
    "VEH",
    "F1_1058_TELE_NUM",
    "WEBSITE",
    "F1_BAS_LOCATION",
    "REASON",
    "ORDR_ACTN_DATE",
    "DATN",
    "TIMN",
    "F1_1001_CAMIS_ID",
    "Dummy"
  },
  local!data: readexcelsheetpaging(
    cons!SSH_TLC_FOR_HIRE_VEHICLE_ACTIVE_AND_INACTIVE,
    0,
    if(
      tointeger(http!request.queryParameters.batchNumber) = 1,
      a!pagingInfo(2, 998),
      a!pagingInfo(tointeger(http!request.queryParameters.batchNumber) * 1000, 999)
    )
  ),
  a!httpResponse(
    statusCode: 200,
    body: a!toJson(
      a!forEach(
        items: local!data.data,
        expression: a!update(
          a!map(),
          local!fields,
          fv!item.values
        )
      )
    )
  )
)

Based on the batch number, used for syncing from the record, I read data from the Excel file. Using the list of field names, that I copied from the first row, I transform the rows into a list of dictionaries. This, as JSON, becomes the body of the HTTP response.

The Integration

The integration accepts the batch number and passes it right to the web API.

The Data Source Expression

As simple as it gets. This might be a good spot for data transformation.

The Record

Performance Considerations

I was not really sure what to expect, how long this would take. I downloaded some public data und started with around 10k rows.

When the file becomes larger, the time just to open the file starts to increase. I saw a linear increase up to 100k rows. Make sure to measure performance for your use case.

Data Transformation

Typically, the date and number formats in Excel are crap. Include the transformation step either in the API, or the source expression. This will also increase the sync time by a bit.

Sync

As of now, we do not have a way to initiate a full sync from a process. This leaves us with a daily sync. If you can make sure to get the new file before that sync, you should be fine.

Summary

It is always good to have more options to implement something tailored to specific use cases. Syncing Excel data directly into a record could be just that.

Rock the Excel-Records, and let me hear your thoughts!

Cheers.