Difference between revisions of "Manage Data Import"

From AgileApps Support Wiki
imported>Aeric
Β 
(108 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<!--Transcluded into Import Data under the heading "Working with Data Imports"-->
<!--Transcluded into Import Data under the heading "Working with Data Imports"-->
==About Data Import==
{{permissions|Import and Export Data|import data}}
{{permissions|Import and Export Data|import data}}


;Considerations:
;Considerations:
:* To import data into the [[Products]] tab, see [[Import Products]] for more detail.
:* 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.
:* 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]]
:* 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]]


Line 14: Line 16:


In this section, we review best practices for creating data files for import in CSV format, so that existing relationships are maintained.
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.
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.
Line 25: Line 21:
=== Data Relationships ===
=== 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 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. Β 
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. Β 
Line 35: Line 31:
=== Data Ownership ===
=== 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 regarding the data they own: the ability to manage their data based on [[Role]].
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]]''
:''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:
There are several options available to assign record ownership during import:
:*Include the Record Owner as a data column in the CSV import file
:*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
:*Merge new records with existing records in the platform


Line 49: Line 41:
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.)
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>
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, contacts.csv</tt>


:''Learn more:''
:''Learn more:''
Line 57: Line 49:
=== Verify the Data File===
=== 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.
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====
====CSV File format====
Line 78: Line 71:


