HOW TO #3 – Import Data

Overview

The following commands are the core command to upload records from a .csv file to a Salesforce Org.

Add-SfRecords (Alias sfinsert) to run an INSERT operation
Update-SfRecords (Alias sfupdate) to run an UPDATE operation
Import-SfRecords (Alias sfupsert) to run an UPSERT operation

Key characteristics are:

For authentication, the first parameter needs to be an authorization token as created by Get-SfAuthToken.
The key parameters are
- The target entity/object name.
- The source .csv or .xlsx file.
- A mapping file to map the field names in the .csv file to the API field name of the target org.
You don't need to always write a mapping file: In many cases it can auto-create the field mapping for you.
The .csv file format is expected to be
- UTF-8 encoding
- Without BOM (Byte Order Mark)
- comma as delimiter.
Default is SOAP API with Batch Size 200. For mass data operations, the bulk API can be used. Default Batch Size then is 2000.

For all examples, we assume, that you already have created an auth token in the variable $MyTargetOrg.

Insert Lead Records

Let's say, we've got a .csv file .\Lead.csv with the following content:

"FirstName","LastName","Company"
"Mickey","Mouse","Mouse Private Investigations"
"Donald","Duck","Duck Tales, Inc."

We can insert those Leads to the target org with the following commands (all commands are equivalent):

sfinsert $MyTargetOrg Lead
Add-SfRecords $MyTargetOrg Lead

What it does for you in the background:

From the object name, it's guessing the .csv file name as .\Lead.csv from the current directory.
As you don't provide a mapping file, it will auto-create one as .\SfDataloaderCli\Lead.sdl from the column names of the .csv

The mapping file will look like this:

FirstName=FirstName
LastName=LastName
Company=Company

So the full command that will be executed will look like this:

Add-SfRecords $MyTargetOrg -Object Lead -Path .\Lead.csv -MappingFile .\SfDataloaderCli\Lead.sdl

Standard operation mode is Batch API (SOAP API) with a Batch Size of 200. If you expect large amounts of records in your .csv, you might want to consider Bulk API instead. This could look like:

sfinsert $MyTargetOrg Lead -Bulk Parallel -BatchSize 5000

Upsert Account Records

Let's say, we've got an unique external ID field ErpCustomerNumber__c on the Account object. This will carry the unique ID of the ERP source system. Now we get a .csv file .\ErpAccounts.csv with the following content:

"ErpCustomerNumber__c","Name","BillingCity","BillingCountry"
"CUST-001","Demo, Inc.", "Munich", "Germany"
"CUST-002","ACME Enterprises, Inc.", "ACME City", "USA"

We can now UPSERT this to the target org via:

sfupsert $MyTargetOrg Account ErpCustomerNumber__c .\ErpAccounts.csv

This does same auto-creation of a mapping file as in the example above. Key difference is that you need to explicitly state the field name that is to be used a external ID field.

Upsert Relationship Lookup Fields

Let's say, we've got:

A custom object "Invoice__c" with the following fields
- Name field is configured as auto-number.
- Custom field CustomerAccount__c as a lookup field to the account record
- Custom field InvoiceDate__c as date
- Custom field InvoiceAmount__c as number
- Custom field ErpInvoiceNumber__c as unique external ID text field
From the ERP system, we get a .csv with the list of invoices for each account.
We want to link the invoice to the account via account field ErpExternalId__c as per example above.

We will need a .csv file .\ErpInvoices.csv with the following content of 3 invoices:

"ErpInvoiceNumber__c","CustomerAccount__r.ErpCustomerNumber__c","InvoiceDate__c","InvoiceAmount__c"
"INV-100","CUST-001","2023-01-01","150.00"
"INV-101","CUST-001","2023-01-03","200.00"
"INV-102","CUST-002","2023-03-03","300.00"

We can now UPSERT these invoice records to the target org via:

sfupsert $MyTargetOrg Invoice__c ErpInvoiceNumber__c .\ErpInvoices.csv

This does same auto-creation of a mapping file as in the example above.
It states the field Invoice__c.ErpInvoiceNumber__c as the unique ID for each invoice record.
If sets the lookup field Invoice__c.CustomerAccount__c by referencing the external id field Account.ErpCustomerNumber__c.

Field Mapping Considerations

Automatic Creation of a .sdl Mapping File

The previous examples rely on the auto-creation of a .sdl mapping file which will only work under the following circumstances:

The column name in the source .csv file exactly matches the field's API name in the target org. Upper/lower case doesn't matter.
For relationship lookups the syntax is same as the syntax of the SOQL SELECT statement:
- For standard lookup fields, e.g. Contact->Account: Account.ExternalIdFieldOnAccount__c
- For custom lookup fields, e.g. Invoice__c.CustomerAccount__c: CustomerAccount__r.ExternalIdFieldOnAccount__c
Field names / column names starting with a hash sign # will be considered a comment line and, as a result, will be ignored.
The command CANNOT and WILL NOT verify whether a field name does exist in the target org. So if your .csv contains invalid column headers, you will get invalid entries in your .sdl mapping file.

This logic will allow auto-creation of the .sdl mapping file.

Map a Subset of Fields of Your .csv File

Let's say, you've got a .\MyLeads.csv file with the following content:

"Id","Status","FirstName","LastName","Company"
"00Q1X000006Y5DaUAK","New","Mickey","Mouse","Mouse Private Investigations"
"00Q1X000006XQ4pUAG","Qualified","Donald","Duck","Duck Tales, Inc."

Now, you want to use this for your UPDATE operation to only update the Status field. But you don't want to your the name fields.

Option 1: Tweak the header line of your .csv file to look like this:

"Id","Status","#FirstName","#LastName","#Company"

Then you can just rely on the auto-creation of the mapping file. This will ignore all fields with the # und update only the status field via the record Id.

sfupdate $MyTargetOrg Lead .\MyLeads.csv

Option 2: You don't want to tweak the .csv file. Use a list of field names instead.

Put your list of fields into a Powershell array in the first parameter and convert this into a .sdl mapping file. Then use for your operation.

ConvertTo-SfMappingFile @('Id', 'Status') MyLeadMapping.sdl
sfupdate $MyTargetOrg Lead .\MyLeads.csv -MappingFile MyLeadMapping.sdl

Provide your own .sdl mapping file

Of course, you can still provide your own .sdl mapping file written in a text editor or created and exported with the Salesforce Dataloader GUI.

This MyLeadMapping.sdl would then look like:

Id=Id
Status=Status

This would also be the option to go if the column names in the .csv file do not match the API names:

"IdInCsv","StatusInCsv","MyFirstName","MyLastName","NameOfCompany"
"00Q1X000006Y5DaUAK","New","Mickey","Mouse","Mouse Private Investigations"
"00Q1X000006XQ4pUAG","Qualified","Donald","Duck","Duck Tales, Inc."