Watershed Essentials: CSV Template Functions & Variables

CSV templates are used to map the columns of imported CSV files to relevant fields in Watershed Essentials. Guides exist for each of the types of data that can be imported via CSV. This guide is a reference of functionality that is available across all types of data.

You can use functions to transform and reformat CSV data. These functions have the power to make templates extremely flexible. You can use any functions available within the Handlebars templating language.

The functions we’ve created, plus some especially useful core functions, are documented in the following sections.

1. Comments
2. Transform Functions
3. String Functions
4. Encoding Functions
5. Logic Functions
6. The Inline Function
7. Template Variables

Comments

Comments provide a way to explain a part of your template to future editors, including your future self. Here's an example:

 {{!-- This section does this thing because of that reason. --}}   

Transform Functions

Transform functions are used to transform a value into the format required.

toDateTime converts any format of date into an ISO 8601 date time. ISO 8601 is the format used for timestamps in xAPI and Watershed. It is likely that you will use this transform in almost every template. The transform takes two properties, an input pattern and a value. For example:

 "timestamp": "{{toDateTime "dd-MMM-yy" columns.[Date Completed]}}"  

Or to convert a Unix timestamp, use:

 "timestamp": "{{toDateTime "s" columns.[Date Completed]}}"  

toDuration converts a length of time in milliseconds to an ISO 8601 duration. For example:

 "duration": "{{toDuration columns.[DurationMilliseconds]}}"  

Use with the math function to calculate durations from times in other units.

 "duration": "{{toDuration (math columns.[DurationSeconds] "*" "1000")}}"   

uuid generates a Universally Unique Identifier (UUID) based on a combination of any number of parameters. It can be used to generate a value for an xAPI statement's registration or ID properties, which are required to contain UUIDs as their values. This function is unlikely to be useful in people, groups, and permissions templates. The function is such that every time exactly the same values are passed, the same UUID is generated. This can be useful for:

• Ensuring the same statement ID is used for all statements recording a particular interaction to avoid tracking the same interaction twice. When Watershed receives a statement with the same ID as an existing statement, the new statement is not stored.
• Generating a UUID for the context.registration property that can be reused in all statements relating to a particular attempt.

When populating the statement ID, ensure the combination of values you're using is unique to the interaction being tracked. For example, you might use a combination of the timestamp, actor ID, verb ID, and object ID:

 "id": "{{uuid columns.[Attempted Date] columns.[User ID] 'http://adlnet.gov/expapi/verbs/attempted' columns.[Module ID]}}"  

When populating the registration, ensure the combination of values you're using is unique to the particular attempt or registration. This might be a single globally unique attempt ID or a combination of actor ID, object ID, and a number representing that actor's attempt at that object:

 "context": {  
  registration": "{{uuid columns.[User ID] columns.[Module ID] columns.[Attempt Number]}}"  
 }   

Please Note: Neither statement ID or registration are required properties, and Watershed will automatically populate the statement ID when one is not specified. 

toMbox adds “mailto:” to the start of the value. This can be used in the actor.mbox property. For example:

 "actor": {  
   "name": "foo",  
   "mbox": "{{toMbox columns.[Email Address]}}"  
 }  

Alternatively, you can simply write "mailto:" directly in your template. Both approaches work equally well.

 "actor": {  
   "name": "foo",  
   "mbox": "mailto:{{columns.[Email Address]}}"  
 }  

toNumeric converts a value to a number. This is useful for converting percentages to a scaled score (e.g., 60.7% would be converted to 0.607).

 "result": {  
  "score": {  
   "scaled": {{toNumeric columns.[Percent Score]}}  
  }  
 }  

 
math performs a basic mathematical operation on the value. For example, the following achieves the same result as toNumeric.

 "result": {  
  "score": {  
   "scaled": {{math columns.[Percent Score] "/" "100"}}  
  }  
 }  

datePlus and dateMinus are used to add and subtract time from a date. 

 "timestamp": "{{datePlus (toDateTime "s" columns.[Date]) "2 days"}}"  

String Functions

String functions are used to modify strings of text.

join is used to concatenate strings of text. It can take any number of parameters and uses the last parameter to join the strings together. In many cases, this joining string will either be a space or comma. For example:

 {{join columns.[First Name] columns.[Last Name] ' '}}  

All functions can be nested inside of other functions using parenthesis. This is especially useful for functions like join. For example, if a CSV field contained a percentage score without the percentage sign, you could use the following code to create a string compatible with toNumeric.

 {{toNumeric (join columns.[Score] '%' '')}}  

In this case, the joining string is empty because the percent sign is added immediately after the score with no space.

replace is used to replace part of a value within a CSV field. This can be useful if there are common errors in a CSV field or you need to reformat data for another function. The following example replaces a timestamp's timezone to make it compatible with toDateTime. 

 {{toDateTime "MM/dd/yyyyy hh:mm aa z" (replace columns.[Completion Date] "US/Mountain" "GMT-07:00")}}  

regexReplace works in the same way, except the second parameter is a regex string. The following example would return 'ab':

 {{regexReplace 'a:c' ':.*$' 'b'}}  

