View Issue Details

IDProjectCategoryView StatusLast Update
05105User patchesImport/Exportpublic2012-06-21 13:23
ReporterTMSWhite Assigned ToTMSWhite  
Status closedResolutionfixed 
Product Version1.91RC5 
Target VersionFixed in Version2.00b 
Summary05105: Import Survey definitions from Excel

I have research colleagues and epidemiologists who would like to create their surveys in Excel (with one row per question), and then import the final product into the survey tool. This is especially helpful for very long surveys, where Excel can be used to easily re-order questions, or common answer-lists can be re-used across blocks of questions.

This strategy has worked for me in the past for a different survey tool (Dialogix). One of LimeSurvey's "competitors", REDCap, also uses an Excel import structure, so it is definitely possible to do this. The challenge is agreeing upon the syntax and semantics within Excel.

In my case, I used 9 columns, with one row per Question/Item
(1) Concept - optional label of what the item was supposed to measure
(2) InternalName - the variable name (might be restricted to 8 or 12 characters, depending upon target statistical package)
(3) DisplayName - a more meaningful name for users
(4) Relevance - the conditions under which the question should be asked - processed by the equation parser and returns Boolean
(5) QuestionType - type of question or Evaluation
(6) ValidationCriteria - datatype,min,max,formatMask,extraAllowableValues
(7) Readback - optional text to help user remember the question - so, rather than, "when we asked you {X}"?, we'd display "When we asked about {X.readback}", you said "{X.answer}".
(8) QuestionOrEvaluationText - this is the text (with optional embedded HTML) that is parsed by the Equation Parser to either form a string to diplay, or compute the result of an Evaluation and store it in a variable.
(9) AnswerChoices - optional enumerated list of answer options (if QuestionType requires such an enumerated list)

LimeSurvey should be able to use a similar approach, but would need more columns to support the advanced option types for each question type.

I found that my users did not mind having a different syntax for each question type, as long as the InstrumentLoad function flagged any errors for them. That let the whole survey be compact and easier to read (rather than having 30+ sparsely filled columns and constantly having to horizontally scroll back and forth as you scrolled down to change data types).

As reference, I'm attaching the Dialogix instruction manual, which shows how it specified its Excel import structure. This just meant to be food for thought - the manual hasn't been updated since 2003, and Dialogix never included Matrix-style questions, so LimeSurvey would need a somewhat different set of columns within such an Excel import format.

Separately, I'll attach the Excel import schema for REDCap if there are no intellectual property restrictions on doing so.

Also note, this approach to importing surveys assumes the existance of an embedded equation parser (see Issue#05103), and a question type for Evaluations (see Issue#05104). This way the Excel could be internally consistent and let users refer to variables by the InternalName (e.g. in Conditions/Relevance, and Piping/Tailoring) rather than needing to adjust all of those references to SGQA notation after loading the instrument.

TagsNo tags attached.
Complete LimeSurvey version number (& build)9992

Issue History

Date Modified Username Field Change
2011-04-11 21:17 TMSWhite New Issue
2011-04-11 21:17 TMSWhite File Added: Dialogix_UserManual_2003.htm
2011-05-07 11:58 c_schmitz Status new => acknowledged
2012-03-21 05:57 TMSWhite Note Added: 18020
2012-03-21 05:57 TMSWhite Status acknowledged => resolved
2012-03-21 05:57 TMSWhite Fixed in Version => 2.00b
2012-03-21 05:57 TMSWhite Resolution open => fixed
2012-03-21 05:57 TMSWhite Assigned To => TMSWhite
2012-06-21 13:23 c_schmitz Status resolved => closed