CSV templates are used to map the columns of imported CSV files to relevant fields in Watershed. 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 Handlebarsjs templating language, any functions added by Handlebars.java, plus some additional functions we’ve created. The functions we’ve created, plus some especially useful core functions are documented below.

Functions with variables are requested to have a space between the end of the variable. for example {{someFunction (otherFunction variable)}} is valid Handlebars syntax. {{someFunction(otherFunction variable)}} is not.

This guide has seven sections.

• Comments
• Transform Functions
• Number Functions
• String Functions
• Encoding Functions
• Logic Functions
• The Inline Function
• 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]}}" 

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

{{toDateTime "dd/MM/yyyy HH:mm" (join (regexReplace columns.[Date Completed] "^(\d+)\.(\d+)$" "$1") "/01/1990 00:" (math (regexReplace columns.[Date Completed] "^(\d+)\.(\d+)$" "0.$2") "*" "1440") "")}}

datePlus and dateMinus are used to add and subtract time from a date.  The amount to add or subtract is expressed using ISO 8601 duration format (PyYmMdDThHmMsS). For example, to add 2 days:

"timestamp": "{{datePlus (toDateTime "dd/MM/yyyy" columns.[Date]) "P2D"}}" 

{{toDateTime "yyyy-MM-dd" "now"}}

{{datePlus "now" "P3M"}}

{{dateMinus "now" "P3M"}}

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 (toFixed (math columns.[DurationSeconds] "*" "1000") 0 "half-up")}}" 

Note: toDuration needs an integer value so when using the math function, you should round the value to an integer using the toFixed function as shown in the example above.

Generate a UUID

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 recieves 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 re-used in all statements relating to a particular attempt.

When used to populate the statement id, you should ensure the combination of values used 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 used to populate the registration, you should ensure that the combination of values used 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]}}"
}

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]}}"
}

slugify replaces spaces and punctuation in a string of text to make it suitable for use as part of a url. For example

"object": {
    "id": "http://example.com{{slugify columns.[name]}}"
}

Number functions

toNumeric converts a value to a number. This is useful for converting percentages to a scaled score, for example 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"}}
  }
}

toFixed is used to round a number to a configured number of decimals. Parameters are value, number of decimals and rounding mode. Some example values and the results with different rounding modes are shown in the table below.

"result": {
  "score": {
    "scaled": {{toFixed columns.[Long Number] 2 "half-up"}}
  }
}

String functions

String functions are used to modify strings of text.

joinIf 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 a lot of cases this joining string will either be a space or comma. For example:

