Blog Posts Process Management Service Oriented Architecture (SOA)

Process Excel files with OIC + Oracle Functions by Stan Tanev

Blog: PaaS Community

In this blog post I will explore how we can extend the native capabilities of Oracle Integration (OIC) with Oracle Functions to process Excel files.

Although OIC can handle a number of file formats natively, .xlsx or .xls files need a bit of extra love.

The inspiration for this blog comes of the back of several customer enquiries into this subject.

The simple solution for most customers is to convert the Excel file formats to CSV and subsequently process them with OIC. I will use this approach here too but with a little bit of help from other OCI services such as Oracle Functions, an API Gateway and Object Storage.

A video of this demo is available here.

The Solution

As OIC does not natively support Excel files you need to write a bit of extra code to handle them. You can either store this code within OIC as a JavaScript library or use something like Oracle Functions as a container for you code. I have chosen to use this approach here and will rely on Oracle Functions to do the file conversion for me.

With this approach in mind, i can think of two ways to implement this pattern:

The All in one Approach:

Above: a high-level design that captures a single integration flow that will convert and process excel files

The flow begins with an Excel file that resides on an FTP server (this can be any FTP/SFTP server but for this demo I will use the file server embedded within OIC). I have chosen to use an FTP server as my starting point for this flow as i still see the predominant use of file servers over cloud storage alternatives in most integration flows. In this step, OIC will use the FTP adaptor and its native abilities to list and download my Excel file(s).
The Excel file(s) will then be pushed to OCI Object Storage by OIC. Refer to my previous blog if you need a refresher on how to write files to Object Storage with Oracle Integration. I am using Object Storage here as a transient point for these files because I want to provide an easy and secure way for my file conversion function (which is hosted in OCI) to access these Excel files. It is also possible to achieve this without Object Storage … as the function code can be altered to simply get the file directly from OIC’s file server via FTP rather then retrieve it from Object Storage. Read the complete article here.

PaaS Partner Community

For regular information on Oracle PaaS become a member in the PaaS (Integration & Process) Partner Community please register here.

Blog Twitter LinkedIn Facebook Wiki