Content Authoring Pilot - Exporting data from Learning Locker and working with the data in Excel

This document covers basic information on Learning Locker for the content authoring pilot.

Learning Locker is an open source learning record store designed to store learning activity statements generated by xAPI compliant learning modules. To login to Learning Locker go here:  If you are a School, College, or Department instructional technology consultant who would like an account to login to Learning Locker to generate reports for instructors in your School, College, or Department please e-mail to request an account.

Learning Locker documentation can be found here:

Creating Exports in Learning Locker

For the pilot the primary way to get data out of Learning Locker is to use the export function. Future work may focus around reports.

  1. Login to Learning Locker.
  2. Click on UW Madison LRS Pilot.
  3. Click Exporting on the left side menu.
  4. Click Edit next to either Storyline Simplified Export, Pressbooks Simplified Export or Captivate Simplified Export [Note that the Captivate report was not available when this document was written]
  5. Click the Download dropdown and select .CSV  Name the CSV something that will make it easily findable in the future.

Working with Your CSV File in Excel

  1. Open the .CSV in Excel.
  2. Turn on the filters by clicking Data tab and then on the Filter.
  3. Filter the object ID column with the Activity ID which can be found by Editing the xAPI Content post in the pilot WordPress instance.
    This is where the Activity ID can be found in the xAPI content post in the pilot WordPress instance (click for a larger image):

  4. Set up your Filter to Text Filter > Contains “Activity ID”:

  5. Insert a column between result duration and result response and title it Answer Translation.
  6. Open the .xml file in Excel and select Open XML “As an XML table”. Click OK.
  7. Click OK through the warning.
  8. Back in the Learning Locker export, add the following formula to any field in the Answer Translation column next to where there is a response other than null in the Result Response column.
  9. Enter a VLookup formula w/ the following criteria

    * Lookup_value = result response cell you are trying to translate
    * Table_array = the columns from the .xml spreadsheet which contains the ID value you are trying to translate in the first column of your selection through the column where the answer translation resides
    * Col_index_num = The column number in your selection from above where the answer translation resides
    * Range_lookup = false
  10. Click OK.
  11. Copy the function into any other cell that needs it. The formula should update automatically.
  12. Copy the entire answer translation column and paste it as “value” only. This will prevent the spreadsheet from needing to reference the .xml spreadsheet for the translation.
  13. Save as an .XLS workbook.

See Also:

Keywords:content, authoring, pilot,learning, locker, record, store, xapi, statements, wordpress, grassblade, csv, excel   Doc ID:76536
Owner:Dan L.Group:Academic Technology Pilots
Created:2017-09-14 07:17 CDTUpdated:2018-04-20 13:51 CDT
Sites:Academic Technology Pilots, DoIT Help Desk, Learn@UW Madison
Feedback:  0   0