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.

What A Load Of Business Data

Auntie Pat Tern thinks Superman's x-ray vision is stupid because "how would he ever know where he's supposed to look and where he should not bother focusing?" That got me wondering about the data and tests in my Salesforce org.  Even clicks-not-code developers should have automated tests that validate configuration changes with existing and expected data. So what's the best way to know which data those automated tests should use?

Some Salesforce developers like to write automated tests with "SeeAllData=True", an outdated and bad practice. A test that can see all data is not the same as a test that is smart enough to see the data that needs testing. For example, a good test will test positive scenarios, negative scenarios and extremes. In other words:

• What if the data is exactly what we planned for -- records with all the right data in all the right places?
• What if the data falls outside of expected norms -- records with missing fields or invalid data, for example?
• What if the data is coming in from a data import operation or an integration and many records need to be processed?

Admins can follow a few simple steps to create automated tests to run against their configuration changes, and their efforts can be used by developers for better unit tests as well. Simply create sample data representative of the expected inputs. Then use code like the following to load that data in a unit test:


Note that you will want to use data that corresponds to the configuration changes that you are testing. In this example, configuration changes and business process automation around Account, Contact and Opportunity record creation will be tested. You can create a similar test class for other related objects, including custom objects.

This code can be run as a unit test by itself to validate configuration changes, or it can be called by other test code to set up data for more complex unit testing related to other code in the org.

Next week, I will look into some of the errors that might occur when you create CSV files of sample data and how to avoid those errors.

But for now, Auntie Pat Tern has a point, who wants Superman looking at just any old thing with his x-ray vision when he ought to be focusing on information that's most helpful.