Print this page

Prepare a CSV file for import or update in Salesforce

Knowledge Article Number 000233560
Description

Here's a checklist of things to consider when you're preparing your CSV data file to import or update it into Salesforce as well as possible scenarios and troubleshooting for import failure.

Resolution

Preparing CSV Files

The first row in a CSV file lists the field names for the object that you're processing. Each subsequent row corresponds to a record in Salesforce. A record consists of a series of fields that are delimited by commas. A CSV file can contain multiple records and constitutes a "batch."

All the records in a CSV file must be for the same object. You specify this object in the job associated with the batch. All batches associated with a job must contain records for the same object.

Note the following when processing CSV files with the Bulk API:

  • The Bulk API doesn't support any delimiter except for a comma.
  • The Bulk API is optimized for processing large sets of data and has a strict format for CSV files (see Valid CSV Record Rows for formats). The easiest way to process CSV files is to enable Bulk API for Data Loader.
  • You must include all required fields when you create a record. You can optionally include any other field for the object.
  • If you're updating a record, any fields that aren't defined in the CSV file are ignored during the update.
  • Files must be in UTF-8 format.
 

Relationship Fields in a Header Row

Many objects in Salesforce are related to other objects. For example, Account is a parent of Contact. You can add a reference to a related object in a CSV file by representing the relationship in a column header.

When you're processing records in the Bulk API, you use RelationshipName.IndexedFieldName syntax in a CSV column header to describe the relationship between an object and its parent, where RelationshipName is the relationship name of the field andIndexedFieldName is the indexed field name that uniquely identifies the parent record. Use the describeSObjects() call in the SOAP-based SOAP API to get the relationshipName property value for a field.

Some objects also have relationships to themselves. For example, the Reports To field for a contact is a reference to another contact. If you're inserting a contact, you could use a ReportsTo.Email column header to indicate that you're using a contact's Email field to uniquely identify the Reports To field for a contact. The ReportsTo portion of the column header is the relationshipName property value for the Reports To field. The following CSV file uses a relationship:
 

1FirstName,LastName,ReportsTo.Email
2Tom,Jones,buyer@salesforcesample.com


Note:

  • You can use a child-to-parent relationship, but you can't use a parent-to-child relationship.
  • You can use a child-to-parent relationship, but you can't extend it to use a child-to-parent-grandparent relationship.
  • You can only use indexed fields on the parent object. A custom field is indexed if its External ID field is selected. A standard field is indexed if its idLookup property is set to true. See the Field Properties column in the field table for each standard object.
 

Relationship Fields for Custom Objects

Custom objects use custom fields to track relationships between objects. Use the relationship name, which ends in __r(underscore-underscore-r), to represent a relationship between two custom objects. You can add a reference to a related object by representing the relationship in a column header.

If the child object has a custom field with an API Name of Mother_Of_Child__c that points to a parent custom object and the parent object has a field with an API Name of External_ID__c, use the column header Mother_Of_Child__r.External_ID__cto indicate that you're using the parent object's External ID field to uniquely identify the Mother Of Child field. To use a relationship name in a column header, replace the __c in the child object's custom field with __r.

For more information, read the Understanding Relationship Names in the Salesforce SOQL and SOSL Reference Guide.

The following CSV file uses a relationship:
 

1Name,Mother_Of_Child__r.External_ID__c
2CustomObject1,123456
 

Relationships for Polymorphic Fields

A polymorphic field can refer to more than one type of object as a parent. For example, either a contact or a lead can be the parent of a task. In other words, the WhoId field of a task can contain the ID of either a contact or a lead. 

Since a polymorphic field is more flexible, the syntax for the column header has an extra element to define the type of the parent object. The syntax is ObjectType:RelationshipName.IndexedFieldName.

The following sample includes two reference fields:

1. The WhoId field is polymorphic and has a relationshipName of Who. It refers to a lead and the indexed Email field uniquely identifies the parent record.

2. The OwnerId field is not polymorphic and has a relationshipName of Owner. It refers to a user and the indexed Id field uniquely identifies the parent record.
 

1Subject,Priority,Status,Lead:Who.Email,Owner.Id
2Test Bulk API polymorphic reference field,Normal,Not Started,lead@salesforcesample.com,005D0000001AXYz


The ObjectType: portion of a field column header is only required for a polymorphic field. You get an error if you omit this syntax for a polymorphic field. You also get an error if you include this syntax for a field that is not polymorphic.
 

Valid CSV Record Rows

