Manage Data Import

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

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

Record owners have special privileges to manage data they own, so it is important to consider how record ownership is assigned.

Learn more: Record Owner

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

• Include the Record Owner as a data column in the CSV import file
• 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, contacts.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.

Note: The data file you import should not exceed 60 MB in size.

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.

• Dates and times should be in your User Format
• Currencies, numbers, and percentages should be in the Database Format

• 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
• CSV exported from a non-German locale is a comma (,) separated files. These comma-separated files should not be imported to a German locale, as German locale uses a different delimiter(;) and the columns will not be mapped correctly to the fields in the object.

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 > Administration > 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 Import Guidelines section.

   • The choices come from the first row in the file, which should be column names.
   • The choices appear in the same order in which they appear in the file.

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

When examining an Import Queue entry, it is also possible to view the Import Log.

Learn more:

• Import Queue
• Import Log

Records Ignored During an Import

During an import, some incoming records may be ignored. (In general, you'll want to be sure that the number of ignored records reported in the Import Log matches the number you expect.)

Incoming records are ignored in the following circumstances:

• The object has a Unique Key Index, and the new record would cause a duplicate key.
• The record_id field is mapped, but it:

• is empty
• has a non-numeric value
• has a value outside the valid range of 1001 to 2³¹ - 1

When a record is ignored (rejected), an entry is added to the Import Log.

Import Guidelines

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

Mapping of Columns and Lookup Relationships

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 Lookup Relationships

When importing your information, consider how the data is connected. Those connections are modeled as relationships between the Account and Contact objects.

A Sample Mapping, with Lookups

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, order amount, source...

where the source value might be "Internet", "Catalog", or "Store".

Having specified that CSV file, the heading row makes it easy to create the mapping:

In this example:

• The Order Number field was originally a unique autonumber field, so it is used here for the record ID.
  Learn more: Using Record ID Fields

• The standard, built-in fields 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 columns could have been exported.)

• If present, the Record Owner field would have the first name and last name of the user separated by a space, in the format: first_name last_name.
• The Owner ID field, if present, would have the record ID of a User record.

• The Credit Card Number selection shows the data in the first line of the input file (in this case, the text from the header line: "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. The imported data could contain one of several kinds of values to establish the Lookup relationship:

A record ID

• The column contains a Record ID value.
• Records in the Recycle Bin are matched.

A Record Identifier, where:

• The column contains data that matches the Record Identifier defined for the object.
• Records in the Recycle Bin are not matched.
• If the Record Identifier is defined as a concatenation of several fields, then the CSV file must contain field values separated by a space, a hyphen, and another space, in this format:

fieldvalue1 - fieldvalue2 - ...

A foreign key (FK)

A value that matches a key in the Customer object.

You specify the kind of data the column contains using the dropdown below the field:

The list shows the valid matching options:

• Matching using the Record Identifier
• Matching by Record ID
• All fields that can be used for foreign key matching.

What Happens when a Lookup Fails

If the column data does not match any record in the target of the Lookup field, then the record is created, but the Lookup field is left empty. A warning message is added to the Import Log to indicate the error. (When the import completes, be sure to check the log for such messages.)

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.

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.

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.

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:

• {yourDomain} - 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