Prepare a CSV File for an Import or Update in Salesforce

Preparing CSV Files

The first row (header 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 is referred to as a batch.

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

Processing CSV files with the Bulk API:

• Files must be in UTF-8 format.
• The Bulk API only supports commas as a delimiter.
  • If you’re using an international computer, the .CSV file generated on Excel is likely not be delimited with commas. You can check if your .CSV file delimiter is a comma by opening it with the Notepad app on your computer.
  • If you see Tom;Jones;buyer@salesforcesample.com it means the current delimiter is a semicolon instead of a comma. This results in import issues with your file.
    • Replace the semicolons with commas. On a PC for instance, you can do CTRL + H with your keyboard to Find and Replace and then save the file.
• The Bulk API is optimized for processing large sets of data and has a strict format for CSV files. 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. 
• If you're updating a record, any fields that aren't defined in the CSV file are ignored during the update.

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

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

Notes:
 

1. You can use a child-to-parent relationship, but you can't use a parent-to-child relationship.
2. You can use a child-to-parent relationship, but you can't extend it to use a child-to-parent-grandparent relationship.
3. 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 Standard Objects.  

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__c to 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.

The following CSV file uses a relationship:
 

1	Name,Mother_Of_Child__r.External_ID__c
2	CustomObject1,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 isn’t polymorphic and has a relationshipName of Owner. It refers to a User and the indexed Id field uniquely identifies the parent record.
    

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

Valid CSV Record Rows

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 isn’t polymorphic.

The Bulk API uses a strict format for field values to optimize processing for large sets of data. Remember 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" isn’t 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.
• Excel has been known to remove leading zeros from *.CSV files. Be sure to review number values (for example, ZIP codes) prior to import.

• As a workaround, Users can attempt to open a *.CSV file in Excel and then Save it. This can sometimes correct certain formatting issues.
• Just one invalid row can cause an entire import to fail. One can break up the CSV into smaller files to help identify which record and/or column is causing errors or import problems.  

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.
 

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

Note: 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 the Data Loader are in the correct case.
• PickList Fields: Your field value must exactly match one of the values in the picklist. If you provide any other new value that doesn't exist in your picklist, it does not add it to your picklist but rather fails the record. Here's a doc on how to Update 'multi-select' picklist field values. 
• Date Fields: Date fields should be formatted as outlined in the Format for the 'Date' and 'Date Time' data in a CSV file otherwise, for the Data Import Wizard you don’t need to change the format of date or time fields.
• Boolean Fields: You can use either a True or False value can be used to map. For example, if you have a checkbox field in your record then to check or uncheck you can use true or false values to check or uncheck it respectively.
• Currency Fields: You don’t have to add commas or currency signs. Salesforce automatically picks the format according to your org’s default currency settings.
• Email Address: Make sure the email is correctly formatted According to RFC 5321 and doesn't include invalid characters that aren’t allowed according to email international protocols. A few examples 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 a 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 conforms to the criteria for that validation rule otherwise it fails the record. Or you could choose to override the validation rule to deactivate it before the upload.
• Triggers: Make sure if you have any triggers on the object you’re importing or updating records for, and your records are failing due to specific criteria not being met; you could choose to bypass the trigger by deactivating it before the upload. Then, after your upload completes, you can activate it again.

When you insert, delete, or update files using the 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’re 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 the Salesforce data fields, and review Mapping Data Fields.


See Also

• Convert your spreadsheet into CSV format
• Add IDs of existing records to your import file
• Replace semicolons with commas in CSV data imports

• Data Import: Prepare your Data for Import
• Data Import: Best Practices for Importing Data
• How to Prepare a CSV File to Insert Using Data Loader
• How to Prepare Your CSV File Using Vlookup in Excel