Use Case: You need to load data stored in CSV files as static resources into different objects
This scenario can happen whenever you want to load data samples into a sandbox org for instance. The exact same set of data samples across all your sandboxes to allow your developers to work and perform unit tests on a specific set of data.
The usual challenges while loading data spread into many objects, using a data loader tool or not, is to be able to load them in one shot without having to load the first file, extract loaded data to get the Salesforce ID generated, working with Excel V-Lookup functions to prepare the next file associated to another linked object, and so on and so forth…
Even having fields defined as External ID on the objects won’t weed out the cumbersome task of having to load file after file, object after object on each and every Salesforce org, with all standard tools Salesforce propose.
If you have access to an ETL tool, that’s better, you can easily build your own job and re-use it whenever and wherever you need!
If you don’t, this solution might help you if you have a bunch of data to load in different objects, and being linked together through lookups or master-detail relationships.
The solution is working at one condition: you need to have an External ID field defined by object, and populate it as part of your data file.
This solution also requires some Apex knowledge, as you need to understand, adapt and execute a script and build the field mapping between your data file and your data model in Apex.
I propose here a simple example of loading 2 Accounts and its associated Contacts. Once you get the idea, you can easily build your own components, and even make it more dynamic by leveraging Custom Metadata Types to store the fields mapping as well as the file-object mapping.
Step 1: Create CSV files with your data and upload them to Static Resources
I personally like working with | as a field separator to avoid any confusion and also have an External ID field in every object, to be ready for any future need. Such a new field has absolutely no impact on your database storage, so you’d better plan for it the sooner the better!
Example of Account file
Name|ExternalID__c|Industry|AccountNumber Account 01|Acc01|Agriculture|12345 Account 02|Acc02|Chemicals|67890
Example of Contact file
Name|ExternalID__c|Department|AccountId John Smith|Ctc1|Accounting and Finance|Acc01 Simon Roberts|Ctc2|Purchasing|Acc01 Rose Jackson|Ctc3|Marketing |Acc01 Amy Johnston|Ctc4|Production|Acc02 Jasmine Sutherland|Ctc5|Research and Development|Acc02
Here we are, we have 2 accounts and 5 contacts to load.
| Account | Contact |
|---|---|
| Account 01 | John Smith |
| Account 01 | Simon Roberts |
| Account 01 | Rose Jackson |
| Account 02 | Amy Johnston |
| Account 02 | Jasmine Sutherland |
You can now upload these files in your Static Resources.
Step 2: Create a CSV Reader class
In order to read the CSV files, we will be simply re-using a great existing shared code available on NicoCRM blog to parse the CSV file.
You can also download the Apex class and its associated Test class below. Code coverage is 100%.
Create these files in your sandbox and go to the next step.
Step 3: Adapt and run the following Anonymous Script on your Sandbox
The below script will allow you to load both your Account data and your Contact data. Before running the script, just adapt the following lines to your situation:
- Lines 2 and 3: update your static resource names (here Account and Contact)
- Lines 9 and 10: update with your field separator
- Lines 25 and 30: update with your field mapping. bodyAccSR_line[0] represents the first field in your CSV file. Map it with the Salesforce field in your object.
- Lines 51-53 and 62-64: same logic as above, bodyCtcSR_line[0] represents the first field in your CSV file. Build the mapping to your Contact object.
- Lines 54 and 65: this is where the link is done from Contact to Account using the External ID field. Update these lines with your Account ExternalID API name (here ExternalID__c)
//Query your files in static resources and get their content as String
StaticResource accSR = [SELECT Id, Body FROM StaticResource WHERE Name = 'Account' LIMIT 1];
StaticResource ctcSR = [SELECT Id, Body FROM StaticResource WHERE Name = 'Contact' LIMIT 1];
String bodyAccSR = accSR.Body.toString();
String bodyCtcSR = ctcSR.Body.toString();
//Parse using your separator
SSSCsvReader bodyAccSR_csvR = new SSSCsvReader(bodyAccSR, '|');
SSSCsvReader bodyCtcSR_csvR = new SSSCsvReader(bodyCtcSR, '|');
/*********************************/
//Loading Account Sample Data
/*********************************/
//Removing header if you are using a title (optional)
bodyAccSR_csvR.readLine();
//Load the next line in a variable
String[] bodyAccSR_line = bodyAccSR_csvR.readLine();
List<Account> lSampleAcc = new List<Account>();
While (bodyAccSR_line != null)
{
lSampleAcc.add(new Account(Name=bodyAccSR_line[0], ExternalID__c=bodyAccSR_line[1], Industry=bodyAccSR_line[2], AccountNumber=bodyAccSR_line[3]));
bodyAccSR_line = bodyAccSR_csvR.readLine();
}
// Last line if not null
if (bodyAccSR_line != null) {
lSampleAcc.add(new Account(Name=bodyAccSR_line[0], ExternalID__c=bodyAccSR_line[1], Industry=bodyAccSR_line[2], AccountNumber=bodyAccSR_line[3]));
}
if (lSampleAcc != null) {
Insert lSampleAcc;
}
/*********************************/
//Loading Contact Sample Data
/*********************************/
//Removing header if you are using a title (optional)
bodyCtcSR_csvR.readLine();
//Load the next line in a variable
String[] bodyCtcSR_line = bodyCtcSR_csvR.readLine();
List<Contact> lSampleCtc = new List<Contact>();
While (bodyCtcSR_line != null)
{
Contact sampCtc = new Contact();
sampCtc.LastName=bodyCtcSR_line[0];
sampCtc.ExternalID__c=bodyCtcSR_line[1];
sampCtc.Department=bodyCtcSR_line[2];
sampCtc.Account = new Account(ExternalID__c=bodyCtcSR_line[3]);
lSampleCtc.add(sampCtc);
bodyCtcSR_line = bodyCtcSR_csvR.readLine();
}
if (lSampleCtc != null) {
Insert lSampleCtc;
}
Run the script and check the results.
Extend this simple example to your own data model
Once you got the main idea behind this simple example, you can apply it to any of your custom data model and extend it as much as you want!
Just keep in mind this script is limited to loading 10 000 records as this is loaded through one Apex transaction. With some extra code you can easily extend the logic with an Apex Batch or Queueable process, and leverage a Custom Metadata driven static resource / object / field mapping!