The Bulk API uses a strict format for field values to optimize processing for large sets of data. Note the following when generating CSV files that contain Salesforce records:

  • The delimiter for field values in a row must be a comma.
  • If a field value contains a comma, a new line, or a double quote, the field value must be contained within double quotes: for example, "Director of Operations, Western Region".
  • If a field value contains a double quote, the double quote must be escaped by preceding it with another double quote: for example, "This is the ""gold"" standard".
  • Field values aren't trimmed. A space before or after a delimiting comma is included in the field value. A space before or after a double quote generates an error for the row. For example, John,Smith is valid; John, Smith is valid, but the second value is " Smith"; ."John", "Smith" is not valid.
  • Empty field values are ignored when you update records. To set a field value to null, use a field value of #N/A.
  • Fields with a double data type can include fractional values. Values can be stored in scientific notation if the number is large enough (or, for negative numbers, small enough), as indicated by the W3C XML Schema Part 2: Datatypes Second Edition specification.
 

Sample CSV File

The following CSV sample includes two records for the Contact object. Each record contains six fields. You can include any field for an object that you're processing. If you use this file to update existing accounts, any fields that aren't defined in the CSV file are ignored during the update.

You must include all required fields when you create a record.
 

1FirstName,LastName,Title,ReportsTo.Email,Birthdate,Description
2Tom,Jones,Senior Director,buyer@salesforcesample.com,1940-06-07Z,"Self-described as ""the top"" branding guru on the West Coast"
3Ian,Dury,Chief Imagineer,cto@salesforcesample.com,,"World-renowned expert in fuzzy logic design.
4Influential in technology purchases."

Note that the Description field for the last record includes a line break, so the field value is enclosed in double quotes.
 

Considerations while importing data into different objects

  • Lookup Fields - ID ; A Salesforce ID is a case-sensitive 15-character or case–insensitive 18-character alphanumeric string that uniquely identifies a particular record. : To ensure data quality, make sure that all Salesforce IDs you enter in Data Loader are in the correct case
  • PickList Fields: Your field value should exactly match to one of the values in picklist . if you provide any other new value which doesnot exist in your picklist it will not add it to your picklist rather fail the record.
  • Date Fields: Date fields should be formatted in a specific date format which data loader accepts otherwise for Data Import Wizard you don’t need to bother to change the format of date or time fields.
  • Boolean Fields: You can use either True or False value can be used to map. For e.g. if you have checkbox field in your record then to check or uncheck you can use true or false value to check or uncheck it respectively.
  • Currency Fields: You don’t have to add commas or currency sign. Salesforce will automatically pick the format according to your org’s default currency settings.
  • Email Address: Make sure the email is correctly formatted According to RFC 5321 and doesnot include invalid characters which are not allowed according to email international protocols. Few example of invalid emails could be like below: 
 
  • Abc.example.com (no @ character)
  • A@b@c@example.com (only one @ is allowed outside quotation marks)
  • a"b(c)d,e:f;g<h>i[j\k]l@example.com (none of the special characters in this local part are allowed outside quotation marks)
  • just"not"right@example.com (quoted strings must be dot separated or the only element making up the local-part)
  • this is"not\allowed@example.com (spaces, quotes, and backslashes may only exist when within quoted strings and preceded by a backslash)
  • this\ still\"not\\allowed@example.com (even if escaped (preceded by a backslash), spaces, quotes, and backslashes must still be contained by quotes)
  • john..doe@example.com (double dot before @)
  • with caveat: Gmail lets this through, Email address#Local-part the dots altogether
  • john.doe@example..com (double dot after @)
  • a valid address with a leading space
  • a valid address with a trailing space
 
  • Validation rule: Make sure if you have any validation rule applied to any field, your value conform to the criteria for that validation rule otherwise it will fail the record. Or you could choose to override the validation rule to deactivate it before upload.
  • Triggers: Make sure if you have any triggers on the object you are importing or updating records for, and your records are failing due to a specific criteria is not met. You could choose to bypass the trigger by deactivating it before upload and after your upload completes you can activate it again.


When you insert, delete, or update files using Apex Data Loader, use the Mapping Dialog window to associate Salesforce fields with the columns of your CSV file.

1. To automatically match fields with columns, click Auto-Match Fields to Columns. The Data Loader populates the list at the bottom of the window based on the similarity of field and column names. For a delete operation, automatic matching works only on the ID field.

2. To manually match fields with columns, click and drag fields from the list of Salesforce fields at the top to the list of CSV column header names at the bottom. For example, if you are inserting new Account records where your CSV file contains the names of new accounts click and drag the Name field to the right of the NAME column header field.

3. Optionally, click Save Mapping to save this mapping for future use. Specify a name for the SDL mapping file. If you select an existing file, the contents of that file are replaced. Click Yes to confirm this action, or click No to choose another file. 4. Click OK to use your mapping for the current operation.

To import data from CSV files that don’t meet these rules, map the data fields in the CSV file to Salesforce data fields (see Mapping Data Fields for more information).





promote demote