Can we import a Quiz from Microsoft Excel ?

Can we import a Quiz from Microsoft Excel ?

by Vijay Reddy -
Number of replies: 21

Hello,

I am new to Moodle. I am trying to use an existing Quiz in Excel format. I have one question per row with the following columns:

1. Question Id 

2. Question Text

3. Choice 1

4. Choice 2

5. Choice 3

6. Choice 4

7. Correct Answer (1, 2, 3, or 4)

8. Explanation (feedback on the response)

Please help me with importing some existing questions in Microsoft Excel 2003 with thye above structure into Moodle Quiz Module.

Thanks a lot in adance!

Average of ratings: -
In reply to Vijay Reddy

Re: Can we import a Quiz from Microsoft Excel ?

by Peter Ruthven-Stuart -
Immàgine de Plugin developers
Victor,

Have a look at the help file titled "Importing GIFT format files".

Normally, one would use a text file to make GIFT formatted questions, however it is possible to use Excel. In fact if you are making a lot of questions of the same format it is quicker to make the questions in Excel.

Once you read the above mentioned GIFT help file, it should be apparent how to use Excel for this purpose.

In reply to Peter Ruthven-Stuart

Re: Can we import a Quiz from Microsoft Excel ?

by Devyani Vinchurkar -
Hello Victor,
Have you found the solution for this? I also need to import data from excel file and stuck.
How to use GIFT to import Excel file?
Please let me know.
Regards
Devyani

In reply to Vijay Reddy

Re: Can we import a Quiz from Microsoft Excel ?

by Howard Miller -
Immàgine de Core developers Immàgine de Documentation writers Immàgine de Particularly helpful Moodlers Immàgine de Peer reviewers Immàgine de Plugin developers
Somebody recently posted about an Excel import routine that they had written but I don't think we ever saw any code.

There are a couple of formats useful for importing into Moodle - GIFT and Moodle XML. There have been some macros written to convert from Word files to both formats but I am not aware of any for Excel. Perhaps there's a VB guru out there that fancies a challenge??
In reply to Howard Miller

Re: Can we import a Quiz from Microsoft Excel ?

by Jeff Forssell -
In this thread were several examples of using Excel to create CLOZE quiz questions (of numerical type).   Maybe you could get some inspiration there.

I suspect the content of the columns should be complemented to really use the possibilities. FOr example that each row should have question type O or [_] and each alternative should have its fields for points and specific feedback.
In reply to Vijay Reddy

Re: Can we import a Quiz from Microsoft Excel ?

by Olga Forlani -

Hi Victor,
to import quiz from an Excel File, I use the Gift Format, and this is an example of my excel file. Every question has to start with "::" and finish with "::", the possible answers have to be included by {....} and every answer has to be preceeded by "~" if wrong, and "=", if right. By using the Excel function "Concatenate" you can include in the same cell the content of every single cell and you are done smile

Attachment excel.png
Average of ratings:Useful (2)
In reply to Olga Forlani

Re: Can we import a Quiz from Microsoft Excel ?

by A. T. Wyatt -
What a neat trick!  THanks for sharing!

atw
Average of ratings:Useful (1)
In reply to A. T. Wyatt

Re: Can we import a Quiz from Microsoft Excel ?

by A. T. Wyatt -
Just in case it helps anyone, here is a spreadsheet that will might help you get started.

This one is set up for 4 distractors, multiple choice. And since there are no wrong answers, I am really going to set all my distractors to "=", not the "~" which signifies an incorrect response.

Tricks--
  • look at the concatenate formula
  • you have to get a few spaces in there
  • you have to make the "=" a character
  • skip a row between items
It works when you copy and paste the generated data into a text editor, save the file, and import in the quiz editor. This is not meant to allow you to upload the spreadsheet itself.

atw
In reply to A. T. Wyatt

Re: Can we import a Quiz from Microsoft Excel ?

by Timothy Takemoto -
Thank you Olga and A.T> Wyatt,
And there was I hoping that this feature would be incorporated when it was there all along.

I think that if someone knows the exel special character for return then we can obviate the need to leave blank lines by adding "^p" at the end of that concatenate. Ah, it is CHAR(10).

But using

=CONCATENATE(A5,B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5,N5,CHAR(10))

results in there being double quotes around each line when I paste to a text file.
In reply to Timothy Takemoto

Re: Can we import a Quiz from Microsoft Excel ?

by Timothy Takemoto -

I stil have not worked out why having a line feed at the end should add quotes around the concatenated string. I am asking on experts exchange.
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21840323.html

