How to Use Data Loader to Automate Data Entry in Oracle EBS


Data Loader tutorial for Oracle Apps — a step-by-step guide to the Macro Loading method.

Automate Data Entry in Oracle Apps  |  Data Loader for Oracle R12

As an Oracle EBS Apps professional, one has to enter records frequently for testing purposes. Data entry has to be done through front-end application forms only, because it's very difficult to load data directly into Oracle back-end tables. Although Oracle provides open interface tables, following the Oracle guidelines is quite difficult, and DBAs are also reluctant to provide direct connections to the back-end database due to security issues.

Because of these reasons, Data Loader becomes an essential tool for automating data loading into the Oracle EBS Suite.

Advantages of This Tool

  1. Loads data directly into front-end forms — no direct connection to the database is necessary.
  2. Needs no special technical expertise — can be used by non-technical end users since data is filled into forms.
  3. Saves lots of time — prepare a load once and duplicate it as many times as you need.
  4. Entirely transparent to the application — the application behaves as if data was entered by a user.

Data Loader

There are multiple ways Data Loader can be used to load data:

In this article we discuss the Macro Loading method. Note: this tutorial is for loading data into traditional Oracle Java forms. If you want to load data into Oracle Self Service forms, use HTML Forms Data Loader instead — see the step-by-step tutorial.

Macro Loading in Three Steps

  1. Record
  2. Populate the data from Excel, CSV, or any delimited file
  3. Play

Detailed Walkthrough

  1. Start your target application form in which you'd like to automate loading of data, and move the cursor to the first field. For example, to add new users in Oracle EBS, log in to Oracle Apps and navigate to System Administrator → Security → Users, as shown below.

    Data Loader: Users Form in Oracle EBS R12

  2. Start Forms Data Loader by clicking its desktop icon.
  3. Click "Create New by Macro Recording" — a dialog will be displayed.

    Data Loader: Record Macro Button

  4. Click "Start Recording" in the dialog.

    Start Recording button in Data Loader

  5. Change to your target application form window using a mouse click or Alt+Tab.
  6. Enter a sample record in your target application form just as you normally would. Use keyboard keys to navigate the target form — not mouse clicks — unless you've checked "Record Mouse Clicks". You can use Alt+FV (Save and Proceed) to save the sample record.
  7. After entering the sample record, click "Stop Recording" in the dialog.

    Stop recording in Data Loader

  8. Click "Create Load".

    Create Load

  9. You'll now see an Excel-like sheet in Forms Data Loader with all the fields you entered in the form.

    Data Loader recorded sheet

  10. Populate this sheet by copying and pasting data from Excel, or any CSV or delimited file. Use "Paste in Text Columns only" and Auto Fill to fill navigation keystrokes. More information.

    Populated sheet in Data Loader

  11. Once populated, make sure the Users Form is open in Oracle EBS Applications with the cursor in the first field, then click "Load" in Forms Data Loader.

    Load button in Data Loader

  12. Forms Data Loader will show the "Load Settings" dialog. Choose the target form window title from the dropdown and click "Start".

    Data Loader Load Settings

That's it — you can now watch Data Loader automatically load all the data into the form.

Download Data Loader

For more information, visit www.formsdataloader.com.

Do's and Don'ts for Data Loader

For troubleshooting loads, see the Data Loader FAQ.