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

Don wrote:

> 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

There is nothing wrong with what you have been doing. My advice would be that all columns in the staging area be VARCHAR2 and that you use a test with TO_NUMBER and TO_DATE inside their own blocks to identify values that are valid for conversion.

Daniel Morgan Received on Sun Mar 16 2003 - 11:35:44 CST