Pular para o conteúdo principal
Base de Conhecimento da FocusVision

Excel Import File Setup

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 "Importing an Excel Document" 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'.