Tab Separated Value survey structure

This feature is available starting in version 2.0

Note, this feature is also available for 1.92, but was added after the feature freeze, so is not part of the base code.  Expert users can add that functionality from GitHub

Excel Import and Export of Survey Structure

This feature is designed to make it easy to use Excel to author and edit surveys.  It completely eliminates the dependence upon SGQA codes.

Getting Started

The easiest way is to take an existing instrument and export it in Excel format.  Use the normal export survey button, and instead of selecting .lss format, select "LimeSurvey Excel survey file (*.xls)".  It will save as an Excel file in the proper format, with all the correct column headings.

Any spreadsheet software the supports Excel 2003 format is fine (e.g. OpenOffice or LibreOffice).  LimeSurvey ignores any formatting within the spreadsheet, but feel free to add some if it helps you.

There will be one row for each group, question, sub-question, and answer.  There are also rows for global survey variables, and for language-specific survey variables.  The primary language will be listed first, followed by any secondary languages.

Relationships are inferred by proximity.  So, questions following a group are part of that group; sub-questions following a question are part of that question, and answers following a question are part of that question.  Thus, you don't need to know the IDs (gid, qid, sqid) for any questions.  Those will be computed automatically upon import.

Tips

The goal of the Excel import/export is to let you rapidly design your survey using a spreadsheet.  We expect that you will frequently import the sheet, check its validity using the "Show Survey Logic"  feature, and test it.  Each time you import it, you will get a new survey.  So, you might end up with many partially developed surveys, but that is fine.  Just get in the habit of keeping track of which is the most recent, or delete the old one after you import the new ones.  Since you never use SGQA codes in the Excel, you never need to worry about what codes LimeSurvey assigns for the primary survey, group, question and answer keys.  So, feel free to import and export as often as you like.

Here are some convenient things you can do with this approach to authoring instruments:

1. Use same Answers for many questions.  Just copy the 'A' rows and paste after each question that should have the same set.
2. Use same sub-questions for many questions.  Just copy the 'SQ' rows and paste them after each question that needs it.
3. "Looping" - use same group many times.  After the group is the way you want it, copy it as many times as needed.  Use Excel filtering to view just the 'G' rows (for groups), and use the Excel column drag feature to update the relevance equations for each group (e.g. for a census, the first relevance might be "numPeople > 1", the next should be "numPeople > 2".  The drag feature will auto-update the number).  Filter by 'Q' rows and ensure that each question has a unique value (e.g. say you name your variables g1_q1, g1_q2, g1_qN, use find/replace to convert g1 to g2 the second group; g3 for the third, etc.).
4. Re-ordering questions/groups.  Simply re-order the rows of the Excel file.
5. Testing survey modules.  For long surveys, you may want to break up the testing into modules.  Simply create new Excel files for each module, deleting any rows that you don't need. This avoids the need to enter lots of data to test later sections of the survey.
6. Testing mandatory questions.  A common complaint is the need to make many questions mandatory, but the need to turn off the mandatory feature for testing.  Simply create the master Excel with mandatory set the the final desired values.  Then, to test it, just delete the "mandatory" column and save the test version of the Excel.  When you import that version, none of the questions will be mandatory.  After you have finished your testing, import the master copy.
7. Setting defaults.  Rather than using the GUI, you can enter any desired defaults in the default column.  This is especially helpful for cases where the GUI does not let you enter the desired value, like Expressions to set the default for list items (like populating a list from a token attribute).
8. Translation.  You can create copies of your Excel - one per language.  Include all the rows for the primary language, then copy and paste them below, and use drag to change the language field to the target language.  These can be distributed to your translators, and re-integrated into a single Excel file when they are done.
9. Bulk setting of advanced question attributes.  You may want all of your equations to start visible (so you can see their values as you collect data), but then hide them all before going to production.  Simply filter the Excel on class = 'Q' and question type = '*' (equation), and set always_hide to 1 for each of those questions.  Similarly, say after you create the survey, you decide which questions should appear in public statistics.  Rather than edit each question through the GUI, filter on class = 'Q', and set public_statistics = 1 for all of the questions that should be visible in statistics.
10. Find and replace.  Say you decide you need to change some phrasing across all of your questions, you can use Excel find and replace to make those changes.  Similarly, say you decide to do a bulk-renaming of your variables, find and replace can come to the rescue.  If you need regular-expression based find and replace, you can select the desired column, copy to a text editor, do your find and replace, and paste the column back into the Excel.
11. Gaining approvals.  If you are doing research, you may have an Institutional Review board who insists upon seeing the text of the questions.  This may be a convenient way to share it.  Similarly for discussions with a client.
12. Team consensus.  If you are trying to get a group to agree upon the wording or appearance of a question or group, you can rapidly prototype / edit the Excel, import it, and show the team (via question or group preview) exactly what the users will see.  That way you can get approval from the team before they leave the room rather than having to document requirements, build them, and get approval at future meetings.
13. Upgrading from other  survey formats.  If you have existing survey in XML, Word, or other format, you can create a translation process to map them to this format.  Although you could also try mapping to the .lss format, the advantage of this format is that it doesn't require you to keep track of foreign key relationships between groups, questions, sub-questions, answers, and defaults.

