Just-in-time data exchange across system boundaries is a competitive necessity for transport logistics companies. This is especially true for Track & Trace (T&T) information: the more complex the supply relationships and the more flexible the transport network, the more difficult it becomes to ensure complete T&T data.
The "final 10 to 20 percent" is often the biggest challenge, as this data usually does not arrive via a standardized interface, but rather finds its way into the ERP or TMS as an Excel or text file via email. Often, this results in manual entry by staff: copy and paste is the order of the day.
The Challenge
Every day, various Excel lists containing wagon numbers, container numbers, additional information, and arrival times arrive via email. Operations staff then begrudgingly transfer this data into their IBM i-based Transport Management System—using copy and paste; first Excel, then the green screen, then back to Excel, and so on.
It is logical to consider finding a way to automatically extract these necessary but cumbersome emails and feed the desired information into the IBM i system using the existing EDIfact interface.
The Individual Tasks
What exactly needs to be done—and, more importantly, how?
- Detect when a new, relevant Excel file arrives via email.
- Convert the Excel file (XLSX) into a processable format (CSV, JSON, etc.).
- Select the relevant Excel worksheets and discard the rest.
- Clean up T&T data by removing unwanted spaces and similar characters (normalization).
- Convert T&T data into individual EDIfact records for the IBM i.
- Save EDIfact records into individual text files.
- Transfer the text files to the IBM i ERP at the appropriate actual-time-of-arrival (ATA).
The Solution: Workato + Cloudmersive
The "weapon of choice" is the cloud-based low-code automation and integration platform Workato. The platform promises to solve almost any automation task where data needs to be exchanged and adapted between numerous systems.
The trigger for an automation run (job) is the arrival of an email in the operations department's Office 365 inbox, provided it is identified as a relevant email.
Next, the Excel workbook must be converted into a processable format. For this purpose, we use Cloudmersive, a leading cloud service for a wide range of conversions. Its API specification allows it to isolate XLSX worksheets and convert them into CSV.
The Automation Must Account for All Contingencies
The tricky part of this task is the structure of the Excel data and the proprietary Excel format itself. While the contained sheets and data are somewhat homogeneous, a closer look reveals:
- Variability in spaces within container numbers.
- Different meanings for order numbers depending on the client.
- Extra rows and notes added by dispatchers.
T&T status updates should only be sent once their actual-time-of-arrival (ATA) has been reached—even if they arrive ahead of schedule.
