Serverless Processing of an Excel File

Photo by Rubaitul Azad on Unsplash

In this article we will be showing you how to parse and process an excel (xlsx) file in Kumologica using excel node.

Kumologica is a free low-code development tool to build serverless integrations. For those who are new to Kumologica I would recommend to go through our articles and Youtube videos to get an insight.

Training & Certification
For a limited time, Kumologica offers free online certification for developers on https://training.kumologica.com/

Use Case

ABC enterprise is having different departments having unique department code . An excel file (xlsx) is holding the lookup code for these departments. The lookup code is available in one of the sheet in the workbook with the name “departments”. A service portal in ABC enterprise needs an API resource that fetches the department name based on the department code available in the excel workbook.

Flow logic

Prerequisite

1. Kumologica designer installed in your machine. https://kumologica.com/download.html
2. Create an AWS S3 bucket with the name — kumoexcelstore. Also download the following sample excel file used in this use case to store in your AWS S3 bucket.

department.xlsx file

Implementation

Following are the steps for building the flow for the above use case.

1. Open Kumologica Designer by typing kl open on command line or a terminal, click the Home button and choose Create New Kumologica Project.
2. Go to the command line in windows or a terminal in mac and point to your workspace. Type the following command to install the excel node: npm install @kumologica/kumologica-contrib-excel.

install via command line

3. Enter name (for example DepartmentLookupFlow), select directory for project and switch Source into From Existing Flow …

4. Copy and Paste the following flow

5. press Create Button.

You should be seeing flow as given below on the designer canvas.

DepartmentLookup flow

Understanding the flow

GET /department is EventListener node is configured to have the EventSource as “Amazon API Gateway”.

Set-Property node will extract the query parameter “code” and store it into the message property departmentcode.

Following is the expression to retrieve the query parameter :

header.event.Records[0].queryStringParameters.code

S3 is the node will retrieve the excel file from the S3 bucket , kumoexcelstore. The ouput of the node is a buffer content available under “Body” property.

Excel node will parse the excel workbook which was downloaded from S3 bucket as a buffer content.

Excel node configuration

Output of the excel node will be as follows:

[
   {
      "sheet":"Logistics",
      "content":[
         {
            "Code":656,
            "Vessel":"VR-8902-AZ"
         },
         {
            "Code":658,
            "Vessel":"VR-8944-AZ"
         },
         {
            "Code":667,
            "Vessel":"VR-8902-AZ2"
         },
         {
            "Code":258,
            "Vessel":"VR-8232-AZ"
         },
         {
            "Code":289,
            "Vessel":"VR-8902-AZ3"
         },
         {
            "Code":390,
            "Vessel":"VR-8933-AZ"
         },
         {
            "Code":340,
            "Vessel":"VR-8911-AZ"
         },
         {
            "Code":191,
            "Vessel":"VR-8902-AZ2"
         }
      ]
   },
   {
      "sheet":"Departments",
      "content":[
         {
            "Code":456,
            "Department":"Marketing General"
         },
         {
            "Code":458,
            "Department":"Marketing Retail"
         },
         {
            "Code":467,
            "Department":"Sales Global"
         },
         {
            "Code":458,
            "Department":"HR Global"
         },
         {
            "Code":489,
            "Department":"Sales APAC"
         },
         {
            "Code":490,
            "Department":"Construction"
         },
         {
            "Code":390,
            "Department":"Construction APAC"
         },
         {
            "Code":391,
            "Department":"Construction Global"
         }
      ]
   },
   {
      "sheet":"ZIP",
      "content":[
         {
            "Location":"Sydney",
            "code":3344
         },
         {
            "Location":"Melbourne",
            "code":3333
         },
         {
            "Location":"San Jose",
            "code":8903
         }
      ]
   }
]

In the above JSON, sheet is the sheet name within the workbook. We need to lookup Department value based on the Code from departments sheet.

Datamapper will be used for filtering and doing a lookup for the department name based on the department code. Following is the JSONata expression for the filtering the department sheet and code under the sheet.

$filter($filter(msg.payload, function($v,$i,$a){
  $v.sheet = 'Departments'
}).content,function($v,$i,$a){
    $v.Code=$number($$.msg.departmentcode)
}).Department

Success is the EventListener End node which will return the lookup value as the response to the API request.

Deployment

1. Select AWS tab on the right panel of Kumologica designer, select your AWS Profile and click connect.
2. Go to “Trigger” section under cloud tab and select the Amazon API Gateway trigger.

API Gateway tigger setting

3. Press Deploy button.

Try it

Invoke the following endpoint using the any REST client of your choice.

https://<<hostname>>/department?code=<<department code>>

Conclusion

This article has shown how easy to develop a serverless API in Kumologica designer that could parse an excel workbook in AWS S3 and do a lookup for the department name within a sheet inside the workbook.

Remember Kumologica is totally free to download and use. Go ahead and give it a try, we would love to hear your feedback.