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

WITH 
    /* Select the STRUCTURE of the exam questions */
    CTE_Structure (Sections,SectionNeme,QuestionLink)
    AS
    (
    Select Identifier211 as 'Sections', name210 as SectionNeme, item223 as QuestionLink
    From Questions
    Where 
    -- 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)
    AS
    (
    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)
    As 
        (
        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)
    As
    (    
    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)
    AS
    (
    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 )
    AS
    (
    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 )
    As
    (
        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