Tip: Any global settings applied in the "Project Settings" window will override settings specified in the Excel file (e.g., question instructions, mandatory vs. optional).
1: Starting the File
Watch the "Excel File Setup" Video
Duration: 5 minutes
The import document is a 2003 Microsoft Excel based file (it must have a .xls extension, rather than the 2007 .xlsx version). The survey importer only reads in the first worksheet in your Excel file, allowing you to keep any notes or additional content in subsequent worksheets within your file.
The excel sheet must have a file header to signify the start of a new import document, as shown below:
| A | |
|---|---|
| 1 | #defaultBlanks=True version=2.0 |
1.1: Explanation of header line:
- #defaultBlanks=False - If set to False, it is mandatory to include labels for each question, row, column, etc. If set to True, the importer will generate labels for you; but can be overwritten using the label column.
- version=X.X - Version number creates the compatibility level for newly added feature, preventing breakage in existing features when new capabilities are added. The current version=2.0.
1.2: Defining the column headers:
There are four column headers that can be used to define and format the survey content in the Excel file. Each column header needs to be the first entry of a column, and should be specified on row 2 of your Excel file. The column headers are outlined below. Note that only 'type' and 'text' are mandatory, unless defaultBlanks is set to False (in which case 'label' would also be mandatory).
Below is a table outlining each of the four available column headers:
| Column Header | Is it required? | Details |
|---|---|---|
| type | yes | Determines the primary element that is to be created by the system, such as question type, term, break,skip, etc. |
| label | no | Used to define the label numbers for each comment, question, row, etc. |
| value | no | Override the default value of a response, primarily used for rating questions when statistics are needed. |
| text | yes | Used to specify the text for the survey questions, rows, columns, etc. |
Below is row 2 of the Excel sheet, depicting an Import file using 'type', 'label' and 'text':
| 2 | type | label | text |
|---|
1.3: Naming the survey:
The last mandatory line within your file is the survey is setting the survey name. Under the 'type' column header enter 'survey' in the 3rd row. Then you can specify a custom name for your survey under the 'text' column header. This should be specified in row 3, as shown below in the example. The text field is the only mandatory field for survey.
| 2 | type | label | text |
|---|---|---|---|
| 3 | survey | My Survey Name |
1.4: Example of working document:
Below is an example using the minimal required columns (type and text) for importing your file:
Below is an expanded version for importing:
Note that each column, A through C, has a column header at line 2 setting up type, label and text for use in the Excel file.
2: Explanation of the content of each of the column headers
The survey elements are the meat of the Excel document, allowing you to markup your survey for importing. This includes the actual content of your survey as well.
2.1: Type
Type are those elements available within Builder, including the items in the 'basic elements' and 'structural elements' menus, along with page breaks. These are the primary functions within a survey. The table below breaks out each available option.
| type | supported column headers | details |
|---|---|---|
| break | None | adds a page break (results in a continue button). |
| checkbox | label, text | creates a multi-select question. |
| comment | label, text | adds a standard comment box. |
| number | label, text | creates a number question. |
| radio | label, text | creates a single select question. |
| section | label, text | creates a section header, not shown in the survey. |
| select | label, text | creates a drop down question. |
| term | label, text | creates a terminate element |
| text | label, text | creates a text question. |
Type modifiers. These setup the additional components within questions.
| type | supported column headers | details |
|---|---|---|
| r | label, text | creates a new row |
| c | label, text | creates a new column |
| ch | label, text | creates a new choice drop down |
| sub | text | overrides the default instruction (NOTE: sub must precede any other sub type in each question.) |
2.2: Label
The label is auto generated by default when set to 'True' in the file header. However, you have the ability to overwrite the default using the label column. Often we want the labels to track back to the original questionnaire, in which case you can specify each label as needed.
If set to True, the importer will generate labels for you, using the below defaults:
- Questions: q1, q2, q3, etc.
- Rows: r1, r2, r3, etc.
- Columns: c1, c2, c3, etc.
- Choices: ch1, ch2, ch3, etc.
Labels can be alpha-numeric, but must start with a letter. Below in red is an example of a question label and row labels.
| 2 | type | label | text |
|---|---|---|---|
| 4 | radio | q3 | What is your gender? |
| 5 | r | r1 | male |
| 6 | r | r2 | female |
2.3: Text
Text is a mandatory column header, and is the column where the content of your survey should be included.
Note that certain characters are not allowed in the text field, such as & (ampersand) or smart quotes (Windows). This can be resolved by using the html ascii codes or replacing with standard quotes.
Below, using the same example, the text for q3 is shown in red for the single select question:
| 2 | type | label | text |
|---|---|---|---|
| 4 | radio | q3 | What is your gender? |
| 5 | r | r1 | male |
| 6 | r | r2 | female |
2.4: Value
The value field is used to override the default values assigned to answer options. This is used when options need an alternate value for statistic testing or data downloads. A primary example would be in a rating scale that ranks high to low. In the standard format, a rating scale would start at 1; if instead you need a 5 point scale that starts at 5 and declines to 1, then you can reset the values as shown below:
| 2 | type | label | value | text |
|---|---|---|---|---|
| 4 | radio | q5 | How did you like...? | |
| 5 | r | r1 | 5 | Very Much 5 |
| 6 | r | r2 | 4 | 4 |
| 7 | r | r3 | 3 | 3 |
| 8 | r | r4 | 2 | 2 |
| 9 | r | r5 | 1 | Not at all 1 |
Important: in order for the details to show in the report, it must be combined with the 'rating/scale' option in Builder options for the desired question. This can be triggered in the 'options' column using 'type=rating'.