Blog Posts Process Management Service Oriented Architecture (SOA)

Process Excel files with OIC + Oracle Functions by Stan Tanev

Blog: PaaS Community

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

    Leave a Comment

    Get the BPI Web Feed

    Using the HTML code below, you can display this Business Process Incubator page content with the current filter and sorting inside your web site for FREE.

    Copy/Paste this code in your website html code:

    <iframe src="" frameborder="0" scrolling="auto" width="100%" height="700">

    Customizing your BPI Web Feed

    You can click on the Get the BPI Web Feed link on any of our page to create the best possible feed for your site. Here are a few tips to customize your BPI Web Feed.

    Customizing the Content Filter
    On any page, you can add filter criteria using the MORE FILTERS interface:

    Customizing the Content Filter

    Customizing the Content Sorting
    Clicking on the sorting options will also change the way your BPI Web Feed will be ordered on your site:

    Get the BPI Web Feed

    Some integration examples