Limitations

1. By design, this feature only works properly for surveys that use qcode (rather than SGQA) naming.  This feature assumes that variable names (question identifiers) are unique throughout the survey. Sub-question names can be repeated, as long as they are unique within the scope of a particular question.
2. Currently, it does not import or export Conditions (since it assumes you'll be using relevance equations instead), Assessments (since it assumes you'll be using tailoring), or Quotas.  However, if there is enough demand, it would not be hard to add these.
3. Currently, the Excel export feature does not export defaults for list-type questions.

File  Format

General

We use the same set of column headings for multiple purposes.  The first 12 columns serve different purposes depending upon the type of entity (e.g. group, question, answer).  The remaining columns are an alphabetical list of the database field names for the advanced question codes.  Below is the syntax for each entity type

The first 12 columns are:

1. class
2. type/scale
3. name
4. relevance
5. text
6. help
7. language
8. validation
9. mandatory
10. other
11. default
12. same_default

Survey Global Parameters

There is one row per parameter in the surveys table.

1. class = 'S'
2. name => database field name
3. text => value

Survey Language-Specific Parameters

There is one row per field per language in the surveys_languagesettings table.  All entries for a given language are collected before doing the insert into that table.

1. class = 'SL'
2. name => database field name
3. text => value
4. language => language

Groups

There is one row per group.

1. class = 'G'
2. name => group_name -- the unique identifier for the group
3. relevance => grelevance -- the group-level relevance equation, without curly braces
4. text => description -- the language-specific description of the group
5. language => language -- the language for the group (e.g. 'en')

Questions

One row per question.  Questions are assumed to belong to the group that precedes them.

1. class = 'Q'
2. type/scale => type -- the (usually one letter) question type (e.g. 'M' is Multiple Choice)
3. name => title -- the unique question name (the root of the qcode naming system)
4. relevance => relevance -- the #Access_to_Variables|relevance equation  for the question
5. text => question -- the language-specific text of the question
6. help => help -- the language-specific help text
7. language => language -- the language for the group (e.g. 'en')
8. validation => preg -- the optional regular expression validation criteria for the question
9. mandatory => mandatory -- 'Y' if mandatory
10. other => other -- 'Y' if the "Other" option should be available (only for some question types)
11. default => default -- if set, this value is inserted into the defaultvalues table for this question
12. same_default => same_default -- 'Y' for true, in which case any defaultvalue set for primary language applies to other languages

Sub-Questions

One row per sub-question.  Sub-questions are assumed to belong to the question that precedes them.

1. class = 'SQ'
2. type/scale => scale_id -- 0 or 1, depending upon question type (e.g. array text will have two scales)
3. name => title -- the "name" of the sub-question, e.g. the one used for exclude_all_others
4. relevance => relevance -- (Future) to support sub-question-level relevance
5. text => question -- the language-specific text of the sub-question
6. help => help -- (Future) to support sub-question-level help
7. language => language -- the language for the sub-question
8. validation => preg -- (Future) to support sub-question-level regular expression validation (e.g. for address parts)
9. mandatory => mandatory -- (Future) to support sub-question-level mandatory (e.g. make only a few sub-questions mandatory)
10. default => default -- if set, then this is the default value for the sub-question (inserted into defaultvalues table)
11. same_default => same_default -- if set, then the default for the primary language is  used for all other languages

Answers

One row per answer.  Answers are assumed to belong to the question that precedes them, and be in the desired sort order.

1. class = 'A'
2. type/scale => scale_id -- 0 or 1 (e.g. for dual-scale)
3. name => code -- the unique answer identifier
4. relevance => assessment_value -- if using assessment option, this is the assessment value for the answer
5. text => answer -- the language-specific text of the answer
6. language => language -- the language for this answer (e.g. 'en')

Examples

Relevance, Tailoring, and Equations

#Relevance Tailoring and Equations|Discussion and Screen Shots

ls2_em_tailoring.xls

Sample Census

#Sample_Census|Discussion and Screen Shots

ls2_group_relevance.xls

Cascading Array Filters

#Cascading Array Filters|Discussion and Screen Shots

ls2_cascading_array_filter.xls

Traditional Validation, Re-envisioned

Discussion and Screen Shots

ls2_validation_tests.xls

Randomization Groups

#Randomization Groups|Discussion and Screen Shots

Randomization_Group_Test.xls