How do I import records? What format does the data need to be in?
Environment:
Data Import Wizard
Resolution:
Within the Oracle B2C Service application, you can use the Data Import Wizard to add a list of contacts to your Oracle B2C Service database. The data must be in a .csv file in order to import the contact information.
Using this import tool, you can also import other record types such as answers, incidents, organizations, and more. For more information on the Data Import Wizard, refer to Answer ID 4191: What is the Data Import feature?.
NOTE: In order to access the Data Import Wizard, in the Administration tab of your profile permissions, you will need to select the 'Data Import' option. When this option is not selected, the Data Import options are not available.
The Data Import Wizard helps you map your .csv file to columns in the database table you're importing into and checks the file for errors. The wizard also allows you to run external events or business rules on the imported records.
The recommended record limit for importing is 10,000. If necessary, you can break a large set of records into smaller groupings as necessary and do multiple uploads.
To use the Data Import Wizard, use the steps below. For additional information, refer to the 'Importing Data' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.
Step 1: Prepare the .csv file to upload
Create a .csv file so that each row represents one record and each column represents a specific field. You can create a file in Excel and then save it in .csv format. In most cases, the first row are column headers in the .csv file. However, you do not need headers in order to import the data with the Import Wizard.
If uploading data for menu fields, you can use the common name of the menu item within your dataset. So, if you have a field named Customer Type with the menu options, Customer, Prospect, or Partner, you can use Customer, Prospect, and Partner in the column for that field.
Before uploading the file, review the data to ensure that the data is formatted correctly and that field information is listed correctly. If the cell is blank, it will upload as a null value in the record.
Step 2: Begin the Upload Process
The Data Import Wizard is accessed from Configuration > Database > Data Import Wizard.
On the first page of the wizard, complete the fields below:
CSV File: Type the full path or click Browse to specify the .csv file to upload.
Header Location: Set whether the column headers are: in a separate file, the first line of the .csv file, or there are no column headers.
Duplicate Records: Select the option for handling duplicate records. If two or more records have the same contact ID (c_id) or the same email address (email, email_alt1, or email_alt2), the records are considered duplicates.
When duplicates are found, you can either update the database with the data from the .csv file (select Update Existing Data) or you can retain the data values from the database and not overwrite the content (select Keep Original Data).
You can flag duplicates as errors by selecting Record Error. If your staff account is set to receive notifications, the error notification is sent to your Notifications console.
Insert Into Contact List: Select from the options below to associate all uploaded contact records with an existing contact list or to create a new list. Click Browse to specify an existing list. Click New to create a new list.
Stop Import On Error: Check this box to prevent any records from being imported if an error is detected, for example, a missing or incorrectly formatted email address is found in the CSV file. If this check box is not enabled, the import will continue despite any error, and only records without errors will be imported. We recommend that you always have this box enabled so that you can be notified.
Run External Events: Select this check box if you want external events to run against the data you are uploading.
Run Workflow Rules: Select this check box if you want business rules to run against the data you are uploading.
When all of the fields on the first page are set, click Next.
Step 3: Map CSV Columns to Database Fields
On the second page of the Import Wizard, you select a template (if applicable) and map the columns to the database.
Template: If you have a template defined to map the columns in the file to fields in the database, select the appropriate template. For more information, refer to the 'Data import templates' section in online documentation for the version your site is currently on.
Column Mappings: Map the columns from your .csv file to the database fields in the table you're importing into. Within the mapping table, the first column (named Column) is the column names from your .csv file. For each value listed, click in the Field to specify the appropriate database field to map this to.
The Ignore on Update check box specifies whether data for existing records should overwrite existing values or be ignored when the data is uploaded. Enable the check box if you want the data to be added for new records, but do not want the data updated for existing records. If you selected Keep Original Contact Data for the Duplicate field on the previous page, then the Ignore on Update field has no effect.
Click Next to proceed.
The Import Wizard now runs a prescan. Your .csv file will be checked for errors such as incorrectly formatted email addresses. If errors are found, the .csv Data Error panel will be displayed; otherwise, sample output will be displayed. Checking the .csv file can take time. For large files, this could take a considerable amount of time.
Note: In order for custom fields to be available within the Import Wizard, you will need to enable the admin 'edit' privileges within each custom field, as you are essentially editing these fields when uploading the data. You may do this by accessing the custom field(s) in question for editing, and then placing a check mark within the option for admin >> edit. After this is enabled your custom fields should be available in the Import Wizard.
Step 4: Review Any Errors from the CSV File
If there is an error with the data you are trying to upload, you will see it in Prescan page. This page provides the number of good and bad records in your file along with links to view both the good and bad records.
Review the bad records to determine what you need to update in the .csv file. The last column in each row lists the error. For example, "Value ‘xxyyzz.com’ for column ‘email’ ($3) is not a valid email address”. In this example, $3 is the column number where the error occurred.
Note: When using the Import Wizard, you cannot upload more than 500 contacts for an individual organization record. If there are more than 500 contacts for an organization, you will receive a message indicating that there are "Too many contacts assigned to the same organization in the file."
Review the sample output to verify that the columns of your .csv file are mapped properly to the database fields. If not, click the Back button and return to Step 3 above or click Cancel. If the data is mapped properly, click Next.
Step 5: Proceed with the Import
When you have no errors in the .csv file (or you selected to ignore them and upload only the good records), selecting Next will begin the import process.
When the upload is complete, you will see a message indicating that the data has been uploaded successfully along with a summary of the records created (Insert) and updated.
Additional Information
If you should receive the error message "The .csv file is too large or does not exist" when attempting to upload contacts, this is most likely a result of Internet Explorer settings. Please review Answer ID 31: Oracle B2C Service Infrastructure Requirements and select the link that corresponds to the version of Oracle B2C Service you're using and follow the instructions for browser settings. You will need to restart Internet Explorer and login to the Oracle B2C Service application after making any changes to these settings. Some users have reported that adding the site to the list of "trusted sites" in Internet Explorer remedies the issue.
For information on 'duplicate criteria' message, refer to Answer 4162: Duplicate Criteria in Data Import Wizard.
