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:
- Export the test from CLS (You'll need 'Design' permissions)
- Convert the file to excel
- You then need to run some SQL script over the file, (Provided below)
- 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
- 'Design' permissions in the CLS so that you can access and export the exam
- Microsoft Excel or some other spreadsheet program
- Microsoft SQL Studio express or some other SQL management tool
- Some basic Excel Skills
- Some basic SQL/Database management skills
Step 1 - Export it from CLS
- Login to your CLS site with 'Design' permissions or above
- Got to Design > Tests and Surveys
- Find your test and select it
- From the 'Actions' menu, select 'Export Test'
- This will download a ZIP file onto you computer
Step 2 - Convert the file to Excel
- Open the ZIP file in Explore
- In the root of the ZIP is a file called 'Objects.xml' save this to your computer
- Open the file in a text editor and change the UTF setting in the top line "encoding=utf-16" to encoding="utf-8"
- Save the file with the same file name, and then open it excel
- Excel will prompt you select the XML method, choose 'As an XML Table'
- Click ok on the Schema warning
- Save the file as an excel spreadsheet
Step 3 - Run the SQL script over the file
- 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
- Download and install SQL Server 2017 Express edition
- Download and install SQL Server Management Studio (SSMS)
- Import the spreadsheet as a database table and name the table' Questions'
- Rename any sections details you want to exclude at the top
- Execute the script, and you should get a table at the bottom
- Right click the square at the top left of the grid, or hit 'CTRL' + 'SHIFT' + 'C' to copy the results with the header labels
- Copy back to a clean excel spreadsheet
- 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 */
Select Identifier211 as 'Sections', name210 as SectionNeme, item223 as QuestionLink
-- 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
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
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
WHERE [unique-id5] IS NOT NULL
/* BUILD THE FIRST QUERY to get the section questions SECTIONS --> QUESTIONS */
Select DISTINCT Sections,SectionNeme,QuestionLink,QuestionTextLink
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
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
INNER JOIN CTE_Distractors On DistractorBaseID = DistractorBaseLinkID
/* THE GRAND FINALE*/
Sales star Genius Branch Manager