This is a tool to load Excel data into Oracle Applications and extract data from Oracle Applications into Excel.
The xls4oracle SQL tool, can execute an apps initialize before the extract, enabling queries on organizations views. The SQL extract tool is free to use.
The only way (supported by Oracle) to load Excel data into Oracle Applications, is to use either Interface Tables or API’s. The xls4oracle tool can load Excel data into both. The tool is build to handle the datastructures of API’s, including table structures.
The way you could choose to load data into Oracle Applications without the xls4oracle tool:
You would need to:
- use excel to create a csv file
- use ftp to transfer the file to the unix/linux box
- use sqlloader to load data into a temporary table
- use a custom build plsql program to load the data into oracle applications
The ways to load data into Oracle Applications using the xls4oracle tool:
With the xls4oracle tool you can:
- use the xls4oracle tool to load directly into oracle applications (apps password is needed)
- use the xls4oracle generated script to load from client sqlplus or server sqlplus
- use the concurrent program “API Load” to load the script
To add a new column in your excel sheet, you only need to map it to the correct api column found on the mapping sheet.
The xls4oracle tool
This is a brilliant tool to load legacy data into Oracle Applications. It is brilliant because there are many ready to use templates, and it is brilliant because it is a simple excel task to customize it. To add or remove columns is done by simple excel actions, and more complex tasks can be handled by modifications of the PLSQL also part of the excel template.
The xls4oracle dataload tool, is actually just a mapping tool, that will generate the mapping section of the plsql needed for the dataload. The mapping will generate plsql statements for the API data structure with the values from the excel data sheet.
The xls4oracle tool can do a complete customer load. The load customer template is creating customer, address, related customer, site usages, profiles and profile amounts from one line in excel, calling API’s up till 10 times.
The xls4oracle tool and the “Load AR Invoices” template is using multiple excel lines to generate multiple plsql tables for a single API call.
Use one of the available templates (or customize it) or build you own dataload excel sheet. The xls4oracle Dataload tool is using a plsql script and mapping data from the Excel template to build execution script. The builded script is spooled and can be executed real time (from excel) or moved for execution to other environments. Load status and error messages is updated on each loaded row in the data sheet.
Video: How to create a xls4oracle template
Pls. download this zip file to see a 10 min. video showing how to create a template. The included word document explains the actions on the video.
Download: Video: How to create a xls4oracle template - posted 28-02-2009