What is a good tool for loading Oracle from Excel, Access, and flat files.

My project needs to load an Oracle database from many different sources. Some are other Oracle databases but most are flat files, excel spreadsheets, or access databases.

All data is first loaded to a staging area which is an Oracle database. Any scrubbing is done when selecting data from the stagging area.

For the Oracle DB source I use a DB link.

For the other sources we use VBS scripts to convert them to a text file and then use SQL loader to put the data in Oracle. We use VBS because it can easily export Excel and Access to a CSV file.

I want to build something that can easily load almost any of these sources without any coding. For example, when an excel spreadsheet arrive we will:
1) Export each sheet export to a CSV file. 2) Create a stagging table to each sheet using the name of the sheet (or a lookup to rename).
3) Dynamically create a SQL*Loader control file 4) Load the data using SQL*Loader

My theory is that this will abstract the tedious work of accessing these sources. The developers scrubbing the data will always have Oracle as a source (the stagging area) and can use PL/SQL. Once we have wrtten something to load one type of source (Excel, Access, etc) we do not have to write it again, we just configure the program to look for another.

We are currently using WebMethods but it does not really fit. Almost all the work is custom code.

Before we devote resources to this I would like to know if there are any tools available that can do this?

Don Received on Sun Mar 16 2003 - 07:11:50 CST