Importing data from MS Excel into your Oracle APEX application is hard?

Think again … it can be as easy as 1 – 2 – 3.

The Use Case

In almost every Oracle database application the business users will want to bulk import data regularly from their MS Excel workbooks.

Unfortunately, extracting data from MS Excel workbooks is technically challenging and using CSV as an intermediate format is not satisfactory either.

Why OPAL:XE?

Being a professional Oracle and specifically APEX developer for the last 15 years, Dietmar Aust has solved these problems numerous times.

Understanding the needs of the database developer, he has created a very flexible yet easy to use solution to get you started in a matter of hours with your first project.

Dietmar is an Oracle ACE.

Oracle ACE

What is it?

OPAL:XE is a toolkit for the Oracle database developer to extract data from MS Excel workbooks directly and on the fly.

It provides a PL/SQL API to simplify the process. Using the included wizards (implemented with Oracle APEX) it can even be done declaratively with little or no programming required in a matter of hours.

The Story

I have been building web applications for 15 years now. They provide many benefits but certainly have do also have some limitations, one certainly being usuability dealing with many rows of data.

Even when using APEX tabular forms or other data grids, the ease of use of MS Excel is unmatched when manipulating many rows of data at the same time.

Like it or not, the average business user is highly familiar with MS Excel and how to work with the data there. It gives them a sense of security when they can compare and validate (or even update) their data with information from other MS Excel workbooks.

Thus, in many applications I have come across very good reasons why my users requested to upload their existing MS Excel workbooks directly into the application, for example when:

    • they have to import data from other data sources (like SAP) and a direct interface is not available.
    • they have to provide a monthly update on data relevant for their business process, but a web user interface is not suitable for updating 100+ rows of data manually. Using copy/paste or import from MS Excel where the original data resides is clearly a much better alternative for the end user.

These two were the most common and prominent use cases, but there are many others depending on the specific client and their needs.

Using CSV as a workaround is common practice but has many limitations it itself, the two major ones being:

  1. The CSV format can only handle a single worksheet. Thus when you need to import data from multiple worksheets you would have to repeat the cycle “Save as CSV” and “Upload file” multiple files. Try uploading 12 sheets in a row …
  2. The CSV format is text based, thus dealing with unwanted formatting masks is really difficult when parsing the data , e.g. 1,234567E+19, 1.000,00€ or 25 are all numbers.

Only by importing data from native MS Excel workbooks (.xls or .xlsx) you can be sure to extract the datatypes properly and you can provide the requested ease of use by your clients.

Your Benefits

  • Your business users will love it

    Finally, the business users can directly leverage their existing MS Excel workbooks (which they alread share with their colleagues). It is convenient for them and makes them fast and productive.

  • Developer Productivity

    Building a fully functional import wizard (including business validations and processing) is a matter of hours, not days. You can easily add your custom pl/sql processing.

  • Import Flexibility

    There are many features available to specify the import sections. They can begin in the upper left corner, but also everywhere else. They can even be found using text to search for.

  • Many samples to get you started right away

    You will find many samples for different types of applications as well as a tutorial on how to integrate that into your own application.

  • Successful integration guaranteed!

    We truly want to make you as our customer happy. Thus we will implement in your first project with us a working prototype based on Oracle Application Express. It will cover the complete integration cycle as well as some of the columns and sections you want to use. It will be a fully functional technical prototype.

Architecture

Before you can process a MS Excel Sheet you first need to define the metadata of the workbook, the sections and the fields you want to extract.

(1) At runtime this metadata together with the MS Excel sheet (as a blob) will be passed to the pl/sql api.

(2) There it will be encoded as XML and sent to a J2EE server for further processing.

(3) The resulting data will be encoded in XML and sent back to the pl/sql where it will store the data in generic runtime tables (4).

First Prototype Implementation Included!

We truly want to make you as our customer happy and we believe 100% in what we offer.

Thus we will implement in your first project with us a working prototype based on Oracle Application Express. It will cover the complete integration cycle as well as some of the columns and sections you want to use. It will be a fully functional technical prototype.

Pricing

  • ASFU License
  • 1500
  • single application
  • Support / Upgrades (optional):
    300 €
    per year, already included in first year
  • Server License
  • 7500
  • unlimited applications
  • Support / Upgrades (optional):
    1500 €
    per year, already included in first year
  • SAAS License
  • 7500
  • unlimited applications
  • Support / Upgrades (optional):
    1500 €
    per year, already included in first year

You can choose between the following types of licenses:

  • ASFU License: This is an Application Specific Full Use License (ASFU), allowing you to use it for a single application. You need to obtain a license for each production server, development and test server don’t need require a license.
  • Server License: The server license allows you to use OPAL:XE for any number of applications. You need to obtain a license for each production server, development and test server don’t need require a license.
  • SAAS License: If you are providing Software as a Service (SAAS) to end customers (and charging money for it) you will need this license, you cannot use an ASFU license for SAAS. You need to obtain a license for each production server, development and test server don’t need require a license.
  • OEM License: You can also embed OPAL:XE into your own applications and sell them to your customers. Please ask us for a quote.

The Next Step?

Click on the button “Buy Now” to get started immediately and claim your free prototype implementation.

Need more information? Click on the button “Request Personal Demo” to get a guided presentation, we can answer all questions then.