CSV Templates Functions and Variables

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

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

Please note: neither statement id or registration are required properties and Watershed will automatically populate the statement id when one is not specified. Speak to the your Watershed implementation team if you need help deciding whether or not it's appropriate to to incliude these properties in your CSV template.

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

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

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 a lot of 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 but 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 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. 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 3rd and 4th 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, 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}}

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 are not equal. For example:

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

ifLessThan, ifLessThanOrEqual, ifGreaterThan and ifGreaterThanOrEqual are used with numerical values and includes content if the value is Less Than, Less Than or Equal To, Greater Than, or Greater Than or Equal To. For example:

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

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.

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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

If you can't find what you need or you want to ask a real person a question, please contact customer support.