Word Doc to CSV Format
This article will provide some tips on converting your questions from a Word document to a csv friendly format
Last updated
This article will provide some tips on converting your questions from a Word document to a csv friendly format
Last updated
If you already have your questions written in a Word or other text document, you will need to either copy and paste them into the quiz directly, or into the csv importer template and bulk upload them to the platform. This article will provide a broad step by step guide on this second option as well as some tips on how to speed up the process.
Here is an example of a Word-style document containing 20 multiple choice questions and their answers - Questions and Answers Example Doc. Our goal is to get the data here onto a worksheet in the format used by the on-platform importer. You can download this template from any quiz's Import page, it is also linked here - Importer Template
Whilst the exact format of the document containing your questions may differ slightly, you should still be able to apply some of the tips in this article to help you speed up the conversion process.
This is guide is based on using Google Sheets. If you are using Microsoft Excel you might find that some of the functions and formulae are slightly different.
MCQs with a short stem and short answer options are the easiest to convert as each bit of data (question stem and answer options) can fit onto one line each in your Word doc and are therefore easy to get onto one row each on spreadsheet. Once we have each bit of data onto its own row, converting it into the importer format is a lot easier.
Your first step is to copy the text from the Word document into the worksheet. It is best to do this all at once rather than one question at a time so you apply bulk operations / formula to all questions at the same time.
Paste the questions onto a 'Scratch Pad' worksheet tab which sits alongside your importer template so you can finalise the format before adding it onto the import sheet
Select a cell with a single click, if you double click a cell and see the flashing '|' cursor in the cell all the text will go into one cell which is not what you want
After you paste the text in you will end up with something like the below image, with all the different lines of text on different rows separated by some blank rows
Your first bit of formatting will be to remove the 'Blank' rows. See the video below on how to filter to the blank rows. Once you have done this, select the top blank row, hold 'Cmd' + 'Shift' (Mac) / 'Ctrl' + 'Shift' (Windows) and press the down arrow to select all the blank rows then right click and Delete Selected Rows.
Your next step is to move the answer options from being listed below the question stem, to being in the columns alongside the question stem. To do this you will use the 'TRANSPOSE' function on Google Sheets. Firstly, apply this to Question 1 and and its answers.
This only works when all questions have the same number of answer options. See the end of the article for a method if you have quizzes where questions have different numbers of answer options
You can now apply that formulae to all the questions. Firstly you need to filter column A to 'Filter by Condition > Text Contains 'Question' so we only transpose the answer option rows next to the question cells.
You will notice in the above video that there is an error after fill handling the TRANSPOSE formulae to all the questions. Option D for question 14 is the question stem for question 15.
The reason this has been left in is to demonstrate the benefit of keeping the labels for content ('Question 15' and 'a)','b)' etc) even if you will ultimately be taking them out before uploading to the platform. Until you are happy with the final format of the questions these labels make it very easy to identify and correct errors when you are dealing with large data sets. Without those labels it would be tricky to see what was a question and what was an answer option.
Your sheet should now resemble the format needed. At this stage it is worth locking in the values of the cells so any changes we make (for example correcting the mistake above) do not interfere with our TRANSPOSE formulae. To do this, we will select all the values in the filtered view, copy and then paste as values. This replaces the formulae in the cells with what the formulae is pulling in (i.e. the answer option). As our columns now match those of the question and answer options on the importer sheet we can move them onto that sheet
We can use the answer option labels to make sure all the values are in the correct columns in the next step
In this example I have indicated a correct answer by adding '- Correct' after that option but you can replace that with whatever you have used to indicate a correct answer. Start by filtering each answer option column to 'Text Contains '- Correct'. Do this for each answer option column, adding the corresponding letter (A - D) into the Correct Answer Option column as you go. To make this step easier you can move the Correct Options column next to the answer option columns, the order of the columns does not matter as long as the column headers are correct.
After checking all the data are in the correct columns you can remove the labels and any phrases used on physical exam papers that are not applicable to an online platform. For example ''Tick the correct answer" could either be removed or replaced with something more applicable like 'Select the correct answer from the list below'. In either case you can use the 'Cmd' + 'F' (Mac) / 'Ctrl' + 'F' (Windows) function in Google Sheets to help you identify instances of this phrase. Click the 3 dots icon then use 'Find and Replace' to remove it or change it. To remove a phrase just leave the 'Replace with' box blank
Now that you have the question specific data added in you can start to add fill out the rest of the which is the same for more than one question. You can find information on those fields in the next article
Remember to add EMQ's and Sections to your quiz first, then add the relevant id's for them to your question csv before importing the questions
Depending on the format of your Word Doc you might find that you need do some additional formatting to get the questions to a point where they are accepted by the importer. Below are some tips on dealing with some of the most common of these
If your question goes over more than one line in your Word doc, it will map onto separate rows in Google Sheets, to use the method outlined above we need the whole question to be on one row.
To make it easier to manage you can filter all of the answer options out so you’re left with just the questions. Do this by selecting the column A header and selecting the filter icon. Filter by condition should be ‘text does not contain’ and a common character in your answer options. In this example we are using the right bracket ‘)’.
Once you have a filtered list of the question content in column A, copy and paste them into column B, ensuring there is one complete question per row (you should see the flashing '|' before pasting to ensure everything goes into one cell)
Once you have completed this for each question, switch the filter around so you view just the cells with 'a)','b)' etc in. Add a formulae in Column B which is '=A7' so you are mirroring whatever is in column A into the next column. Fill handle this down for all the answer options. You can now turn off the filter.
Select the whole of Column B, copy and paste as values. Check over the questions and answer options in Column B to ensure everything has carried over correctly from Column A.
Once you're happy, delete Column A. From here you can repeat the steps outlined above (from Removing Blank Rows onwards) as you now have each piece of the question (stem and answer options) on their own row.
You can filter by text colour in the same way you can filter by Text Contains. You can do the same if the correct answer option is highlighted in a different colours
We recommend adding tables and images in after importing the text content of your questions. It is much easier to format them within the questions and visualise how they will appear to users on the Quiz Editor than using html tags on the csv.
There is a manual step needed here to get the data to a point where you can complete the steps outlined above. Once you have completed the 'Remove Blank Rows' step, check what the highest number of answer options a question has is, then add empty rows below the existing question option rows to ensure that all questions have that number of rows between each question stem. You should then be able to complete the 'TRANSPOSE' step as normal with any questions that have fewer answer options simply having blank cells for the higher answer option columns.
