Sunday, August 7, 2016

CSV Data: Commonly Surfacing Vexations in Data

According to Auntie Pat Tern, "Trying the same thing over and over without getting the results you want is enough to make anyone crazy." Unfortunately, that's the excuse Tim Toady uses when he doesn't do his homework because he's tired of not getting the results he wants from the effort.

When it comes to creating CSV files for use as unit test data, what should be an easy process can make you a bit crazy if you wind up getting unfamiliar errors. The following steps and potential errors may help:

Step 1: Export some production data.
Step 2: Delete all but a reasonable selection of data.
Step 3: Remove system fields.
Step 4: Make sure the date fields are in the form YYYY-MM-DD
Step 5: Make sure date/time fields have a "T" between the date and time, with no space, such as 2016-08-07T01:40:39
Step 6: Make sure the commas that appear in text fields haven't thrown off the number of columns.
Step 7: Make sure there are no blank rows with only commas and no values
Step 8: Renumber IDs starting with 1
Step 9: Remove any invalid IDs for related records (Account ID of 000000000000000AAA appears for all top-level accounts, and should be removed, for example)
Step 10: Upload the CSV file as a Static Resource for your code to reference

If you are creating a set of child records for an object that is related to another object, you can sort the parent and child data by the IDs of the parent to make sure you get records that match in both data sets. Use search and replace to redo the parent IDs to match the new IDs in the CSV file of parent records. For example, if you have a CSV for Accounts with IDs numbered 1-200, the related contact records must use 1-200 for the Account ID as well.

Bad CSV files might result in the following errors:

Potential Error Likely Solution
Invalid ID value on line 2: 000000000000000 Remove invalid IDs
Too many DML rows: 10001 Load fewer records
CSV Parse error: '8/20/1959' is not a valid value for the type xsd:date Format dates as YYYY-MM-DD
Duplicate ID value on line 81: null Remove empty rows from CSV file
CSV Parse error: '2011-09-07 01:00:31' is not a valid value for the type xsd:dateTime Format date/time fields with "T" rather than space between date and time
Validation Errors While Saving Record(s) Erroneous data or IDs not starting with 1
System.UnexpectedException: Salesforce System Error Remove stray commas throwing off columns
Static Resource not found Make sure code refers to Static Resource by name

Start with a small number of records using the fewest fields for testing the code or configuration changes you need to test. That way, you won't wind up like Tim Toady, who falls back on bad habits when errors occur with his first attempts to follow best practices.


  1. Hello bonnie
    I recently watched your pluralsight course on Testing. Great job by that way. I am trying to implement data silos using the Test.loaddata method but am running into an unknown validation issue. I‘m using a test utility to load the CSV data, then calling that method in an @TESTSETUP on my actual test class. I was able to successfully load two separate CSV’s (Users & Accounts), but am not able to load campaign records. I’m getting an error that says “Validation Errors While Saving Record(s)”. To keep me sane, I have turned off all validation rules and taken my Campaign CSV down to one record with minimal information.

    I have scoured the net for an explanation and have re-watched your course a few times now. I am paying close attention to required fields, formatting of Date and DateTime and the other Vexing issues that are outlined on your blog. Any ideas on this?

    Test Utility
    public class TestUtilityClass {

    public static void CreateTestAccountsAndCampaigns() {
    list dataList = new list();

    Test Class
    private class Test_OfficerStats_CampaignUpdate {
    // Testing the Following Conditions:
    // 1) Test that an event memberships update will retroactively alter the stats for a particular day
    // 2) Test that an alteration of the facilitating officer for a particular day will retroactively alter officerstat totals for both officers on that day
    // 3) Test that deleting an event from a past day will retroactively alter the officerstat totals

    @testSetup static void setup() {
    id UserId = system.UserInfo.getUserId();
    user CurrentUser = [Select id from user where id = :UserId];



    // ****************** Test Scenerio 1: *****************
    public static Testmethod void TestUpdateEvent(){
    list AcctList = [Select id, RecordTypeId, name from Account];
    list userList = [select id, name FROM User where isActive = true];
    for(Account a : AcctList){
    for(user u : userList){

    // ****************** Test Scenerio 2: *****************
    public static Testmethod void TestChangingOfficer(){


    // ****************** Test Scenerio 3: *****************
    public static Testmethod void TestDeletingEvent(){



    1. Make sure your IDs are valid and unique integers, and check other fields against suggestions I listed in this post. What error are you seeing in the debug log? It could indicate that you have a custom validation rule on a trigger or other code that runs on data creation.
      Best Wishes!