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

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:

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):

I then press the Create Workspace button.

Enter the workspace by clicking the link.

Login to the Workspace.

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

Open the SQL Workshop tool:

Then click on Utilities.

Now select the Data Workshop:

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

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:

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.

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.

The Primary Key configuration page:

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.

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.