The Data Import tool allows to use data from a spreadsheet to automatically create records in the system. Imports can also be used to make mass updates to existing records (Upsert)

Steps to perform to import data

1. Open a corresponding record profile in the Customize section of ASPEC Setup
2. Click Import button at the top of the record profile
3. Download the csv Import Template file
4. Fill out and save the csv Import Template file. Scroll down for more detailed information regarding the data validation.

   Make sure that the file is saved as csv and not any other format (.xlsm, .xlsx etc)

5. Upload the file by clicking “Choose file”
6. Click Validate. The results of file validation are displayed in a table “Import File Errors below this button.
7. Correct the values in csv Import Template file if needed, then upload the file and run validation until the “Import File Errors” table is empty and the Validation Results displays “File Validation Passed”.
8. If are updating existing records in ASPEC, deselect checkbox “Do not override if column is empty ” to clear values in ASPEC records. If the checkbox is selected, the import will ignore empty cell and will not clear corresponding fields in ASPEC.
9. The import will display the number of imported records when the import is completed.

Some tips and tricks

1. Do not rename or reposition columns in the Import Template.
2. Each record in ASPEC has its (Universal Unique Identifier (UUID). These values are automatically generated when saving records in the ASPEC interface or when importing them.

To import NEW records, leave the UUID column of that record type empty. For example, when importing IBOs, leave IBO UUID column empty and when importing Contacts leave

Contact UUID column empty.

See the next section of more information regarding linking records using UUID column.

1. Field values must follow the validation rules set in ASPEC.

• Required fields cannot be left blank
• Lookup values must match exactly the values entered in ASPEC setup. For example, Country must match the values configured in the Countries setup, Custom Fields Lookup values must match lists configured in the Lookups section.
• Import File Delimiter, Number and Date must match the format configured in the user profile of the person who will be performing the data import.

Linking objects during Imports

Some records in ASPEC are linked. For example, Contacts are linked to Account, Leads and IBOs are linked to Accounts and Contacts.

Below is the list of UUID used in ASPEC imports.

Note: the records are created or updated if the required fields are filled out in the import file and pass the validation.

Account Imports

Contact Imports

Note: In the imports below, empty Account UUID or Primary Contact UUID indicates the need to create a new Account or Contact. A new Account or Contact is created if the required fields are filled out in the import file and the corresponding UUID is left empty.

Filled out Account UUID and Contact UUID are used only for linking Leads, IBOs, Interactions to Accounts or Contacts. They are not used to override Account or Contact fields with values provided in Lead, IBO or Interactions import files.

Use Account and Contacts imports to update Accounts and Contacts.

Lead Imports

IBO Imports

[:en]

Overview

Import tool is available on Account, Contact, Opportunity (IBO) , Interaction and other form setups.

Administrators can use Import to perform one if the two following mass actions:

1. import new records
2. update existing records (UPSERT)

The first column of all import templates contains UUID (a special unique key) of the record in ASPEC.

If this value is left empty, then the Import creates a new record.

If the UUID cell contains a valid value, then the corresponding record will be updated using the column values from the row in the import file.  UUID values can be exported from ASPEC prior to performing UPSERT.  UUID is the first available value in every Export Template.

If any row of the import file fails the validation, the results are displayed in the Import page and none of the rows are imported.  The whole file must pass the validation in order to create new records.

Import page

Open the Setup section and find the the profile associated with the records type you wish to import.   Click Import button at the top of the profile.  That button opens an Import page.

Fig 1: Button to open the Import tool on Opportunity (IBO) form

The top of the Import page contains the following buttons:

Fig 2: Controls for uploading and validating the import file

1. Download Import Template: this is the button to downlaod the csv Import Template file.
   Fill out the file and save it to your local drive. Ensure it is saved as a csv file.
   Do not rename or reposition columns in the Import Template.  If you are not sure about the values, scroll down the Import page for more information regarding the format of the fields and the allowed values for dropdown fields, as displayed in Fig. 4 on this page.
2. Cancel: click this button to cancel the import
3. Import File: Click Choose file to upload the filled out Import Template.   Make sure that the file is saved as csv and not any other format (.xlsm, .xlsx etc.). Import File Delimiter, Number and Date must match the format configured in the user profile of the person who will be performing the data import.
4. Ignore required validation: This option applies to required fields when importing NEW records.  More on this option in the next section on this page.   
5. Validate: clicking this button compares the data in the file with the format required by ASPEC ( spelling, required fields, number and date values etc). Any errors are displayed on the Import page.
6. Validate and Import: clicking this button compares the data in the file with the format required by ASPEC ( spelling, required fields, number and date values etc).  If all of the data passes the validation, it is imported into ASPEC. The Import page will display the number of imported records when the import is completed.

Clicking Validate or Validate and Import displays a validation feedback.

Validation feedback “Import File Errors” displays a table with the details regarding incorrect or missing data.

Fig 3: Incorrect data in the import file

“File Validation Passed” means that all data in the file is correct.

 Note: if you have many errors that don’t seem to make sense, check the file format. Ensure that it is a csv file.

Using the option to Ignore Required Validation

New records:

Checking the “Ignore…” option ensures that cells for all required fields are not blank and  filled out correctly.  Required fields must have a value in a correct format.  For non-required fields, the cell can be left blank, but if that cell has a value, it must be a correct value and format.

Unchecking the option allows to import empty cells,as it skips checking if the field is required.  However, if the cell value is not blank, it must have a correct value and format.

Existing records:

Checking and unchecking the option has the same effect. Cells can be left blank, for both mandatory or non-mandatory field.  Validation skips empty cell values and does not override existing fields in existing ASPEC records.

To summarize, if the cell is filled out, the imports never accepts a value that inconsistent in value or format.  For new records, the Administrator can choose to allow for empty values for required fields, by checking the “Ignore..” option.

Take a look at the three examples below.

In each example let’s assume that a Country dropdown is configured with lookup values “Canada”, “France”, “Germany” and “United States”. It means that users can select one of these four values in the Country dropdown.

Example 1:

If the import file contains an invalid value – e.g. “Spain”. The validation always fails,  for both new and existing records, Country mandatory or not mandatory, and regardless if the “Ignore.. ”  option is checked or unchecked.  “Spain” value does not make sense, unless the value is added to the field configuration prior to the import.

Example 2:

The import file contains value “United States” in the Country column. The validation passes (with the option “Ignore.. ” checked or unchecked), because “United States” is a valid value.

If importing a new record, the result is “United States” in the Country field.

If UPSERTing, the result is “United States” in the Country field, replacing any previous value.

Example 3:

The import file contains an empty value “” in the Country column. The validation depends on the action and mandatory/non-mandatory setup of the field.

If importing a new record, the validation requires that Country if filled out. If passes only if the cell contains a valid value.

If the option to “Ignore..” is checked, the import will create a new records with empty Country field. If If the option to “Ignore..” is not checked, the import will reject this line from the import file.

If UPSERTing, the validating passes,as it does not check against mandatory flag for records that aleady exist in ASPEC.  The result is an unchanged value in the the Country field of the existing record.

Checking the required fields and field format

The bottom of the page lists fields available for the import.

Fig 4: Data description

Required fields are denoted by a red bar, placed to the right of the column header. For example, IBO Status is required.

For Roles field, include the full Role path . Administrator/Europe/France is an example of a role 3 levels deep.

Lookup fields have the allowable values listed in brackets. For example, IBO Status and IBO Products 1 Area are lookup fields. Lookup values must match exactly the values entered in ASPEC setup. For example, Country must match the values configured in the Countries setup, Custom Fields Lookup values must match lists configured in the Lookups section.

Date and numeric fields display the field type in brackets. For example, IBO Closed Date is a date field.

Numeric and date fields must be in a format that matches the User’s Locale Settings and the format of the field.  Note that csv file is a text file, where formatting is not automatically performed as in the MS Excel.  To check the format of values in the import file. open it with  a Notepad/Notepad++ or any other editor that does not format automatically.

Ensure that the date matches the format defined in User Profile and that numbers follow the following rules:

1. for Integer, the format must match User Profile, but the number of the decimal digits must be 0.  An example of Integer field is Quantity (Products) or any custom field set to be an integer number
2. for Decimal, the format must match User Profile, but the number of the decimal digits must be 2.  An example of Decimal field is Unit Price (Products) or any custom field set to be a decimal number

UUID ((Universal Unique Identifier), like Source Event UUID in the image above, requires a special unique value for the linked record.  UUID means a special key, attributed to a record. Each record in ASPEC has its UUID. These values are automatically generated when saving records in the ASPEC interface or when importing them.  This key can be obtained by clicking the Information button of the record, located in the top right corner of the record. Copy and paste that UUID into the import template.  Please also see Linking Object in Import Template by scrolling down this page.

Fig 5: Information button

Linking objects during Imports

Some records in ASPEC are linked. For example, Contacts are linked to Account, Leads and IBOs are linked to Accounts and Contacts.

Below is the list of UUID used in ASPEC imports.

Note: the records are created or updated if the required fields are filled out in the import file and pass the validation.

Account Imports

Contact Imports

Note: In the imports below, empty Account UUID or Primary Contact UUID indicates the need to create a new Account or Contact. A new Account or Contact is created if the required fields are filled out in the import file and the corresponding UUID is left empty.

Filled out Account UUID and Contact UUID are used only for linking Leads, IBOs, Interactions to Accounts or Contacts. They are not used to override Account or Contact fields with values provided in Lead, IBO or Interactions import files.

Use Account and Contacts imports to update Accounts and Contacts.

Lead Imports

IBO Imports

** if the Marketing module is used, Source Event UUID is also available

Interactions Imports

Note: Read more on Interaction Types here.

Import available if using Service module:

Service Request Imports:

Service Order Imports:

The Data Import tool allows to use data from a spreadsheet to automatically create records in the system. Imports can also be used to make mass updates to existing records (Upsert).