Leveraging APEX in XE 11gR2 to rapidly load Excel data into a database table

1

Whilst preparing for the Thursday Thunder session at the last day of the ODTUG Kaleidoscope 2011 conference in Long Beach, where an all star team of moderators and developers will present a live application development session with key components from Fusion Middleware, I came across a very useful feature of APEX, readily available in my local light weight Oracle XE Database 11gR2.

The Thursday Thunder session will create a portal for participants of a technology conference just like ODTUG Kaleidoscope itself. It will also create a back office application for the conference’s organizing team and it will model and implement a key business process addressing the submission, acceptance and delivery of presentations at the conference. This session has three moderators – Chris Muir, Lonneke Dikmans and Duncan Mills – and a development team of five – Edwin Biemond, Peter Ebell, Ronald van Luttikhuizen, Luc Bors and Steven Davelaar. In order to have a case that is as realistic as possible, I requested the ODTUG organizers for the actual data of this year’s conference – in terms of presenters, sessions and the planning of them all.

They were very helpful and almost instantly provided me with the requested data – in an Excel spreadsheet file. We need to have that data in a bunch of relational database tables. My challenge therefore was to import the data from the spreadsheet into the database. A couple of options are open to me – including an external table in the database, some custom code for reading the Excel file or its csv counterpart or I could make use of the APEX utility in the SQL Workshop component. I used the latter – and it worked like a charm.

The original Excel document is shown here:

Image

Lots of rows and columns. Not overly complex, but still a lot of work to process. Not so with APEX.

I started the XE Database Control in my browser – available at http://127.0.0.1:8080/apex/f?p=4950.

On the homepage, I click on the APEX button. Next I enter the details for a new database user (ODTUG) as well as a new APEX user (same name):

Image

I then press the Create Workspace button.

Image

Enter the workspace by clicking the link.

Login to the Workspace. Image

APEX then asks me to change the password and login again with the changed password.

Image

Open the SQL Workshop tool:

Image

Then click on Utilities.

Image

Now select the Data Workshop:

Image

Several options to load or export data are available. I want to process Spreadsheet Data, so I select Spreadsheet Data:

Image

I have select two options – whether to create a new table or use an existing one and whether to upload a file or copy and paste the data:

Image

Select all data in the Excel spreadsheet. Press CTRL+C (Copy to clipboard). Then switch back to APEX and Paste (CTRL+V) from clipboard to textarea.

Image

Press Next.

The table properties are shown. I need to change the proposed column name SESSION, as it is a reserverd word (could APEX not have noticed that for itself?). Click on Next again.

Image

The Primary Key configuration page:

Image

I will have a new column added – called ID – that is populated with sequence based generated values.

Click Load Data to create the table and insert the records based on the data pasted from the Excel sheet.

Image

In hardly any time at all, I have my table created based on an Excel spreadsheet worth of data. This is only a small first step on the road to a grand finale at ODTUG – Thursday morning, It’s Fusion Middleware Thursday Thunder! Powered by APEX.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Very nice. I liked this application tool. I am asking if this tool support our locale (Arabic) features like Right-t0-Left orientation property that is found in Oracle Developer Forms and Reports tools.
    thanks.

Leave a Reply