Skip to content

HOW TO #2 – Export Data

Jump to bottom
ubraig edited this page Jul 12, 2023 · 1 revision

Overview

Export-SfRecords (Alias sfextract) is the core command to export records from a Salesforce Org into a .csv file.

Key characteristics are:

  • For authentication, it takes an authorization token as provided by Get-SfCredential (Alias: sfauth).
  • The key parameter is the SOQL SELECT statement to be provided as string.
  • There are several advanced options for creating the field list of the SOQL statement. Instead of writing the whole statement yourself, in some cases it can auto-create the field list for you.
  • The .csv file format is pre-configured as UTF-8 encoding with comma as delimiter.
  • If you don't provide a target file name and path, a default will be created for you.
  • Default is SOAP API with Batch Size 200. For mass data operations, the bulk API can be used. Default Batch Size then is 2000.

We assume, that you already have created an auth token in the variable $MySourceOrg.

General note on the command syntax shown below:

  • The command Export-SfRecords and its sfextract can be used interchangeable.
  • The first 3 parameters, Authorization + Entity (SObject Name) + SOQL Statement are mandatory. As per Powershell default behaviour, you can provide those as named parameters as well as via positional parameters.

Export Lead Records

The following commands are equivalent:

  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead"
  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead" .\Lead.csv
  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead" .\Lead.csv -BatchSize 200

The last example could also be written in the long form as:

  • Export-SfRecords -SfCredential $MySourceOrg -Object Lead -soql "SELECT Id, FirstName, LastName, Company FROM Lead" -Path .\Lead.csv -BatchSize 200

If you expect a large number of records, the Bulk API might be a better choice. And again, the following commands are equivalent:

  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead" -Bulk Serial
  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead" .\Lead.csv -Bulk Serial
  • sfextract $MySourceOrg Lead "SELECT Id, FirstName, LastName, Company FROM Lead" .\Lead.csv -Bulk Serial -BatchSize 2000

Result is Lead.csv in the current directory.

Advanced SOQL Statements

You can write all SOQL statements that are supported by Dataloader, including:

  • WHERE clause to filter
  • ORDER BY clause
  • LIMIT clause

Example:

  • $MySoql = "SELECT Id, FirstName, LastName, Company FROM Lead WHERE (Status = 'New') ORDER BY LastName ASC LIMIT 20"
  • sfextract $MySourceOrg Lead $MySoql

Defining the List of Fields to Export

There are various options for putting together the field list:

Full SOQL Statement

Provide a full SOQL statement:

  • sfextract $MySourceOrg Account "SELECT Id, Name, BillingCity, BillingCountry, MyCustomField__c FROM Account"

Alternate option in separate variables:

  • $soql = "SELECT Id, Name, BillingCity, BillingCountry, MyCustomField__c FROM Account"
  • sfextract $MySourceOrg Account $soql

Alternate option via Powershell variable expansion:

  • $MyAccountFields = 'Id, Name, BillingCity, BillingCountry, MyCustomField__c'
  • sfextract $MySourceOrg Account "SELECT $MyAccountFields FROM Account"

Get List of Field Names from a .txt File

Let's say, we've got a plain text file .\MyAccountFields.txt with the following content:

Id
Name
BillingCity
BillingCountry
MyCustomField__c
#MyUnnecessaryField__c

Then we can put together this as comma-separated list of field names and use in same command as above:

  • $MyAccountFields = ConvertTo-SfFieldList (Get-Content .\MyAccountFields.txt)
  • sfextract $MySourceOrg Account "SELECT $MyAccountFields FROM Account"

All entries with a prefix of # will be considered a comment line and will be ignored.

Get List of Field Names from the Header of a .csv File

Let's say, we've got an existing .csv file .\MyAccounts.csv with the following content:

"Id","Name","BillingCity","BillingCountry","MyCustomField__c","#MyUnnecessaryField__c"
"0011X00000dd9XxQAI","Demo, Inc.","Munich","Germany","12345678","DummyValue"

Then we can put together this as comma-separated list of field names und use in same command as above:

  • $MyAccountFields = ConvertTo-SfFieldList (Get-SfFieldNames .\MyAccounts.csv)
  • sfextract $MySourceOrg Account "SELECT $MyAccountFields FROM Account"

All column names with a prefix of # will be considered a comment line and will be ignored.

Clone this wiki locally