Canopi Forums
PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 04 Feb 2018 11:26 AM by Greg McLoughlin-Wilden
Janison CLS: Exporting Exam test questions Hack
�0 Replies
You are not authorized to post a reply.
Author Messages
Greg McLoughlin-Wilden
New Member
New Member

04 Feb 2018 11:26 AM

    Exporting Exams questions

    So you've always wanted to dump last years questions out of our LMS and get them into a spreadsheet so that you can review them for next year.

    Sadly many LMS's just don't have a 'Export now' function, especially if they are high stakes exam. So here is a hack to get the Janison CLS test questions and distractors into a spreadsheet. 

    And when I say Hack, I don't mean it's insecure (it is), what I mean is that you have to do a bit of hard work.

    So there are four basic steps:

    1. Export the test from CLS (You'll need 'Design' permissions)
    2. Convert the file to excel
    3. You then need to run some SQL script over the file, (Provided below)
    4. Massage the SQL Results in Excel

    I've only tested this on an exam that has sections, but it should work on one without as well.

    What you'll need

    1. 'Design' permissions in the CLS so that you can access and export the exam
    2. Microsoft Excel or some other spreadsheet program
    3. Microsoft SQL Studio express or some other SQL management tool
    4. Some basic Excel Skills
    5. Some basic SQL/Database management skills

    Step 1 - Export it from CLS

    1. Login to your CLS site with 'Design' permissions or above
    2. Got to Design > Tests and Surveys
    3. Find your test and select it
    4. From the 'Actions' menu, select 'Export Test'
    5. This will download a ZIP file onto you computer

    Step 2 - Convert the file to Excel

    1. Open the ZIP file in Explore
    2. In the root of the ZIP is a file called 'Objects.xml' save this to your computer
    3. Open the file in a text editor and change the UTF setting in the top line "encoding=utf-16" to encoding="utf-8"
    4. Save the file with the same file name, and then open it excel
    5. Excel will prompt you select the XML method, choose 'As an XML Table'
    6. Click ok on the Schema warning
    7. Save the file as an excel spreadsheet

    Step 3 - Run the SQL script over the file

    1. Open the file in your prefered SQL tool. I prefer Microsoft SQL Server Studio Express, if you don't have it then follow the next steps
      1. Download and install SQL Server 2017 Express edition
      2. Download and install SQL Server Management Studio (SSMS) 
    2. Import the spreadsheet as a database table and name the table' Questions'
    3. Rename any sections details you want to exclude at the top
    4. Execute the script, and you should get a table at the bottom
    5. Right click the square at the top left of the grid, or hit 'CTRL' + 'SHIFT' + 'C'  to copy the results with the header labels
    6. Copy back to a clean excel spreadsheet
    7. Ta Da, you're done. Delete any columns you don't want and away you go.

    If you need help

    This is a bit geeky, but I'd encourage you to give it a go, it is good fun. 

    If it does all get all to much, log a ticket with support , and we'll get you sorted, make sure to include your spreadsheet file.


    The SQL Script

        /* Select the STRUCTURE of the exam questions */
        CTE_Structure (Sections,SectionNeme,QuestionLink)
        Select Identifier211 as 'Sections', name210 as SectionNeme, item223 as QuestionLink
        From Questions
        -- Exclude any sections you don't want like the introduction and exit sections
        -- Make sure you edit these to your Section idenifiers

        Identifier211 <> 'AT2AC0021' and
        Identifier211 <> 'AT2AC0022' and
        Identifier211 <> 'AT2AC0000'
        /* Select the SECTION questions*/
        CTE_Sections (QuestionLinkID, QuestionTextLink)
        Select [unique-id195] as QuestionLinkID, Question203 AS QuestionTextLink
        From Questions
        WHERE [unique-id195] is not null

        /* Select the QUESTION Details and their Distractor link*/
        CTE_Questions (QuestionTextLinkID, QuestionID, QuestionGUID, QuestionText, DistractorBaseID)
            Select [unique-id123] AS QuestionTextLinkID, Identifier132 AS QuestionID, [Original-id124] AS QuestionGUID, QuestionText As QuestionText, [Item154] AS DistractorBaseID
            FROM Questions
            WHERE [unique-id123] IS NOT NULL
        /* Select the DISTRACTORS details */
        CTE_Distractors (DistractorBaseLinkID, DistractorText, IsCorrect)
        SELECT [unique-id5] AS DistractorBaseLinkID, Text As DistractorText, IsCorrectAnswer AS IsCorrect
        FROM Questions
        WHERE [unique-id5] IS NOT NULL

        /* BUILD THE FIRST QUERY to get the section questions SECTIONS --> QUESTIONS */
        CTE_SectionQuestionLink (Sections,SectionNeme,QuestionLink,QuestionTextLink)
        Select DISTINCT Sections,SectionNeme,QuestionLink,QuestionTextLink
        from CTE_Structure
        Inner Join CTE_Sections ON QuestionLInk = QuestionLinkID
        /* Build the Second Query QUESTIONS --> DISTRACTORS */
        CTE_QuestionDistractors (Sections,SectionNeme,QuestionLink,QuestionID,QuestionTextLink, QuestionText,DistractorBaseID )
        Select Sections,SectionNeme,QuestionLink,QuestionID,QuestionTextLink, QuestionText,DistractorBaseID
        From CTE_SectionQuestionLink
        Inner Join CTE_Questions ON QuestionTextLink = QuestionTextLinkID

        CTE_DistractorFinal (Sections,SectionNeme,QuestionLink,QuestionID,QuestionTextLink, QuestionText,DistractorBaseID, DistractorText, IsCorrect )
            Select Sections,SectionNeme,QuestionLink,QuestionID,QuestionTextLink, QuestionText,DistractorBaseID, DistractorText, IsCorrect
            From CTE_QuestionDistractors
            INNER JOIN CTE_Distractors On DistractorBaseID = DistractorBaseLinkID

        /* THE GRAND FINALE*/
        Select  *
        From CTE_DistractorFinal 

    Sales star Genius Branch Manager 


    You are not authorized to post a reply.

    Register for more features

    Register now to post your comments.

    If you Register on the Canopi site you can post questions and comments to the forum.