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

To convert an Excel date number use:

{{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.

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

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

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. 

value rounding modes
up down ceiling floor half_up half_down half_even
5.5 6 5 6 5 6 5 6
2.5 3 2 3 2 3 2 2
1.6 2 1 2 1 2 2 2
1.1 2 1 2 1 1 1 1
1 1 1 1 1 1 1 1
-1.1 -2 -1 -1 -2 -1 -1 -1
"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.

Please note: You can also use join to join strings. This works the same way as joinif, but will return an empty string if any of the values to be joined are empty. When joinif encounters an empty value it will skip the separator and move on to the next value.

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] '(?<!\\)\"' '\\\"'}}

 This regex escapes quotes included in csvs as &quot;. Actual quote characters in CSV files are escaped automatically.

 The quotes need to be double escaped because they need to be escaped in the template and in the xAPI statement.

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.

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

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

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

A comparison between a value and a list of values can be performed using IfEquals and IfNotEqualsifEquals 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"}}

 Hint: This works by replacing the contents of the column with "0" if it matches the regex, and then seeing if the result of that equals 0. In this example, 0 is used as the value to replace and match because 0 is a number. This ensures that the ifEquals will never evaluate true for a non-number. If, for example, "isNumeric" was used instead, the ifEquals would also evaluate to true if the column included the value"isNumeric" rather than a number.

 

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

Comments

0 comments

Please sign in to leave a comment.

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