For a clean import, with no duplicate records and correct data mapping, check the formatting of your data file. Β 
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:
:*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.''
::''James Smith'' is not the same as: ''Jim Smith'' or ''James J. Smith Jr.''
::''The Morgan Co.'' is not the same as:Β  ''Morgan Company''
::''The Morgan Co.'' is not the same as:Β  ''Morgan Company''
:*Make sure your data is clean and scrubbed, see these resources for more information:
:*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://www.infoworld.com/article/07/10/29/44FE-dirty-data_1.html Dirty Data]
::*[http://en.wikipedia.org/wiki/Data_cleansing Data Cleansing]
::*[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:
:*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
::*Look for consistency in spaces between words
Line 90: Line 88:
::*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
::*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
::*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 ====
==== Required Fields ====
Line 105: Line 104:


To use the Data Import Wizard:
To use the Data Import Wizard:
#Click the '''Settings > Data Management > Import Data'''
#Go to '''[[File:GearIcon.png]] > Administration > Data Management > Import Data'''
#:
#Select the object to import into.<br>The next screen appears.
#:
#:
#Enter the name of the CSV file to be imported
#;Select File Containing CSV Data: Enter the name of the CSV file to be imported, or browse to select a file.
#:
#:
#Upload the CSV 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
#;Select File containing CSV data:In this example, ''contacts.csv'' is selected
#;Select from Previously defined Mappings Profiles:Optionally, select the [[Mapping Profile]], which remembers how to align your data fields with the record structure already in the platform
#:
#:
#Click '''[Next]'''
#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]]''
#;Map Columns:For each field in your object, choose the matching column from the CSV file, as explained in the [[#Import Guidelines|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.
#:
#:
#Click '''[Next]'''
#Click '''[Next]'''
#Confirm that the mapping is correct, and 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]]
#;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.
#;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
#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


=== Save a Mapping Profile for Later Use ===
=== Mapping Profile ===
{{:Mapping Profile}}
{{:Mapping Profile}}


Line 131: Line 132:


To view the results of data import:
To view the results of data import:
#Click '''Settings > Data Management > Import Queue'''
#Click '''[[File:GearIcon.png]] > Administration > 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
#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]]''
When examining an Import Queue entry, it is also possible to view the Import Log.


===Example: Importing Products===
:''Learn more:''
{{:Import Products}}
:* [[Import Queue]]
:* [[Import Log]]
Β 
===Records Ignored During an Import===
{{:Records Ignored During an Import}}


==Import Guidelines==
==Import Guidelines==


When setting up an Import, there are two major areas to consider:
When setting up an Import, there are two major areas to consider:
:;[[#Map Columns|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.
:;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.


:;[[#Set Attributes|Set Attributes]]: These are the Data Merge and record ownership options. They dictate whether an imported record is blended with an existing record in the platform, or is used to create a new record. They also determine default owners for records that do not designate who owns them.
:;Import Attributes: These options cover data merging and record ownership.


===Map Columns and Establish Relationships ===
===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.
When importing your information, consider how the data is connected. Those connections are modeled as [[Object Relationships|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:
====A Sample Mapping, with Lookups====
:<tt>order number, customer name, credit card number,</tt>...
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".


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


In this example:
In this example:
:* The standard, built-in fields <tt>Record Id</tt>, <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 fields could well have values in the file.)
:* 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 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 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 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:
:* 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).
::* '''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.)


===Set Attributes ===
:* 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.


;Merge the new information with an existing record using Primary Key:
::: '''A Record Identifier''', where:
When this box is checked, the primary key field defined for the record is used to merge existing data. Β 
::::* 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>


==== Data Merge Attributes ====
::: '''A ''foreign key'' (FK)'''
;Considerations:
:::: A value that matches a key in the Customer object.
:*While you can opt not to merge, merging 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 Attributes ====
::You specify the kind of data the column contains using the dropdown below the field:
:::[[File:ImportMapLookupField.png|border]]


You can select a default owner for all records added to the database that do not include an owner field of their own. By default, you will own all records that are imported.
::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.


Optionally, select the name of the [[Record Owner]].
====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.)


;Considerations:
===Import Attributes ===
:* This field is used when:
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.
:: a. The Record Owner field is not mapped, or
:: b. There are imported records that do not specify the record owner.
:* If the field is blank, such records are assigned to the user performing the import.


==== Save Mapping Profile ====
====Merge Option: Do Not Create Duplicate Record====
Give the mapping profile a name and description so you can reuse it later. (In most cases, its a good idea to do so.)
:When this box is checked, the [[Primary Key]] field defined for the record is used to merge existing data. Β 


===Importing from a Site===
:;Considerations:
A form on a [[Site]] page can submit data to the import queue with the following syntax:
::*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.
<pre style="overflow:auto">
::*When merging new information into an existing database, the system must find a way to match your data with the existing information.
<form
::*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.
Β  method="POST"
::*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.
Β  encType="multipart/form-data"
Β  action="http://{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>


===Special Import Considerations===
====Ownership Option: Owner of Imported Records====
'''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.
: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. Β 


====Accounts====
:;Considerations:
:{| border="1" cellpadding="5" cellspacing="0"
::* This field is used when:
!Include these Required Fields
::: a. The Record Owner field is not mapped, or
!Recommended Fields (optional)
::: b. The field is mapped, but the field is empty.
|-
|Account Name
|
Address <br>
Phone Number
|}


:*In the Import Attributes section, complete the following information:
===Using Record ID Fields===
::;Merge the new information with an existing '''Account record''':Choose from:
It is generally advantageous to include the record_id field in the [[Mapping Profile]] when:
:::*Do not merge
: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.
:::*Merge using Account Number
:
:::*Match using Account Name Only
: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.)
:::*Match using Account name and Street Address
:
: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.)


{{Note|You can also import "Primary Contact" information, using an Accounts import. If multiple contacts are imported, only the first is "primary".)}}
;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.


====Contacts ====
===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 ====
:{| border="1" cellpadding="5" cellspacing="0"
:{| border="1" cellpadding="5" cellspacing="0"
!Include these Required Fields Β 
!Include these Required Fields Β 
Line 250: Line 243:
|-
|-
|
|
Last Name or Full Name<br>
*If an [[Index]] exists for the {{Typenolink|}}, include the Index fields when mapping columns.
Account<br>
:*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
First Name<br>
Address<br>
Phone number<br>
Email address<br>
|}
|}


:*In the Import Attributes section, complete the following information:
{{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'''.}}
::;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 ====
==== 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:


:{| border="1" cellpadding="5" cellspacing="0"
:* If there are no unique index conflicts, the record is added.
!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]]. If no match is found, you can choose to merge records based on [[Assignment Policy|Assignment Policies]].
:* 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.)


{{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".)}}
:* 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.


:When Prospect attributes are specified, the following information is requested:
:* If the record_id is mapped, records are always merged, ''unless'' there
:*In the Specify Prospects Creation Policies section, complete the following information:
is a unique key conflict. In that case, the record is ignored. An error is recorded in the [[Import Queue]] status.
::;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.
Β 
===={{Typenolink|}}s====
:{| 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'''.}}


====Records in a Master-Detail Relationship====
====Records in a Master-Detail Relationship====
Line 369: Line 270:
!Recommended Fields (optional)
!Recommended Fields (optional)
|-
|-
|[[Lookup]] field (associated with the [[Master-Detail relationship]])||
|[[Lookup]] field (associated with the [[Master-Detail relationship]]) || (none)
|}
|}
Β  Β 
Β  Β 
Line 377: Line 278:
:*The CSV file must contain the Lookup field associated with the Master-Detail relationship.
:*The CSV file must contain the Lookup field associated with the Master-Detail relationship.
:*The mapping must include the [[Lookup]] to the Master record.
:*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==
==FAQs==
'''Can I Undo an Import? '''
'''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.


* 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]]
* 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.

Latest revision as of 10:17, 17 June 2022

About Data Import

Lock-tiny.gif

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:

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.

Notepad.png

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:
/ ? > < # $ ! . ) ( , :

Thumbsup.gif

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:
  • 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 GearIcon.png > 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 GearIcon.png > 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 GearIcon.png > 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:

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 231 - 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:

ImportMapColumns.png

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:
ImportMapLookupField.png
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 231 - 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

Notepad.png

Note: Because the Record Id field is not mapped, the DO NOT DUPLICATE option appears in the next screen. Select the checkbox Checkboxicon.gif 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