Novi Survey Home
Import options for a multiple choice question from Excel
Previous Topic  Next Topic 

Options from a multiple choice question can be imported from an Excel spreadsheet. Note that this feature is available to customers installing the software on their own server. It is not available to subscribers to the On-Demand service.

Given a workbook 'Book1.xlsx' containing a spreadsheet 'Sheet1'

The values 'red', 'green', and 'blue' can be imported as possible choice in a multiple choice question.

For each value to import, the spreadsheet needs to contains a unique key used to track the response. In the example above, the key is in column A. Both the keys and values must be textual values (in the example above, the data for cells in column A are '1, '2, and '3 to indicate to Excel that the values of textual and not numeric).

The connection string to the spreadsheet to enter in the screen for the multiple choice question is:

  • For excel 2007 or greater: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Book1.xlsx;Extended Properties="Excel 12.0;HDR=No";

  • For excel 2003: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Book1.xlsx;Extended Properties="Excel 8.0;HDR=No";

These connection string examples assume that the Excel file is in directory 'C:\Temp'. The Excel file must be available from the server running the software (i.e., the server must be able to read the file at the specified path). The easiest way to make the file available to the server is the place the file in a directory on the server.

The SQL for fetching the data should be formatted as follows:

select f1 as answerKey, f2 as answerValue_enUS from [Sheet1$]

Here 'Sheet1' is the name of the worksheet containing the data in the workbook. 'f1' corresponds to column A and 'f2' corresponds to column B. 'answerKey' and 'answerValue_enUS' are required to track the data in the import. See Multiple choice question for more details.


After import, the screen shows the following data:

See also: Multiple choice question