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.

No comments:

Post a Comment