Friday, February 10, 2012

How to feed Data into Oracle Apps Forms using Dataloader Utility

Friends, this post will quickly take a quick look on using the data loader software…

In General, there is no extensive setup required to run a load. The user must have an Oracle login and password and be logged in to the system. Lets walk through the steps necessary to create a simple load.


This is the DataLoad window. The spreadsheet section of the page is very similar to the spreadsheets in Microsoft Excel. Importing data from an Excel spreadsheet into the DataLoad spreadsheet is very easy.

First open the Excel file that should be copied.


For successful import, this file should have no titles or added fields in the spreadsheet. The spreadsheet above was created to import thirty discrete jobs into Oracle. It contains the job number, the type of job being created, the assembly name, the manufacturing class, quantity, job status, and the start date. It was ordered in this way based on the order in which this data is required when a job is created in Oracle.


The best way to determine exactly what is necessary for data input in Oracle is to open the window into which data will be entered and create a dummy input, recording every command used along the way. These will most likely include use of the TAB key and possibly the Enter key. Any commands that must be used should be recorded in an excel file. For a list of the available commands for use in DataLoad, a list can be found under the Edit menu, under “Commands” or by clicking the “Edit Commands” button in the toolbar.


For this example, the first line in the Book 3 Excel spreadsheet will be used, but will be re-formatted for use with DataLoad.


This first line is how the data were organized in the original file, while the second line has the same data, but it reformatted for DataLoad. The multiple “TAB” commands at the end of the second line will finish a record in Oracle and tab down into the second record for input of another discrete job. The inputs in the file mimic exactly what is necessary from a user when inputting a discrete job. After identifying how the file should be formatted, the rest of the original Excel spreadsheet should be reformatted as well.


A save command could be added to the end of the file, but I would suggest not using that command. If Oracle were to run slow during input, Dataload would continue trying to load information even if Oracle is not responding. This could cause data discrepancies if input errors occur. Visual review of new records in Oracle should be used before saving new inputs.

When the Excel spreadsheet has been formatted correctly, highlight the desired cells to be copied in the Excel spreadsheet. When importing into a DataLoad spreadsheet, DataLoad will automatically recognize the active Excel spreadsheet without being directed towards it.


Now go back into the DataLoad window. Click the “Import data from Excel” button in the toolbar to automatically copy all the cells selected in the excel spreadsheet. This button is on the far left in the picture below. Import can also be done by clicking “Excel Import” under the Tools menu.


The information from Excel is now in DataLoad and read to be copied into Oracle. First, be sure that the Oracle instance is open to the window in which the input will be completed, with the cursor in the field requiring the first input.


Back in DataLoad, there are two necessary fields to be filled before the load can begin, as well as one optional field. These are the Window, Command Group, and Description fields.


Under “Window”, DataLoad will automatically recognize every window open on the PC. Choose the Oracle instance running the input window. For the “Command Group”, choose 11i. This is an older version of the Oracle E-Business Suite that uses the same commands at R12. A description can be entered in the “Description” field, but is not necessary.

Before beginning the load, ensure that the data is correct. This load can be saved for use later. Also, ensure that the open instance of Oracle is still valid and doesn’t require re-login.

When the load is ready to begin, click on either the “Send data to form” button in the toolbar, or the “Send Data” option under the Tools option in the toolbar.


Data insertion will automatically begin. The user may be required to stand by and ensure that data is being entered correctly. When the load has completed, the user can look over the inserted data to ensure its accuracy. If there was an error during input, it may have occurred due to lag in Oracle during insertion. To change this, first close out of the Oracle form without saving the erroneous data and reopen the input form to be ready for the next try.

In DataLoad, the user can specify a delay time after data insertion to account for any lag in processing. To change the delay time, either click on the hourglass button in the toolbar or click the “Delays” option under Tools. This will open the DataLoad Delays window.

clip_image024 clip_image026

Upon changing the delay options, the load can be initiated once again. Continue to change this delay options as often as necessary to ensure an accurate load.

I hope it helped you understand the basics of Data Loader..

2 Responses to “How to feed Data into Oracle Apps Forms using Dataloader Utility”

Anonymous said...
May 30, 2013 at 6:45 AM

I am using new Data Loader and I really liked its macro recording feature which makes it very simple to create loads. You can download a trial edition from www.formsdataloader.com

Anonymous said...
October 21, 2013 at 9:28 AM

I concur the new new Data Loader for Oracle is very good for loading data in Oracle Apps and also in Oracle R12 Self Service forms


Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.