The following regex can be used to escape quote characters in a string. The quotes need to be double escaped because they need to be escaped in the template and in the xAPI statement.

 {{regexReplace columns.[Learner Free Text Response] '"' '\\\"'}}  

printable removes newlines from a string of text. This can be useful for user-entered data. For example:

 {{printable columns.[Learner Free Text Response]}}  

rjust and ljust are used to add padding characters to the right or left of a string. For example, a CSV might include user IDs without leading zeros, which need to be added:

 {{rjust columns.[User] size=5 pad="0"}}  

lower, upper, and capitlaize can be used to change the case of a string, which is especially useful for names.

 {{capitalize columns.[Name]}}  

substring takes certain characters from a string, which can be useful if a CSV contains multiple pieces of information in one field. The following example will take the third and fourth characters from the CSV field:

 {{substring value 2 4}}  

Please Note: Substring will throw an error if the value it's passed does not contain enough characters to complete its task!

Encoding Functions

Encoding functions are used to encode or decode variables. urlEncode url encodes a variable. urlDecode url decodes a variable. escapeXml escapes XML content. unescapeXml unescapes XML content.

These can be useful in a number of scenarios, such as:

• An activity name has been encoded and needs to be decoded to make it readable.
• An unencoded variable needs to be included as part of the activity ID.

For example:

 “id”: “http://example.com/activitiies/{{urlEncode columns.[Unique Activity Name]}}”  

Logic Functions

Logic functions can be used to add values, properties and even whole objects based on data in the CSV file. Logic functions wrap content in opening and closing tags with the format: 

 {{#function}} content {{/function}}  

if includes content if the CSV field is not empty. For example:

 {{#if columns.[Score]}}  
 ,  
 "score": {  
  "raw": {{columns.[Score]}}  
 }  
 {{/if}}  

Please Note: Commas are only allowed in JSON if there is a following property. Therefore, you might need to include the comma inside your if block, rather than preceding it, as shown in the example above.

unless includes content if the CSV field is empty. For example:

 {{#unless columns.[Score]}}  
 "score": {  
  "raw": 0  
 }  
 {{/unless}}  

Alternatively, you can use if and else:

 {{#if columns.[Score]}}  
 "score": {  
  "raw": {{columns.[Score]}}  
 }  
 {{else}}  
 "score": {  
  "raw": 0  
 }  
 {{/if}}  

ifEquals includes content if two parameters are equal. ifNotEquals includes content if two parameters aren't equal. For example:

 {{#ifEquals columns.[Status] "FAIL"}}  
   "verb": {  
     "id": "http://adlnet.gov/expapi/verbs/failed",  
     "display": {"en": "failed"}   
   },  
   "result": {  
     "completion": true,  
     "success": false  
   },  
 {{/ifEquals}}  

ifCompare is used with numerical values and includes content if the value is Less Than (LT), Less Than or Equal To (LE), Equal To (EQ), Greater Than (GT), or Greater Than or Equal To (GE). For example:

 {{#ifCompare "GT" columns.[percentcomplete] "0"}},  
 "scaled": {{math columns.[percentcomplete] "/" "100"}}  
 {{/ifCompare}}  

You also can use the ifEqual, ifLessThan, ifLessThanOrEqual, ifGreaterThan, and ifGreaterThanOrEqual functions to the same effect.

Inline Function

In a lot of import CSVs, it is necessary to repeat blocks of the template. You can use the *inline function to avoid repeating the same code multiple times. For example, you can define an actor block to be used in multiple xAPI statements like so:

 {{#*inline "actor" }}  
 "actor": {  
   "name": "{{columns.[Name]}}",  
   "account": {  
     "homePage": "https://example.com",  
     "name": "{{columns.[Employee_ID]}}"  
   },  
   "objectType": "Agent"  
 }  
 {{/inline}}  

You can then insert that block into statements with the following code:

 {  
  {{> "actor"}},  
  "verb": {  
   "id": "http://example.com/verb"  
  },  
  "object": {  
   "id": "http://example.com/object"  
  }  
 }  

Inline functions can take variables, enabling you to define reusable but flexible blocks. For example:

 {{#*inline "actor" }}  
 "actor": {  
   "account": {  
     "homePage": "{{homepage}}",  
     "name": "{{id}}"  
   },  
   "objectType": "Agent"  
 }  
 {{/inline}}  
 {  
  {{> "actor" id=columns.[Employee_ID] homepage="https://example.com"}},  
  "verb": {  
   "id": "http://example.com/verb"  
  },  
  "object": {  
   "id": "http://example.com/object"  
  }  
 }  

 

Template Variables

Template variables are variables mapped to columns of the CSV that can be directly inserted into the template using the syntax:

  {{NameOfTemplateVariable}}  

They are useful if:

• you want a tidier looking template that doesn’t use the columns syntax; or
• you want to reuse the template with CSV files that use different column headings.

To add a template variable, click the Add Template Variable link and then complete the Column and Template Variable fields with the name of the column and template variable you want to match. Template variable names aren’t allowed to include spaces or special characters.

Please Note: You don’t have to use template variables at all, since you can reference columns directly from the template. Use them if you find them helpful and skip that step if not.