{{joinIf 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 but without the percentage sign, you could use the following code to create a string compatible with toNumeric.

{{toNumeric (joinIf 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 that the second parameter is a regex string. The example below would return 'ab'

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

The following regex can be used to escape quote characters in a string.

{{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 3rd and 4th characters from the csv field:

{{substring value 2 4}}

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, for example:

• 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}}

You can perform If... Else... logic by including an else block:

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

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

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

All logic functions can have an else block. For example:

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

ifEquals includes content if two parameters are equal. ifNotEquals includes content if two parameters are not equal. Both values must be strings. For example:

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

A comparison between a value and a list of values can be performed using IfEquals and IfNotEquals. ifEquals compares all subsequent parameters to the first parameter and includes content if any of them match. ifNotEquals includes content if none of the subsequent parameters match the first. For example:

{{#ifEquals columns.[Status] "FAIL" "F" "fail"}}
    "verb": {
        "id": "http://adlnet.gov/expapi/verbs/failed",
        "display": {"en": "failed"} 
    }
{{/ifEquals}}

ifLessThan, ifLessThanOrEqual, ifGreaterThan and ifGreaterThanOrEqual include content if the value is Less Than, Less Than or Equal To, Greater Than, or Greater Than or Equal To, respectively. All values must be strings, but comparisons are first made by attempting to convert the strings to numerical values. For example:

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

If the strings do not have numerical representations, then comparisons are made using standard lexicographical string comparisons. This allows timestamps to be compared correctly. For example:

{{#ifLessThan (toDateTime "MM/dd/yyyy" columns.[expirationDate]) "2018-07-15"}},
    "verb": "http://id.tincanapi.com/verb/expired"
{{/ifLessThan}}

isFirstRow and isLastRow are used to include template elements only on the first or last row of the CSV file (after any header rows). This can be useful if the first or last row include a special value, such as an average or total. For example:

{{#isFirstRow}}
  This only shows up on the first row!
{{else}} 
  This shows up every row after the first!
{{/isFirstRow}}

{{#isLastRow}}
  This only shows up on the last row!
{{else}} 
  This shows up every row before the last!
{{/isLastRow}}

Skipping rows

Skipping rows entirely is done by simply not including any statements/people/groups etc. when the logic conditions match. This might be done by wrapping the whole template in a logic function. The inline function can be used to make this cleaner. Templates will often include all the logic is together and the result of each outcome calls a different inline function (or passes different parameters to an inline function). Here's a simplified example of that from an LMS template:

{
  "statements": [
    {{#ifEquals columns.[Transcript Detail-Transcript Overall Status] "Completed"}}
      {{> 'completed-statement'}},
      {{> 'attempted-statement'}},
      {{> 'registered-statement'}}
    {{/ifEquals}}
    {{#ifEquals columns.[Transcript Detail-Transcript Overall Status] "In Progress"}}
      {{> 'attempted-statement'}},
      {{> 'registered-statement'}}
    {{/ifEquals}}
  ]
}

In this case, if the status is neither Completed nor In Progress, the row is skipped.

if this and that

Multiple if conditions can be nested inside one another to only include elements if multiple conifitions are true.

{{#ifEquals columns.[Transcript Detail-Transcript Overall Status] "Completed"}}
  {{#if columns.[Timestamp]}}
    {{> 'completed-statement'}}
  {{/if}}
{{/ifEquals}}

if this or that

Or conditions are achieved by repeating ifs both pointing to the same inline function.

{{#ifEquals columns.[Transcript Detail-Transcript Overall Status] "Completed"}}
  {{> 'completed-statement'}}
{{/ifEquals}}
{{#ifEquals columns.[Transcript Detail-Transcript Overall Status] "Passed"}}
  {{> 'completed-statement'}}
{{/ifEquals}}}

Using regex for more complex logic functions

You can also regex inside logic functions for more complex logic. For example, the following logic condition would evaluate to true only for values containing only numbers.

{{#ifEquals (regexReplace columns.[Column that sometimes contains a number] "^\d+$" "0") "0"}}

The following can be used to validate email addresses:

{{#ifEquals (regexReplace columns.[email] "^[\w-]+@([\w-]+\.)+[\w-]+$" "valid@example.com") "valid@example.com"}}

Splitting fields

Sometimes a single cell of a CSV file contains a list of items separated by a delimiter, such as a comma or pipe. For example, a Jobs Roles column might contain a list of all job roles a person is able to perform. In that case, the splitEach function can be used to apply a template for every item in that list. The keyword {{this}} is used to insert items from the list into the template. For example.

{{#splitEach columns.[jobrole] ','}}
  {
    "customId": "Job Role: {{this}}",
    "name": "{{this}}",
    "type": "Job Role",
    "peopleCustomIds": ["{{columns.[userid]}}"]
  },
{{/splitEach}}

This would result in a Job Role group being created for every item in a comma separated list of job roles.

splitEach also provides variables containing information about the state of the split operations: booleans @first, @last, @even, and @odd, and the 1-based index value @index. These can be used within the splitEach block to provide conditional results.  For example, suppose you need to create a comma-separated list of items within an array. @last can be used with the unless function to insert a comma after each item except the last one:

"choices": [
  {{#splitEach columns.[CHOICES] ';'}}
    { 
      "id": "{{this}}" 
    }
    {{#unless @last}},{{/unless}}
  {{/splitEach}}
]

Inline function

In a lot of import CSVs it is necessary to repeat blocks of the template. You can use the *inline function 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 allow to include spaces or special characters.