webMethods.io Integration processing excel file

Knowledge base
, ,
1

Introduction

This article explains how the excel input file can be processed using out of the box connectors.

Audience

It is assumed that readers of this article know how to create integrations on Webmethods.io.

Use Case

  1. Pick the file from one drive location.
  2. Extract the data from the xlsx file.
  3. Transform the data into the desired format.
  4. Write the data into some third-party storage location.

Assets Developed

  • Workflow: This workflow is responsible for initiating the process on a scheduled basis. It will read xlsx file from one drive location.
  • Flow Service: This service is responsible for getting the data from the workflow and transforming the data into the desired format.

Prerequisite

  • Active webmethods.io Integration account
  • One drive account
  • Input file in xlsx format

Workflow

• Create the workflow and name it “ExcelProcessingWorkFlow”.
• Select the excel online connector from the connector list.
• Configure the connector Excel online

Configure the Excel online connector

• Select Action Get Rows
• Name: Get Packaging list details
• Authorized Excel Online: Create Account

image
image607×699 47.2 KB

• Once the connector is configured, we need to configure the location from where the Excel data needs to be picked up
• In the connector it can read the data only from one drive location.
• Select the folder name. it will show you all the folders available in one drive
• Select the Excel file. This will show all the excel files are in xlsx format. Any excel file in older format like xls format will not be visible in the drop-down menu
• Select the Sheet name. It will show all the sheets present in the excel sheet.
• From Row and to Row are optional fields. If you have the requirement to select some particular rows and columns then we need to pass the values.
• In our use case We want to load all the data from the selected sheet, Therefore we have selected nothing in the rows and columns.

image
image985×508 48.8 KB

• Once we extract the data from the Excel sheet, we need to parse the data so that it can be used for further processing.
• Use Json Stringfy application to stringify the data coming out from Excel online connector.
• After getting the data we will submit the data to the flow service for implementing complex transformation logic.

image
image985×376 65.6 KB

Flow Service

  • This flow service will get invoked from the workflow.
  • In this flow service are logging the data on the monitor tab.
  • In our use case for POC purposes we are mapping the data and creating the output document.

End-to-End Testing

  • Place the file at the configured one drive location.
  • Invoke the workflow to pick the file and start processing.
  • Workflow picks the file and extracts the data from excel sheet.

image
image985×300 66.7 KB

• Logs for getting the excel data from one drive location

image
image985×405 56.2 KB

image
image985×350 98.6 KB

• Below flow service gets invoked from workflow

image
image716×674 67.2 KB

image
image946×419 126 KB

Points to remember

  • Excel online connector supports the latest version of xlsx processing.
  • Every connector has a limit on the payload size. Please refer to the documentation if your use case demands the high payload size processing like in MB’s.
  • Attaching the sample workflow and flow service used in our case.

Flow Service:
TransformExcelData.zip (12.7 KB)

Workflow:
export-fl4df08198b87e1c5bba8168-1708338576316.zip (137.0 KB)

2 Likes
2

from_row and to_row attributes in input of the Excel online connector should be used to process large Excel files in chunks.

Another way of processing Excel files is to combine “SpreadSheet reader” and “SpreadSheet to JSON”. They can be used to process Excel files coming from SharePoint.