Manage Data Import

Users that have the Import and Export Data permission can import data

Considerations

• If there is an error during the import, it may need to be repeated. When importing data into an empty object, you can simply delete the data and do it over. But when you are importing into an object that has existing data, it's a good idea to back up the object first, by exporting it in a Package. (Alternatively, create a Sandbox account, and do the import there.)
• Results of the import are reported in the Import Queue.
  In particular, that report contains the number of Records Ignored During an Import. Carefully examine that report to make sure the number records added and ignored match your expectations.
• To import data into the Products tab, see Import Products for more detail.
• A specific syntax is required to import data into a Multi Object Lookup field.
  Learn more: Multi Object Lookup Considerations

Preparation for Data Import

If you already use an electronic planning tool (like Outlook or Outlook for Business Contact Managers, ACT!, GoldMine, Franklin-Covey, etc.), you're halfway there. Each of these applications offer a means to export your data to a compatible file format. Generally, you can find instructions in the tool's help area when you look up the term Export.

If your business information exists in a spreadsheet or a database (like Microsoft Excel, FileMaker Pro or Microsoft Access), you will find these applications offer a similar process to export your data. Lookup the term Export in the application help section.

Data Organization

In this section, we review best practices for creating data files for import in CSV format, so that existing relationships are maintained.

In a Sales Force Automation (SFA) system, for example, information is typically organized into four key areas:

• Accounts
• Contacts
• Opportunities
• Prospects

Your goal, after an import, is to have all of the information you have imported correctly organized into those areas, with data relationships and record ownership correctly established.

Data Relationships

When importing your information, consider how the data is connected. For each company (Account) you work with, you likely work with several people (Contacts) in the organization. These Contacts may work in different offices (each with a different address / telephone / cell), but still be part of the same Account.

When that information is contained in different spreadsheets, for example, the Account name maybe spelled "ABC Co" for one contact, "ABC Co." in another, and "ABC Company" in a third.

People reading those spreadsheets know that it's the same company, but a computer processing the data doesn't. So, to correctly establish those relationships when you import the data, make sure that values are spelled the same way, when they establish relationships.

The result, after importing the data, will be a single Account record, with all information pertaining to that account, and multiple Contact records that all point to the same Account.

Data Ownership

In an organization, Accounts (and Template:Leadprospects) might be managed by a sales representative. In this scenario, the sales rep would "own" the Accounts/Template:Leadprospect records. Record owners have special privileges regarding the data they own: the ability to manage their data based on Role.

Learn more: Record Owner

In any case, it is important to consider how record ownership should be assigned.

There are several options available to assign record ownership during import:

• Include the Record Owner as a data column in the CSV import file
• Automatically assign record ownership, based on an Assignment Policy
• Merge new records with existing records in the platform

Create the CSV File

Once the data has been prepared for export, use the vendor instructions to export the data as a CSV file. (The acronym CSV stands for comma-separated values, a common file format for data exchange.)

Your system may provide a single output file, or multiple files. Give your files meaningful names. Although many spreadsheet applications have the ability to hold multiple worksheets in a single file, a better practice is to keep each export file separate: accounts.csv, prospects.csv, pricebook.csv

Learn more:

• CSV files
• Electronic planner vendor websites, like: Microsoft Outlook, Act! or GoldMine

Verify the Data File

Use a spreadsheet application to open the data file and make any changes before you import the CSV file into the platform.

CSV File format

Before proceeding, include a Heading row that follows these guidelines:

• The Heading row is the first line in the file
• The Heading row has a unique name for each column
  For example:order number, order_amount, customer name, ...

And make sure it follows the guidelines for CSV data:

• Column names are separated by commas
• Characters allowed in the column headings include the alphanumeric characters, hyphens, and underscores:

0-9 a-z A-Z - _

• To include a comma or other special character in a column heading, put it in double quotes:

..., "last name, first name", "SS#", ...

• Fields do not include any special characters, unless they are in quotes:

/ ? > < # $ ! . ) ( , :

Tip: The entries in the heading row are displayed when you map the CSV columns to Object fields. Good names will make the process easier.

Data Format

For a clean import, with no duplicate records and correct data mapping, check the formatting of your data file.

• Computers are very literal, so when you type a name differently, the system can think that you mean different people, or companies, for example:

James Smith is not the same as: Jim Smith or James J. Smith Jr.

The Morgan Co. is not the same as: Morgan Company

