Process Excel files with OIC + Oracle Functions by Stan Tanev

imageIn 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

  1. 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).
  2. 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.

clip_image003 Blog clip_image005 Twitter clip_image004 LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

Technorati Tags: SOA Community,Oracle SOA,Oracle BPM,OPN,Jürgen Kress

    About Jürgen Kress
    As a middleware expert Jürgen works at Oracle EMEA Alliances and Channels, responsible for Oracle’s EMEA Fusion Middleware partner business. He is the founder of the Oracle SOA & BPM and the WebLogic Partner Communities and the global Oracle Partner Advisory Councils. With more than 5000 members from all over the world the Middleware Partner Community is the most successful and active community at Oracle. Jürgen manages the community with monthly newsletters, webcasts and conferences. He hosts his annual Fusion Middleware Partner Community Forums and the Fusion Middleware Summer Camps, where more than 200 partners get product updates, roadmap insights and hands-on trainings. Supplemented by many web 2.0 tools like twitter, discussion forums, online communities, blogs and wikis. For the SOA & Cloud Symposium by Thomas Erl, Jürgen is a member of the steering board. He is also a frequent speaker at conferences like the SOA & BPM Integration Days, JAX, UKOUG, OUGN, or OOP.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    %d bloggers like this: