Watershed Essentials: How to Create a Custom CSV Template

How do I add a custom CSV 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, you can upload new CSVs following the same structure as needed.

Your Watershed Essentials account comes with a default interactions template. If you have broader needs and have the technical capabilities, you can create your own template and upload it for future use.

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 by expert xAPI practitioners. This guide explains how to create Import Templates. It has three sections.

1. The Statement Template
2. Complete Example Statement Template
3. Creating a New Import Template

Before creating a new import template in Watershed, you’ll need to build your statement template.

Creating The Statement Template 

The Statement template is an array of xAPI statements with placeholder values and functions that are filled with 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 . For example, the below syntax inserts the value of the Email Address field into the actor.mbox property.

 "actor": {  
   "name": "foo",  
   "mbox": "mailto:"  
 }  

You also can insert values using template variables using the syntax .

The statement template can include an array of statements, which can be useful for generaterating multiple xAPI statements per row of the CSV. Take, for example, a CSV of quiz results that include 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"  
    }  
    }  
   ]  
   }  
 }  
 }  
 ]  
 }  

Once you have created your Statement Template, you are ready to add your custom CSV Import Template to Watershed.

 Creating a New Import Template in Watershed

1. In your Watershed account, go to the Edit Templates menu in the Other Data Sources section of the Data page. Click "Add Import Template" to get started. Import templates are the code that convert the data in your CSV to xAPI.
   
   
2. Enter a name for the template and then upload an example CSV source file, which the template will be based on.
   
   
3. In the next two steps, you'll specify template variables and create a statement template. We recommend that rather than specifying template variables up front, you dive straight into creating the statement template—scrolling back up to specify template variables if and when you need them.
   
4. Paste the Statement Template you created earlier into the Create Statement Template box. It will look something like this:
   
5. After you save the template, it should appear on the list of templates:
   
6. Go to the Import Templates menu in the Other Data Sources section of the Data page. Select the new template you just created from the dropdown.
   
7. Upload the CSV file that fits the format of your newly added template:
   
8. The screen will update to preview a few statements. Click the Import statements button to begin the import. It could take up to a minute for the upload to complete. 
   
   

CSV Import Tool Tips:

• If your file has more than 10,000 lines, you will need to upload it in batches.
• If there are any errors with your file, Watershed will warn you of possible errors prior to uploading the file so you can make corrections before importing the data into your account.