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 spreadsheetabove 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 fileshould 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 underTools. This will open the DataLoad Delays window.

 

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..

Credit goes to the below website(s) :

http://www.shareoracleapps.com/2012/02/data-loader-on-oracle-apps-example.html

Data Loader Basics

Introduction:
Data loader is an utility where you can load data into different Form Based systems especially Oracle APPS. This simple utility works by recording keystrokes that are necessary for loading data from frontend.

Software be downloaded free from
http://www.dataload.net/downloads/download.php

Advantages:

• Easy to learn and use
  
• Can save time for repetitive processes
  
• Can be copied from Excel
  
• Can be an option to data edits and complex interfaces if the data are simple
  

Disadvantages:

• Cannot track mouse movements.
  
• Cannot perform interactive sessions to much extent. 
  
• Do not generate a success failure logs.
  

List Of Commands:

DataLoad Command	Application Action(s)
TAB	<Tab>
ENT	<Enter>
*UP	<Up Arrow>
*DN	<Down Arrow>
*LT	<Left Arrow>
*RT	<Right Arrow>
*SP	Save & Proceed
*FE	Field Editor
*PB	Previous Block
*NB	Next Block
*PF	Previous Field
*NF	Next Field
*PR	Previous Record
*NR	Next Record
*ER	Erase Record
*DR	Delete Record
*FR	First Record
*LR	Last Record
*SAVE	Save Record
*SB	Send a single space character
*ST	Select entire field text.
*SLN or *SL(N)	Pause for N seconds. Note 1
*BM	Block Menu
*AX	Alt + X where X is a single letter (A-Z). Note 2
*FI	Find +
*FA	Find All +
*QE	Query Enter +
*QR	Query Run +
*CL	Clear Field +
*IR	Insert record +
*CW(window)	Change to window window. +
*ML(coordinates)	Position the mouse at coordinates and press the left button. ++
*MR(coordinates)	Position the mouse at coordinates and press the right button. ++
\^{f4}	Closing a window
*CW(window)	Make window the new target window for DataLoad.
*SLN or *SL(N)	Sleep for N seconds.
*ML(coordinates)	Position the mouse at coordinates and press the left button.
*MR(coordinates)	Position the mouse at coordinates and press the right button.
*DL(coordinates)	Position the mouse at coordinates and double click the left button.
PROMPT(message)	Prompt the user with message and wait for a response
BACKSPACE	{BACKSPACE}
DELETE	{DELETE}
UP ARROW	{UP}
DOWN ARROW	{DOWN}
LEFT ARROW	{LEFT}
RIGHT ARROW	{RIGHT}
END	{END}
ENTER	{ENTER}
TAB	{TAB}
ESC	{ESC}
HOME	{HOME}
PAGE DOWN	{PGDN}
PAGE UP	{PGUP}
INSERT	{INSERT}
Toggle Numlock1	{NUMLOCK}
Prnt Scrn2	{PRNTSCRN}
F1	{F1}
F2	{F2}
F3	{F3}
F4	{F4}
F5	{F5}
F6	{F6}
F7	{F7}
F8	{F8}
F9	{F9}
F10	{F10}
F11	{F11}
F12	{F12}
F13	{F13}
F14	{F14}
F15	{F15}
F16	{F16}
Note 1 DataLoad can send keystrokes to applications faster than they can be processed. If this problem is encountered, delays can be added to the load which will pause DataLoad at key times. The *SLN command can be added to the spreadsheet to indicate DataLoad should ‘sleep’ for a given number of seconds. E.g. ‘*SL5′ will cause a delay in processing for 5 seconds. Decimal numbers can be used for more precise delays, E.g. *SL0.5 will result in a half second delay. A large number of predefined delays are available in DataLoad and these, along with *SL, are described in greater detail in Using delays. To reduce setup work, predefined delays should be used instead of *SL wherever possible. Note 2 In Oracle Applications it is sometimes necessary to press a button to navigate to another block. This can be achieved by pressing <Alt-X>, where X is the letter that is underlined on the button. Any menu item can also be invoked by pressing <Alt> + the letter underlined on the menu.
To use any combination of the Shift, Control, Alt and right Alt keys one of the following codes should be used. If you want to send the +, ^, % or & keys these characters test must be enclosed in braces {}.
Key	Code
SHIFT	+
CTRL	^
ALT	%
Right Alt	&

Case Study:
Granting  “Application Developer” responsibility to 3 users.
Process:

1. The following example would show how to assign ‘Application Developer” responsibility to the users USER1, USER2 and USER3
   
2. Try to record the process of assigning the responsibility to an user through key-strokes only.
   
3. Record the Keystrokes in terms of Data Load commands.
   
4. Note them sequentially to create the Dataload file (.dlt) as shown in the screenshot below.
   

5. Execute the Data Load being choosing the right window and command group



6. Be absolutely sure no other window becomes active during the process of data loading. 

After completion of data load the window shows the final status.



The sample data file is also attached along with.

Click here to download