Oracle Marketing R12 has a standard import facility that allows users to upload data from their desktop and import as organizations and contacts, leads, and event registrations.
While executing the import in Oracle is straightforward, ensuring the source data is in the correct format and will load without errors is anything but. Oracle requires some fields to use record IDs that aren’t available to users. Other fields are conditionally required based on values of related columns. Some fields aren’t validated at all, allowing users to load invalid data into Oracle.
To make life easier for marketing users, I built an Excel template to help layout and validate the data and to automate time-consuming tasks. I’ve been enhancing it ever since. Below are some highlights from the current version, updated to support R12.
Templates for Every Type of Import
Each worksheet supports a different type of import or special scenario—such as capturing data directly from Oracle Marketing’s list export operation to import as new sales leads.
List of Value fields allow selection of valid values from a drop-down. In this case, the value will then populate for all records since Oracle allows only one Source System per import.
To help standardize customer names and ensure new leads are matched with existing customers in Oracle, the template will attempt to find the standardized customer name using the contact’s email domain.
In the example shown below, “oracle.com” maps to “Oracle Corporation,” so the name was standardized and the non-standard “Oracle” will be ignored. For the second record, no standard entry was found, so the original customer name will be imported.
Oracle requires phone numbers be parsed out into separate fields. This is very time consuming to do manually as the number formats vary by country and customers provide data in different ways.
My template automatically parses full numbers into the Oracle standard format. Here we see country and area codes being parsed and generated for the two US numbers and different parsing for the UK number.
Preparing the CSV File
Once the data is ready, the user can automatically create the necessary CSV file using the custom menu shown below. This routine performs several operations to ensure values are saved to the text file correctly and preserves the template for future re-use.
Once the data is saved as .csv, it’s a straightforward process to upload and import into Oracle Marketing.