How do I create a Data CSV Import Template?

Watershed’s Import Data settings page enables you to import CSV files to be converted into xAPI statements using a template. The templates map the columns of a CSV file to properties of an xAPI statement. You’ll need an import template for each type of CSV you upload, and once the mapping has been created new CSVs following the same structure can be uploaded as often as required.

Templates are created using the Import Templates settings page. Creating import templates is a complex task that requires an in-depth technical knowledge of xAPI and is normally completed for you by the Watershed team. This guide explains how to create Import Templates. It has three sections.

Creating a New Import Template

To create a new Import Template, click the Add Import Template button on the Settings / Import Templates page. Enter a name for the template and then upload an example csv source file which the template will be based on.

In the next two steps you specify template variables and create a statement template. We recommend that rather than specifying template variables up front, that you dive straight into creating the statement template, scrolling back up to specify template variables if and when you need them. 

The Statement Template

The Statement template is an array of xAPI statements with placeholder values and functions that are filled in using data from each row of the CSV. The statement template uses the Handlebarsjs templating language. See CSV Templates Functions and Variables for a list of available functions.

Values are inserted using the handlebars syntax {{columns.[Name of Column]}}. For example, the below syntax inserts the value of the Email Address field into the actor.mbox property.

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

You can also insert values using template variables using the syntax {{NameOfTemplateVariable}}.

The statement template can include an array of statements. This is useful if you need to generate multiple xAPI statements per row of the CSV. For example, often we see CSV files of quiz results which include the overall quiz results plus responses to each question; you can use multiple statements in the template to create one statement for overall quiz completion plus one statement per question.

Complete Example Statement Template

Below is a complete statement template example for a CSV file recording the status of learners in regards to compliance certification training.

{
 "statements": [
  {
   "actor": {
    "name": "{{columns.[First Name]}} {{columns.[Last Name]}}",
    "account": {
     "name": "{{columns.[Employee UID]}}",
     "homePage": "https://example.com/employee-id"
    }
   },
   "object": {
    "id": "https://example.com/certifications/{{columns.[Certification Type]}}",
    "objectType": "Activity",
    "definition": {
     "type": "https://www.opigno.org/en/tincan_registry/activity_type/certificate",
     "name": {
      "en": "{{columns.[Certification Type]}}"
     }
    }
   },
   {{#ifEquals columns.[Status] "PASSED"}}
    "timestamp": "{{toDateTime "dd-MMM-yy" columns.[Sort by Test Date]}}",
    "verb": {
     "id": "http://adlnet.gov/expapi/verbs/passed",
     "display": {"en": "passed"}
    },
    "result": {
     "completion": true,
     "success": true
    },
   {{/ifEquals}}
   {{#ifEquals columns.[Status] "FAIL"}}
    "timestamp": "{{toDateTime "dd-MMM-yy" columns.[Sort by Test Date]}}",
    "verb": {
     "id": "http://adlnet.gov/expapi/verbs/failed",
     "display": {"en": "failed"} 
    },
    "result": {
     "completion": true,
     "success": false
    },
   {{/ifEquals}}
   {{#ifEquals columns.[Status] "CURRENT"}}
    "timestamp": "{{toDateTime "dd-MMM-yy" columns.[Sort by Test Date]}}",
    "verb": {
     "id": "http://adlnet.gov/expapi/verbs/passed",
     "display": {"en": "passed"} 
    },
    "result": {
     "completion": true,
     "success": true
    },
   {{/ifEquals}}
   {{#ifEquals columns.[Status] "EXPIRED"}}
    "timestamp": "{{toDateTime "dd-MMM-yy" columns.[Expiration Date]}}",
    "verb": {
     "id": "http://id.tincanapi.com/verb/expired",
     "display": {"en": "expired"}
    },
   {{/ifEquals}}
   "context": {
    "contextActivities": {
     "category": [
      {
       "id": "https://example.com/certifications/csv-import/v1",
       "definition": {
        "name": {
         "en": "Example Certifications CSV Import Version 1"
        },
        "type": "http://id.tincanapi.com/activitytype/source"
       }
      }
     ]
    }
   }
  }
 ]
}
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.