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.

Who can use this feature?
 User Types
Only Global Admins can create CSV import templates and upload CSV files.
 Pricing
Available on paid plans (Analyst, CLO, and Enterprise).
 Expertise
Only Experts should create CSV import templates.

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.

You may encounter errors while writing your CSV template. Any errors with the template will appear inside of a red text box as shown below. In the particular example below, the opening tag {{#*inline 'object'}} does not match the closing tag {{/inlin}}. Notice that the closing tag says inlin when it should be inline.

image__2_.png

Preview

When you're ready to test the template, use the preview button to test it. If there are problems compiling the template (due to syntax errors etc.), you will receive error messages indicating this below the template.

Once the template compiles, clicking preview will open up a pop-up showing the statements, people, groups and permissions that the template will create for each row of the file. Use this to ensure that the correct data is being used. 

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

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