But even if it means globally replacing " with zilch I think that I prefer it, especialy when we remove the grey columns, adding the control characters in the concatenate which becomes

=CONCATENATE("::",A2,"::",B2,"{",CHAR(13),CHAR(10),"=",C2,CHAR(13),CHAR(10),"~",D2,CHAR(13),CHAR(10),"~",E2,CHAR(13),CHAR(10),"~",F2,CHAR(13),CHAR(10),"}",CHAR(13),CHAR(10))

Additionally, as far as I know, feedback screens in moodle still display the answers (A,B,C,D) in the same order that they appear in the GIFT file. Hence if the correct answers is always the first answer then students will be able to get the correct answer on the second attempt. So introducting four IF statements and a random number will put the right answer in a random position.

Please see attached.

In reply to Timothy Takemoto

Re: Can we import a Quiz from Microsoft Excel ?

by Timothy Takemoto -

Please see attached "gift_maker" an Excel file to make GIFT text files to import questions to Moodle.  It can make up to 100 questions at a time. It does not use a macro

All you need to do is enter the questions and answers

  • Does NOT require the user to copy any rows prior to writing the questions
  • The question name defaults to the question itself (as does the GIFT parser)
  • Optional generic feed back is added to each answer
  • One only has to copy one cell, which contains everything
  • You can also save the file as a text file if you prefer.
  • The correct answer is in a random position of a,b,c,d
  • The answers are on a seperate line in the GIFT file, making it easier to add custom feedback later.

But, even the combined Excel experts do not seem to be able to tell me why there are quotes at the beginning and end, so at the moment one still has to remove these by hand.

Thanks again to Olga Forlani and A.T Wyatt for this neat idea.

Tim

Average of ratings:Useful (1)
In reply to Timothy Takemoto

Re: Can we import a Quiz from Microsoft Excel ?

by Mehjabeen Leghari -
Gift maker is usefull but tell us the way how to corporate negative marking in this it only works for positive marks.
In reply to Timothy Takemoto

Re: Can we import a Quiz from Microsoft Excel ?

by Katie Fraser -

Hello, I imported the gift_maker Excel file successfully into my Moodle 2.0 quiz module using only the two sample questions in the file.  However, when I added my own questions and tried to upload it, I got the error message below.

 

 

 

 

 

 

Can anyone tell me what I'm doing wrong?

 Many thanks!
 

 
 
In reply to Timothy Takemoto

Re: Can we import a Quiz from Microsoft Excel ?

by Dom London -

Hi Tim

I am genuinely grateful for the intention (as we'd all like a simpler way of getting a quicker way to enter loads of questions) But like a few others here I too got nowhere fast, dashed hopes


 got a whole rake of errors - see below

Any suggestions welcome. Im surprised no one has created some tools to make this less fiddly  more reliable.

 

Best

Dom

Error importing question
What is the French for dog?{
Could not find {...} around answers
Error importing question
}
Could not find {...} around answers
Error importing question
What is Stanley Milgram famous for?{
Could not find {...} around answers
Error importing question
}
Could not find {...} around answers
Importing 8 questions from file
Error(s) found parsing the import file. No questions have been imported. To import any good questions try again setting 'Stop on error' to 'No'
In reply to Olga Forlani

Re: Can we import a Quiz from Microsoft Excel ?

by Dennis Daniels -
Sweet!
In reply to Dennis Daniels

Re: Can we import a Quiz from Microsoft Excel ?

by Olga Forlani -

Great smile

Olga

In reply to Olga Forlani

Re: Can we import a Quiz from Microsoft Excel ?

by Devyani Vinchurkar -
Hi Tim,
I downloaded the gift_maker.xls from your site. But when i press "Make Gift File" button or "Clear questions" button, nothing happens.
I have enabled macros also.
Please help!
Devyani

In reply to Olga Forlani

Re: Can we import a Quiz from Microsoft Excel ?

by Muddasar Amin -

Hi,

I have tried this import a quiz in moodle 2.4, but I am successful. Could you please elaborate more than this?

Regards.

In reply to Vijay Reddy

Re: Can we import a Quiz from Microsoft Excel ?

by Josh Bruck -

I know this subject is over ten years old, but I am working on a GIFT Format Toll in Excel that can be used to copy and past into notepad and saved.

This one will only work with single answer multiple choice questions. but you can save questions into multiple categories in the spreadsheet and it will format the questions and categories into one sheet.

I am going to create one for multiple answer multiple choice soon.

One file includes spaces for feedback, and one does not.

Average of ratings:Useful (1)