View source for Manage Data Import

<!--Transcluded into Import Data under the heading "Working with Data Imports"-->
==About Data Import==
{{permissions|Import and Export Data|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]].<br>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.<br>''Learn more:'' [[Multi Object Lookup#Considerations|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 {{leadprospect}}s) might be managed by a sales representative. In this scenario, the sales rep would "own" the Accounts/{{leadprospect}} records.

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
:*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: <tt>accounts.csv, prospects.csv, pricebook.csv</tt>

:''Learn more:''
::*[http://en.wikipedia.org/wiki/Comma-separated_values CSV] files
::*Electronic planner vendor websites, like: [http://office.microsoft.com/en-us/outlook/default.aspx Microsoft Outlook], [http://www.act.com/ Act!] or [http://www.frontrange.com/goldmine.aspx 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<br>For example:<tt>order number, order_amount, customer name, ...</tt>

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:
:::<tt>0-9  a-z  A-Z  -  _</tt>
:*To include a comma or other special character in a column heading, put it in double quotes:
::: <tt>..., "last name, first name", "SS#", ...</tt>
:*Fields do not include any special characters, unless they are in quotes:
::: <tt>/ ? > < # $ ! . ) ( , :</tt>

{{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:
::*[http://www.infoworld.com/article/07/10/29/44FE-dirty-data_1.html Dirty Data]
::*[http://en.wikipedia.org/wiki/Data_cleansing 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|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:
#Go to '''Settings > Data Management > Import Data'''
#:
#Select the object to import into.<br>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|Mapping Profile]], which defines how the CSV data fields are aligned with the record structure in the platform
#:
#Click '''[Next]'''
#;Map Columns:For each field in your object, choose the matching column from the CSV file,<br>as explained in the section, [[#Import Guidelines|Import Guidelines]]''
#:
#Click '''[Next]'''
#Confirm that the mapping is correct, and click '''[OK]'''
#:
#;Set Attributes:Set the import attributes,<br>as explained in the section, [[#Import Guidelines|Import Guidelines]]
#:
#;Save Mapping Profile: Enter a name and description for the mapping profile, to save it for later use.
#:
#Click the '''[Start]''' button to Start the Data Import<br/>Optionally, click '''[Save]''' to save the profile and import the data at a later time

=== Mapping Profile ===
{{:Mapping Profile}}

=== View the Import Queue ===

To view the results of data import:
#Click '''Settings > Data Management > Import Queue'''
#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]]

===Example: Importing Products===
{{:Import Products}}

===Records Ignored During an Import===
{{:Records Ignored During an Import}}

==Import Guidelines==

When setting up an Import, there are two major areas to consider:
:;[[#Map Columns and Lookup Relationships|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|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. For each company ([[Account]]) you work with, you likely work with several people ([[Contacts]]) in the organization. Those connections are modeled as [[Object Relationships|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:
:<tt>order number, customer name, credit card number, order amount, source</tt>...
where the <tt>source value</tt> might be "Internet", "Catalog", or "Store".

Having specified that CSV file, the heading row makes it easy to create the mapping:
:[[File:ImportMapColumns.png|border]]

In this example:
:* The Order Number field was originally a unique autonumber field, so it is used here for the record ID.<br>''Learn more:'' [[#Using Record ID Fields|Using Record ID Fields]]

:* The standard, built-in fields <tt>Record Owner</tt>, and <tt>Owner Id</tt> 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 <tt>Record Owner</tt> field would have the first name and last name of the user separated by a space, in the format: <tt>first_name last_name</tt>.
::* The <tt>Owner ID</tt> 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_Locator#Record_Identifier_Fields|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:
:::::: <tt>fieldvalue1 - fieldvalue2 - ...</tt>

::: '''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:
:::[[File:ImportMapLookupField.png|border]]

::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 <tt>record_id</tt> is not one of the mapped fields. If a CSV column is mapped to the <tt>record_id</tt> 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|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 <tt>record_id</tt>s 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 <tt>record_id</tt> 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<sup>31</sup> - 1
:* If the record ID in the imported data is empty, non-numeric, or outside the valid range, the record is ignored.<br>''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.
:* The <tt>record_id</tt> field can be mapped for all objects, with the exception of the [[Opportunity]] object.

===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. {{leadprospect}}s
::d. Opportunities
::e. Other Object(s)

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

====Accounts====
:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|Account Name
|
Address <br>
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 ====

:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
Last Name or Full Name<br>
Account<br>
|
First Name<br>
Address<br>
Phone number<br>
Email address<br>
|}

:*In the Import Attributes section, complete the following information:
::;Add Accounts for unmatched Account Names:Click this checkbox [[File:checkboxicon.gif|link=]] 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.<br>(You can also do an [[#Accounts|Accounts import]] to import such contacts.)
:* To import contacts related to Prospects, you need to do a [[#Prospects|Prospects import]].}}

<!--
==== List Members ====
{{TBD|}}

:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
<br>
|
<br>
|}

====Products ====
{{TBD|}}

:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
<br>
|
<br>
|}
-->
====Prospects ====

:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
Company Name<br>
|
Primary Contact Name<br>
Phone Number<br>
|}

:When importing Prospects, the Set Attributes screen requests a Merge Method and, optionally, to save the [[#Mapping Profile|Mapping Profile]]. If no match is found, you can choose to merge records based on [[Assignment Policy|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 [[File:checkboxicon.gif|link=]] 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====

:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
Opportunity Name<br>
Account Name, Account Number, or Prospect Name<br>
Close Date<br>
Stage<br>
Amount<br>
|
Primary Contact Name<br>
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 =====
:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields 
!Recommended Fields (optional)
|-
|
*If an [[Index]] exists for the {{Typenolink|}}, 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 [[file:checkboxicon.gif|link=]] 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 {{type|}} 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====

:{| border="1" cellpadding="5" cellspacing="0"
!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 [{{DOCHOST}}/javadocs/com/platform/api/Controller.html 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:
:{|
<pre style="overflow:auto">
<form
  method="POST"
  encType="multipart/form-data"
  action="https://{domain}/networking/sites/{tenant_id}/dataimport/multipartController/com/platform/{namespace}/{package}/{controllerClass}
>
  ...
</form></pre>
|}
where:
:* '''{domain} -''' Is the platform domain. For example: <tt>www.myPlatform.com</tt>
:* '''{tenant_id} -''' Is your Tenant ID. For example: 1234567. (This value is obtained from [[Company_Information#Basic_Information|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: <tt>ourcompany</tt>
:* '''{package} -''' Is the package in which you implemented the controller class. For example: <tt>dataimport</tt>
:* '''{controllerClass} -''' Is the name of a class that implements the [{{DOCHOST}}/javadocs/com/platform/api/Controller.html Controller] interface, with no extension. For example: <tt>MyController</tt>

==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.