Exporting to Excel

If you have selected to export to Excel File or Excel File (Answer Codes as Labels) in stage 1 of the dialog, then stage 2 of the dialog will appear as seen below.

Note: When exporting to Excel, if you intend to re-import the data that is to be exported then you should choose ‘Excel File’ not ‘Excel File (Answer Codes as Labels)’. This is because you cannot import the data if it’s in the format ‘Answer Codes as Labels’.

Figure 1 - The second stage of the Export dialog for an Excel export

1. Make the appropriate selections. The information displayed, and the options available, are as follows:
• Excel Version - the file will be exported to MS Excel® in the Office version selected (default .xlsx). The variables may be split over several worksheets. Microsoft Excel 97-2003 (.xls) and Microsoft Excel 2007 or newer (.xlsx) are supported. The variable names will be in the first row.

Note: The default Excel export format is now .xlsx, and all new export tasks will use this format unless another format is selected. Older tasks, for example recurring tasks that were set up before the default format changed, will continue to export to the format specified when the task was set up.

• Truncate Open Ends - for Open Ends (Open Text questions with no field width specified), it is recommended to truncate texts to improve performance. If a number of characters is specified for the Open Ends Width, texts longer than this limit, if any, will automatically be truncated. If No Truncation (Full Length) is selected, the whole text will be imported/exported, but the task will take significantly longer to execute.
• Loop Handling - this option is only available if the survey included loops. Here you specify how loops are to be handled for the export. The options are:
• Separate files - the default export format for loops creates one export file for each loop, and one export file for the top level. In the files containing the loop data there will be one row per loop iteration per respondent, so there will be several rows for each respondent. Each row will have the responseid (system generated respondent id) of the respondent and the precode of the loop id for the iteration as identifiers, and then the answers for the questions inside the loop for that specific iteration for that respondent. This means you can export a hierarchical survey without having to generate a flattened template.
• Single file - Instead of exporting loop data as separate files, you can export all data belonging to a survey in one single file. Loop data will then be expanded. This means that variables will be created to represent each iteration of each question.
• Position of loop variables - only available when Single file is selected in the Loop Handling option. This allows the user to specify in which position the loop variables should be exported. The options are:
• As in Survey - the loop variables are positioned as the corresponding loop was placed in the Survey.
• Appended at end - the loop variables are appended at the end of the template.
• Variables to include (Survey Data Template) - you can build templates to choose a subset of data fields for your exports. If you check this box, select the template you wish to use. Refer to the Data Processing documentation for more information.

Note: With the Survey Data Template the user can specify which variables are to be exported and in which order. Label information is taken from the “Text in Question Labels” and “Text in Answer Element Labels” options. This applies to all Excel and Text Delimited File exports. For the other exports all information is taken from the Templates and these options are not available.

• Text in Labels - when the Variables to include (Survey Data Template) option above is selected, the Text in Labels option becomes available. Here you can select whether you wish to export the column labels from the template or from the survey (the labels can be modified in the template - see the Data Processing User Guide for further details).
• Override Filename - allows you to override the system-generated file name. Tick this checkbox to open the file name text-box. Here you can edit the system-generated name (go to APPENDIX F: EXPORT FILENAME DYNAMIC VALUES for more information). Note that when exporting Survey Data to the FTP server in Survey Data > Exports or Data Processing rules, the Override Filename option accepts relative paths, as for example “..\..\myftpfile_in_another_folder.txt”. In this case the file will not be stored in the default folder (“ProjectsData\p123123” or “RulesData\RuleN”), but rather directly under the (Company) Download folder.

Note: The '_^PROJECTID^_' part of the name will be replaced by the real survey id during the export. Default is ^PROJECTID^_^USERID^_^TASKID^ (if Date Filter is used, then ^START^ and ^END^ will also be included). ^PROJECTID^ is only "required" when executing a rule; it is not "required" when running an export using the Export GUI.

• Export language - in the event the survey is multi-lingual, select the survey language version to be exported.
• Text in Question Labels - select the text you wish to be included as the question labels. The options are:
• Text only - the label element will contain the question id followed by the text of the question, separated by a colon.
• Title only - the label element will contain the question id followed by the title of the question, separated by a colon.
• Title and Text - the label element will contain the question id followed by a colon, then the question title followed by the question text, these separated by a hyphen.
• Question ID - the label element will contain only the question id.

Note: As the engine that is used for the exports requires that the column titles are unique, and in Professional Authoring question titles can be the same on different questions, the question id is included first for all question label options.

• Text in Answer Element Labels - allows you to specify whether the Answer element of a Multi / Grid / Open Text List / Numeric List Question is to be positioned in front of the Question label, after the Question label or without the Question label when exporting survey data or when generating a Survey Data Template which can be used for a survey data export. The content of the Question label depends on the Text in Question Labels setting, which allows you to specify whether you want the Question ID, Question text, Question title or Question text and Title exported. Select the text you wish to be included as the answer labels. The options are:
• Answer (Question Label) - the question label is placed after the answer element.
• Question Label (Answer) - the question label is placed before the answer element.
• Answer - the answer element is placed without the question label.

Note: When exporting to Microsoft Excel 97-2003, the column header text will be truncated to 64 characters. Note that this limitation does not exist in the Microsoft Excel 2007 or Delimited Text File export, which can be used instead. The text file output can then be opened and processed as required in Excel.

Note: Microsoft Excel 2007 cannot be imported. Only single files can be imported.

2. When you have made the appropriate selections, click Next to go to the third stage of the dialog.