• Make sure your data is clean and scrubbed, see these resources for more information:

• Dirty Data
• Data Cleansing

• This is a good time to verify your data so that you are not putting bad information into a clean system:

• Look for consistency in spaces between words
• Check for leading or trailing spaces
• Check that periods are placed consistently in abbreviations (Co. Corp., Inc., St., Ste., Fl., Mt., Ave.)
• Some of the import pages are set up to map a full contact name, and others may expect separate first and last names; Include this information in your import files when required
• Verify that the account name is spelled the same way in every record, or use the Account ID as the mapping field

Required Fields

When importing data for objects that that have required fields, make sure that all of those fields have data.

Learn more: Special Import Considerations

Importing the Data

When you've verified the format of the data file(s), you're ready to begin importing them.

Use the Data Import Wizard

The Data Import Wizard makes it easier to map columns in the CSV data to fields in the Object you are importing. The first step is to upload the CSV file, so you can see the data you're working with. Then you create the mapping profile.

(An input file is required, because the data fields in the first line are displayed when you create the mapping profile. If you don't have a data file handy, create a dummy file with nothing but the Heading row.)

To use the Data Import Wizard:

1. Go to Settings > Data Management > Import Data

2. Select the object to import into.
   The next screen appears.

   Select File Containing CSV Data

   Enter the name of the CSV file to be imported, or browse to select a file.

   Select from Previously defined Mapping Profiles

   Optionally, select an existing Mapping Profile, which defines how the CSV data fields are aligned with the record structure in the platform

3. Click [Next]

   Map Columns

   For each field in your object, choose the matching column from the CSV file,
   as explained in the section, Import Guidelines

4. Click [Next]
5. Confirm that the mapping is correct, and click [OK]

   Set Attributes

   Set the import attributes,
   as explained in the section, Import Guidelines

   Save Mapping Profile

   Enter a name and description for the mapping profile, to save it for later use.

6. Click the [Start] button to Start the Data Import
   Optionally, click [Save] to save the profile and import the data at a later time

Mapping Profile

A mapping profile is used in Data Import. It tracks how to match CSV data fields with the record structure defined in the platform. It also specifies whether incoming data is appended or merged, and determines how a merge occurs.

Multiple Mapping Profiles can be created for the same object, one for each CSV file that uses a different arrangement.

Creating a Mapping Profile

1. Click > Administration > Data Management > Import Data
2. Select the object to import into
3. Select the file containing CSV Data (Required. See note below)
4. Click [Next]
5. Map Columns and Lookup Relationships
6. Click [Next]
7. Specify Import Attributes to control merging and ownership of imported records
8. Save the Mapping Profile.

View the Import Queue

To view the results of data import:

1. Click Settings > Data Management > Import Queue
2. In the Data File page, view information regarding the settings or status of your import as well as any issues that may have occurred

Learn more: Import Queue

Example: Importing Products

Import Products

Import Guidelines

When setting up an Import, there are two major areas to consider:

Map Columns

Determines how the columns in the imported data are mapped to fields in the target object. It also determines how incoming data is matched with records that already exist in the platform, establishing the Object Relationships that connect people to companies, opportunities to products and pricebooks, and much more.

Import Attributes

These options cover data merging and record ownership.

Map Columns and Establish Relationships

When importing your information, consider how the data is connected. For each company (Account) you work with, you likely work with several people (Contacts) in the organization. Those connections are modeled as relationships between the Account and Contact objects.

Similarly, suppose you are importing Orders from a spreadsheet, and that the Heading row in the CSV file looks like this:

order number, customer name, credit card number,...

You can use that CSV file to create the mapping:

In this example:

• The standard, built-in fields Record Id, Record Owner, and Owner Id have no corresponding entries in the CSV file, so they are left blank. (Had the data file been created by exporting data from this instance of the platform, those fields could well have values in the file.)

• The Credit Card Number selection shows the data in the first line of the input file ("credit card number"), along with the column it is in (column #3).

• The Related to Customer field is a Lookup field that goes to the Customers object. The data in this case comes from column #2. Since it is a Lookup field, the platform knows that the imported data could be one of two kinds:

• An actual record ID from the Customer object - In that case, you would choose the Match by Record Id option for the Lookup relationship.
• A Record Identifier field - In that case, the field value will be used to look up the record. (To do so, the Record Identifier must have been defined for the object.)

Import Attributes

The data merge option dictates whether an imported record is blended with an existing record in the platform, or is used to create a new record. The ownership option determines the default owner for records that do not have a designated owner.

Merge Option: Do Not Create Duplicate Record

When this box is checked, the Primary Key field defined for the record is used to merge existing data.

Considerations

• This option only appears when record_id is not one of the mapped fields. If a CSV column is mapped to the record_id field, that value is always used for merging.
• While it is optional, merging is highly recommended as it tends to greatly reduce the number of duplicate records created during an import.
• When merging new information into an existing database, the system must find a way to match your data with the existing information.
• When you use a unique field like an Account Number or Social Security number, there is no question about which record matches, so the merge process goes smoothly.
• When you use a text field like Account Name in a merge, you can accidentally create duplicate records if the account names vary slightly in different records. See Data Format for more information.

Ownership Option: Owner of Imported Records

By default, you will own all records that are imported. Optionally, you can select a default Record Owner for all records that do not specify an owner.

Considerations

• This field is used when:

a. The Record Owner field is not mapped, or

b. The field is mapped, but the field is empty.

Special Import Considerations

Best Practices

• When you prepare your data, confirm that the Required fields are available.
• Include the Recommended Fields in your data file to streamline the import process to minimize the potential for creating duplicate or invalid records.
• For Sales Force Automation applications, import your data in this order:

a. Accounts

b. Contacts

c. Template:Leadprospects

d. Opportunities

e. Other Object(s)

Note: Accounts and Contacts are merged differently than Opportunities or Prospects. Pay close attention to the differences.

Using Record ID Fields

It is generally advantageous to include the record_id field in the Mapping Profile when:

a. You are importing records that were previously exported from the same instance of the platform, in order to massage the data in some way.

b. You are importing records from another instance of the platform into an empty object. Keeping the record IDs intact ensures that lookups from other records you import will point to the right place. (Make sure the object is empty, for this purpose. If it isn't, completely different records could be combined into one, because they happen to have the same ID.)

c. You are importing records from a completely different system (for example, an Excel spreadsheet), in which numeric index fields already exist, and are referenced by other records. Those numeric indexes can become record_ids for the new records, to establish Lookup relationships from the other records. (Once again, the object you are importing into should be empty, in this circumstance.)

Considerations

• When mapping the record_id field, make sure that record IDs in the imported data are an exact replica of record IDs in the platform. Otherwise, only import into an empty object.
• Record IDs you create must fall in the range 1001 to 2³¹ - 1
• If the record ID in the imported data is empty, non-numeric, or outside the valid range, the record is ignored. (Learn more: Records Ignored During an Import)
• If the record ID matches a record in the recycle bin, the merge still occurs, but the new data goes into the deleted record. If recovered later, the record will include the merged data.

Accounts

Include these Required Fields	Recommended Fields (optional)
Account Name	Address Phone Number

• In the Import Attributes section, complete the following information:

Merge the new information with an existing Account record

Choose from:

• Do not merge
• Merge using Account Number
• Match using Account Name Only
• Match using Account name and Street Address

Note: You can also import "Primary Contact" information, using an Accounts import. If multiple contacts are imported, only the first is "primary".)

Contacts

Include these Required Fields	Recommended Fields (optional)
Last Name or Full Name Account	First Name Address Phone number Email address

• In the Import Attributes section, complete the following information:

Add Accounts for unmatched Account Names

Click this checkbox icon to create a new Account record if no matching record is found in the platform

Note: A Contacts import can only be used to import contacts related to Accounts. (You can also do an Accounts import to import such contacts.) To import contacts related to Prospects, you need to do a Prospects import.

List Members

__TBD__

Include these Required Fields	Recommended Fields (optional)

Products

__TBD__

Include these Required Fields	Recommended Fields (optional)

Prospects

Include these Required Fields	Recommended Fields (optional)
Company Name	Primary Contact Name Phone Number

When importing Prospects, the Set Attributes screen requests a Merge Method and, optionally, to save the Mapping Profile. If no match is found, you can choose to merge records based on Assignment Policies.

Note: You can also import "Primary Contact" information, using a Prospects import. (In fact, this is the only way to import contacts related to Prospects. If multiple contacts are imported, only the first is "primary".)

When Prospect attributes are specified, the following information is requested:

• In the Specify Prospects Creation Policies section, complete the following information:

Prospect List Name

Name of the prospect list

Prospects Lead Information Provider

• If a Lead Provider is selected then an additional checkbox is displayed:

Merge using the Lead Providers Unique Key

Click the checkbox icon to use the unique key in the Lead Provider's data as the basis for record merging.

• If Lead Provider is "None", this choice appears:

Merge options

• Do not match
• Match using Prospect Name and Street Address
• Match using Prospect Name

If no match is found, use Assignment Policy

This option tells the system what to do if no match is found in the existing prospect records

• Do not use Policies
• Use Policy if ownership cannot be determined using the specified matching criteria
• Always use Assignment Policy (ignores ownership specified in the import file)

Assignment policies to automatically determine lead owner

The policy to use when either of the last two choices are selected in the field above.

If no match is found assign Prospects to

Specify the owner for unmatched records.

Create/modify Contact for Lead

Check this box to create a new contact record if the contact specified in the Prospect record does not exist.

Opportunities

Include these Required Fields	Recommended Fields (optional)
Opportunity Name Account Name, Account Number, or Prospect Name Close Date Stage Amount	Primary Contact Name Source

Choose to associate the Opportunity with the import data Account Number, Account Name or Prospect name.

When importing Opportunities, it is important that the [Related To] field matches the Associated object you selected above. Choose the name of the person on your team who will own these opportunities.

Custom Objects

Required and Recommended Fields

Include these Required Fields	Recommended Fields (optional)
If an Index exists for the Custom Object, include the Index fields when mapping columns. The Index fields define a Unique Key that is used as the Primary Key when importing data	If an Index field is not used, map all fields of interest

Note: Because the Record Id field is not mapped, the DO NOT DUPLICATE option appears in the next screen. Select the checkbox icon to enable merging of fields using the Index / Primary Key.

Importing a Custom Object with One or More Unique-Key Indexes

Assume that a Custom Object has multiple unique indexes, one of which is chosen for the import. Here is what happens to imported records:

• If there are no unique index conflicts, the record is added.

• If the index selected for import is violated and the merge option is chosen, the record is merged. (Existing record data is replaced by data from the imported fields, for all fields mapped in the merge.)

• If the selected unique index is in conflict, and merge was not specified, the record is ignored. An error is recorded in the Import Queue status.

• If any of the other unique indexes is violated, the record is ignored (even if the merge option is chosen). An error is recorded in the Import Queue status.

• If the record_id is mapped, records are always merged, unless there

is a unique key conflict. In that case, the record is ignored. An error is recorded in the Import Queue status.

Records in a Master-Detail Relationship

Include these Required Fields	Recommended Fields (optional)
Lookup field (associated with the Master-Detail relationship)	(none)

To import records in a Master-Detail relationship:

• Best Practice is to import Master records first.
• Then select the Detail Object and choose the Upload File.
• The CSV file must contain the Lookup field associated with the Master-Detail relationship.
• The mapping must include the Lookup to the Master record.

Programmatic Imports

Importing from a JSP Page

To do imports programmatically, you can use the platform's Java APIs and page management features to:

• Implement a Controller class
• Create a JSP Page that uses it
• Create a JSP page to track the status of the submitted import

Learn more: Java_API:Data_Import

Importing from a Site

A form on a Site page can submit data to the import queue with the following syntax:

<form
  method="POST"
  encType="multipart/form-data"
  action="https://{domain}/networking/sites/{tenant_id}/dataimport/multipartController/com/platform/{namespace}/{package}/{controllerClass}
>
  ...
</form>

where:

• {domain} - Is the platform domain. For example: www.myPlatform.com
• {tenant_id} - Is your Tenant ID. For example: 1234567. (This value is obtained from Company Information (Basic Information section), if you have admin privileges. If not, ask your admin to get it for you.)
• {namespace} - Is your development namespace, as defined in Developer Configuration. For example: ourcompany
• {package} - Is the package in which you implemented the controller class. For example: dataimport
• {controllerClass} - Is the name of a class that implements the Controller interface, with no extension. For example: MyController

FAQs

Can I Undo an Import?

• You can walk through the steps to understand the import process, and if you quit the wizard before you click the [Start Import] button, your data will remain unaltered. You can also quit the data import wizard by clicking any other tab in the application.
• If you have already completed an import, and you decide you want to Undo the work, see Mass Delete Data

What happens when I import data into an object that uses Custom Access Criteria?

• At this time, Custom Access Criteria rules are not validated on import. This means that any data can be imported, regardless of the Custom Access Criteria rules.