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

I only gave a brief overview here.

I think I'll make the staging area columns all varchar2. I'm not sure on the length yet. Remember, the tables are dynamically created so I could scan the data for the max length. Maybe I'll make them as big as possible.

 If I can get 90% of them working I can tweak it from there.

I was hoping there was a product that did something like this. Maybe I can build this product.

On Sun, 16 Mar 2003 08:12:58 -0800, "Ana C. Dent" <anacedent_at_hotmail.com> wrote:

>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
>
>
>Rhetorical question follows...
>
>How do you plan on handling various numbers of columns
>of varying data types (i.e. VARCHAR2, NUMBER, DATE, etc)?
Received on Sun Mar 16 2003 - 19:54:51 CST