Apsona for Salesforce supports data import (also called uploading) into all Salesforce objects, both native and custom. The import
facility requires data records in comma-separated (CSV) or tab-separated (TSV) format,
with a header row specifying column names. You can use it to:
- upload new records
- “upsert” records, i.e., add new
records while updating existing records
- update selected fields of existing records matched
by your matching conditions.
Apsona's import facility includes several commonly-needed features:
- Column name mapping:
The column names in the header row need not be in any particular order, nor do they need to correspond to Salesforce field names. You can map the columns to fields
during the import process. You can also de-select (i.e., omit) any fields you wish to exclude from the import. This gives you the most flexibility with the
format of the input CSV data.
- Duplicate checking: It can detect duplicate records, both within the CSV data and between the CSV data and
the Salesforce database. You can choose the fields you wish to use for duplicate
- Data validation: It validates your data against your Salesforce object fields. It ensures, for example, that dates, numbers, pick lists and e-mail addresses conform to valid values.
- Interactive error correction: You can quickly fix data errors right in your browser, before the data is ever submitted for import. This makes the
entire import process much smoother and more reliable.
- Handling lookup fields: When importing into lookup fields (e.g., the Account ID field of a Contact), you can specify either the record ID or the
name of the lookup, or any external ID, unique, or auto-number field. The importer will look up the related record automatically. For example, when importing
contact records, you can specify either the account name or the account ID, and the importer will look it up automatically before importing. This facility is
available for all fields in all objects, both native and custom.
- Echo of rejected records: You can optionally choose to have the importer reject all erroneous records and return them as a separate CSV file. This
file contains an additional column with detailed information about the errors, so that you can correct them and re-import just the erroneous data.
- Automatic record linking - no more "vlookup": After importing an object's records, Apsona's importer provides an “echo-back” of your
data records along with the record ID values for the newly-added records. Using this feature, coupled with the duplicate elimination, you can import into
multiple related objects using a single CSV data set, as we will describe below. For example, you can use a single CSV data file to import all your leads and
add them into a campaign. Or you can use create Account and related Contact records from one CSV file. This eliminates a key pain point associated with data
imports. With many other data loaders, you need to import into one object, export the results, extract the record IDs, link them to the records of the second
object, and then import the second object's records. With Apsona's automatic lookup, this is not needed. You can get more information about this feature, along
with an example of how to use it, in this page.
A tour of the import wizard
Apsona's import wizard is available for all objects. It can be accessed via the Tools - Import/update menu item.
The import wizard provides three steps:
- Provide CSV data and set up CSV options;
- Set up the CSV columns to data fields and correct errors if needed;
- Import the data and save the results.
It is important to emphasize that the importer looks the same for all objects, even custom objects. The present description uses
the Contact object for illustrative purposes only.
Step 1: Provide CSV data
In this first step, you provide the CSV data for import and specify a few options on how to handle the data.
You can either provide the CSV data from a CSV file, or paste it into the text box, as indicated by the radio buttons in the screen shot above.
In addition, you can tell the importer what to do with the data, via the "Action" option: either import new records or update existing records.
Importing new records
- Here you can specify how to detect duplicates, and what
to do with them. In this context, we say that two records
are duplicates of each other if their values match on a specific set of match fields. You can choose the match fields from the "Match
fields" dropdown. For example, when importing Contacts, you might specify the Phone, First name, and Last name as the match fields. This
means that if two Contact records match in their values of these three fields, they are deemed duplicates. Matching is case-independent,
and the matching check is carried out on the data within your CSV file as well as between the CSV file and the database.
- The "Match fields" dropdown lets you choose the set of fields that the importer will use for detecting duplicates. The available
field sets varies depending on the object into which you are importing. For example, the Contact object includes sets of fields such as "Email" +
"First name" + "Last name" and "Account name" + "First name" + "Last name", while the Account object includes "Name" + "Phone" and "Name" +
- The "Duplicates" dropdown lets you choose how duplicates are handled: either
reject the duplicate record, update the database, or simply add the record to the database, ignoring the duplicate.
- If you choose "Reject", then any records in the CSV data that are duplicates of records in the database (i.e., that match on the
match fields, as described above) will be rejected in Step 2.
- If you choose "Update" in the dropdown, you are doing what is commonly called an "upsert" – if a record in the CSV data matches one in the
database, the former will be used to update the latter. This means that any fields you have specified in the CSV data record will overwrite the
corresponding fields in the database
record. Note that if there is more than one CSV data record that matches, the last one in the CSV data prevails.
- If you choose "Add", then there is no duplicate checking: all your CSV data will be added into the database.
Updating existing records
To update existing records, you will (naturally) need to specify
The value in the "Lookup by" dropdown specifies the criteria that Apsona should use for finding the records to be updated. For example:
- the records to be updated, and
- the fields to be updated and their values for those records.
In each case, Apsona's importer will flag an error if there is no database record matching the data in the lookup fields you specify.
- You can choose the "Contact ID" option, and include the contact ID column in your CSV data. In this case, each record in your CSV data will be used to
update the database record with the matching contact id value.
- Similarly, if you choose "Business Phone, First Name, Last Name", you must include those three fields in your CSV data. In this case, each record in
your CSV data will be used to update the database record that contains the same business phone, first name and last name.
The main difference between the "upsert" case (with importing new records, above) and "update" is that when updating, you need only specify the lookup fields
and the fields you wish to update. For instance, if you want to update the phone numbers of a bunch of contacts, you can provide just two columns in your CSV
data: the Contact ID (for lookup) and the Phone number column (for update). But in the "upsert" case, you must specify all the mandatory fields in the object
to which you are importing. Also, in the update case, if a lookup fails, the record will be rejected, but in the upsert case, the record will be added to the database.
Customizing the match fields
- Apsona shows a predefined list of match field sets in the "Match fields" and "Lookup by" dropdowns, enabling you to select a set of fields that will act as
match key. For example, when importing contacts, if you select the match field set
Email, First Name, Last Name (shown in the above screen shots), Apsona
will treat two records as identical whenever they match on those three fields. This enables you to identify duplicates based on those fields. If a field is
marked in Salesforce as an External ID or as unique, Apsona will include that field in the list of match field sets.
Sometimes, particularly when importing into custom objects, the field sets that Apsona provides will not be enough, and you need to create your own match
field set for matching during the import or update. To serve this need, Apsona includes a "Create new..." option in the dropdown, as in the screen shots above. If
you select that option, a popup window appears, from which you can select one or more fields that you wish to act as a temporary match field set.
The fields you select will appear as an option in the match field selector. You can then choose the newly-created option and proceed with the import process.
Matching unique or external ID fields
A common need is to be able to link imported records with parent records in Salesforce. For example, you might want to import Contact records which you wish to
link to existing Account records. To help with such cross-object matching, the match field set is allowed to include the Record ID field, the display field, or
any field in the looked-up-to object that is marked as unique. For example, again consider Contacts linking to existing Accounts.
Since the Contact object has a lookup to the Account object, you can add to the match field set either the Account ID (which is the record ID), the Account Name (which is
the display field), or any fields marked as unique in the Account object. In the screen shot above, we see that there are four candidate fields,
Account: Account Name,
Account: ACT ID and
Account: External ID (SAP) available. This is because this example org
includes two unique ID fields named
ACT ID and
External ID (SAP) on the Account object, so they can both be used in a match field set, in
addition to the Account ID and the Account Name.
Step 2: Set up columns
In this step, you match the columns in your CSV data with the fields in the Salesforce object to which you are importing.
Here, Apsona displays your data in grid form, with dropdowns at the top of each column.
- Each dropdown contains the fields of the Salesforce object. For each column, you can either choose one of the fields, or choose "Do not import" to omit the
- If your CSV data contains headers that match the Salesforce field names, Apsona selects the matching fields automatically.
- If you click a cell in a column
that has been matched to a field, the cell becomes editable, and you can change its contents in-place (see screen shot at right). The editor shown
depends on the the matched field: for example, if you have matched the column to a picklist field, the editor shows a dropdown containing the valid values for
that picklist. This provides a quick way to correct errors.
- When you click "Next" after matching the columns to fields, the importer validates all the data against the corresponding Salesforce object fields, and if
any errors are found, it displays a popup containing the list of errors. You can optionally choose to ignore the erroneous records and proceed to the next step,
in which case those records will appear as rejected in Step 3.
Step 3: Finish and save results
Note that the actual saving of data into your Salesforce database begins only after you click the "Import" button in this step. Until then, no data is actually
saved into your Salesforce database. So you can click the "Download match results" link to obtain the results of matching your CSV data against the Salesforce database.
When you click the "Import" button, a progress bar appears, and the import process begins.
Downloading or viewing import results
When the import is complete, the importer displays a panel
containing the results of the import. From there, you can:
- download the data records imported, along with their record ID values, by selecting the "all records" radio button and then clicking Download
- download the rejected records, if any (so that you can correct the errors and import them), by selecting the "Rejected records" radio button and then clicking
- click the "Go to list" link to take you to the console view showing just the records you imported. You can then save the result as a filter, or perhaps - if
you just imported Contact or Lead records - add them all to a Campaign as described in this article.
Notes about data types and validation
Step 2 of the import wizard includes data validation, as noted above.
- Date values can be in one of several formats. If the data is in
mm/dd/yyyy format, there can be ambiguity between English and American
dates (e.g., the date 11/10/2012 means Nov 10th in the US, and 11th October in the UK). In such a situation, the choice of date format in step 1 (see screen
shot above) is used to disambiguate the date value. But if your date is in
yyyy-mm-dd format, there is no ambiguity. Both formats are acceptable, even within
the same CSV file.
- Multi-select picklist values must be separated by semicolons.
- With picklist values, Usually Apsona requires that a data value in a column mapped to a picklist field (or multi-select picklist field) must match a
valid value for that picklist. Sometimes it might be necessary to allow invalid values, and in such a situation, you can check the "Allow invalid picklist
values" checkbox in step 1 of the import wizard.
- When importing into a Record Type field, you must uncheck the "Allow invalid picklist" values checkbox. This is because, when mapping
picklist values, Apsona takes the value you specify and maps it to the corresponding Salesforce-internal picklist value, which is usually (but not always) the
same. And Apsona treats the RecordType as a picklist, not as a lookup, for historical reasons. When you check the box that says "allow invalid picklist values,"
you're basically telling it to trust you with whatever you put in there, and not bother to map it - in particular, not replace the Record Type name with the
corresponding Record Type ID. And that in turn causes a Salesforce error saying "Record Type ID value of incorrect type", because Salesforce finds the
name of a Record Type where it is expecting the ID of a Record Type.
Matching CSV records
One common situation is where you would want to match a set of data records in CSV format against your Salesforce data, and obtain the record ID for each record,
if one is available. For example, you might have available a list of contact names and email addresses in CSV format, which you obtained from a trade show, and
you wish to match the list against your Salesforce database, obtain their record IDs, and add them to a particular Campaign. The import tool allows you to do
this. You can take advantage of the fact that the import tool does the matching of CSV data records against Salesforce data in steps 1 and 2, but does not
actually import the records until you click the Import button in step 3. So, to find your matching record IDs, simply walk through the first two steps of the
import wizard, as described above, using the "upsert" option for importing (add new records, updating existing records). When you get to step 3, click the
"Download match results" link at the top of the panel. The resulting download will contain your original CSV data, with an additional first (leftmost) column
containing the record IDs of the matched records.