working on applications and technology

Recently I need to import some excel data into database for data analysis. At the fist I use SQL Developer to do the impot, but I encouter many problems. So I decide to use apex to do the import and it turn out to be really much better than the SQL Developer import function.

The apex Excel import function is easy and wizard guided, and here 's the step:

1. Login into apex as admin
2. Go to 'Utilities'
   
   
   
   
   
   
   
3. The 'Utilities' include some useful tools and dataload is one of the most useful when import small set of data from text file or Excel file.
   
   
   
   
   
   
   
4. Dataload utility could import data file text file, Excel file and XML file.
   
   
   
   
   
   
   
5. There 's no need to create a table for data import, apex allow us choose whether this import will create a new table automatically.
   
   
   
   
   
   
   
   
   
   the most convinient way is to use copy and paste to copy Excel file directly into apex.
   
   
   
   
   
   
   
6. Then we will have the opportunity to define the table column which is used to hold the imported data.
   
   
   
   
   
   
   
   
   
7. We could also define an sequence to generate primary key ID for the new table.
   
   
   
   
   
   
   
   
   
   Just click 'Load Data' and we 're redirect to the dataload report screen which show the total imported rows.
   
   
   
   

It is really easy!

By the way, I do not define date data type for the excel date column, I just treat it as if it is just varchar2. and after the import, It is easy to convert it to date datatype in database using to_date function. I could avoid many problem when dealing with importing Excel date column. It is much eaiser to do convertion inside database!

Recommended books: 

Pro Oracle Application Express (Expert's Voice in Oracle)

Author: Scott Spendolini, John Scott

Publisher: Apress (2008)

Binding: Paperback, 700 pages