The xls4oracle tool
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.
To load Excel data into Oracle Applications, you need to use either Interface Tables or API’s. The xls4oracle tool can load Excel data into the interface tables, but also load into API’s. The tool is build to handle the datastructure of API’s, including multiple table structures.
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 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.
the xls4oracle tool
1 named user license: EUR 199
Download:
Current version is developed for Excel 2003 and Excel 2007. The xls4oracle is verified on Windows XP and Windows Vista. To use the tool you need:
-
Oracle Database 10g Client Release 2 (10.2.0.1.0) - with a valid tnsnames
and download the tool. If you install sql*plus, the OO4O will be include in the installation.
This is the full functional version, but without a proper license key it has limited dataload functionality. Pls. request a trial key, that will enable you the full dataload functionality for a limited time. Pls. apply for a trial key for a extended period, if needed or order a license key. With a license key, you will get access to all templates and the excel sheet for initiating concurrent requests.
You need to register (free of charge) and login before download.
Trial License Key:
Pls. Register as user on this site. - and you will soon receive a trial license key. With the trial license key you will be able use the full version of the xls4oracle tool for a limited time.
If you need help with the setup:
Pls. contact: support@xls4oracle.com
How does the dataload work?
For each transaction1 in the data sheet, the xls4oracle tool will generate a plsql script, using the load data, the mapping information and the plsql template. The script will be executed and the return status, including any error messages will be updated on row in the data sheet. The xls4oracle tool can easily be customized to handle new requirements. New load templates is easy to build using the API script generator. The xls4oracle tool is only merging data and using open plsql source. Only the mapping data is generated inside the tool, all other plsql code is open source. This makes it very easy for developing new load templates and customizing existing templates.
The Load Customer templates
There is templates to load and update customers. The load customer can load a complete customer from one line in Excel - executing up to 10 API calls:
- Create Customer account, customer profile and related customer
- Customer Account Site, Party Site and location
- Bill to site use and profile
- Ship to site use
Reference:
The xls4oracle tool is in use at Dako A/S, for loading/updating:
- Customers (incl. addresses, profiles, relations & site usages)
- Pricelist Modifiers
- Open AR Invoices
- Inventory balances and adjustments
- MTL items and OPM items
- Item Cost
- HR data
- Installed Base
The tool is in use for rollout of Oracle Applications to several countries. Many rollouts is now completed and there has only been minor modifications to Dako’s xls4oracle templates to handle the latest rollout. The japanese rollout is also completed including japanese character set handling. Customer updates and pricelist modifier updates are in use regularly.
The Apps password
To load data directly into Oracle Applications, you need the apps password. For production environments, where apps password is restricted, you have at least these options:
- Arrange for a person knowing the apps password, to enter the apps password, when loading to production.
- Send the generated spoolfile to a DBA for execution.
- Set up a concurrent program, to call a sqlplus script. Use a paramenter to pass the spoolfile name. Copy the spoolfile to a unix network drive (a samba share), and execute the concurrent program.
At Dako we are using option 3 for load to production.
Getting started:
To get started, pls. register and download the xls4oracle tool. By registering, you will receive a trial license key, enableing you to use the xls4oracle tool for a limited time. The Load_scott_tiger.xls template will enable you to learn how to use the xls4oracle tool on the scott/tiger tables.
Video: How to create a xls4oracle template
In 12 minutes, this video will show you how to create a new template for updating the HZ_CUST_ACCOUNTS table with excel data. The template is including extract sql script and plsql script using the HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT api. Pls. download this zip file including:
- avi video file
- word document with comments
- the final xls4oracle template
Templates
Templates for the xls4oracle tool. Learn more
Free Developer Scripts
Developer scripts for apps initialize, database object seach, database object source extracts, generate PLSQL’s for API’s and … Learn more
If any questions pls. contact: info@xls4oracle.com
1 A transation, can be defined as one or more excel lines