This article provides comprehensive information on exporting data from EFS Survey projects. It covers various export formats, options, and best practices for handling survey data.

In the Projects module, various options for exporting project-related records can be found under the Export menu item of each project. If you export result data from a project with loops, you can check the status of your export job or download the complete file in the Export Tasks menu. In EFS People installations, the Export menu item in the People module allows you to additionally export people-specific data (e.g. master data or address data of panelists).

Before exporting, you should consider exactly which data you need and which programs are to be used for further evaluation.

Example: Exporting result data for evaluation in SPSS

The example below illustrates the export of project result data. The following requirements should be met:

• Only the results of closed-ended questions should be exported.
• Only survey data of respondents who have completed the survey should be exported.
• Further evaluation is conducted using SPSS.

Please proceed as follows:

1. Start the export process under Projects → {Selected project} → Export by selecting the data type “Result data”, and confirm by clicking on Next.
2. In the list which then appears for selecting the export template, format, and extensive fine-tuning adjustments, you can make the desired restrictions. In the “Export settings” area, choose “SPSS” as the file format and “Project, closed-ended questions only” as the export template. Leave the default settings for data compression (“No compression”) and language (“English (default)”) unchanged.
3. Open the “Restrict export to disposition codes” section and deactiveate all disposition codes except 31 (Completed) and 32 (Completed after break) according to the requirement defined above.
4. Then, click on the Export button and reconfirm the export by clicking on Copy file to local PC on the following page.
5. The download will begin. A pop-up window will open, in which you can choose to either open or save the file. Click on the Save button.
6. A further dialog window will open, in which you can choose the folder you wish to save the file to. You can also rename the file. Confirm by clicking on Save.
7. Once the download has been completed you can close the window by clicking on Close window.

On several large collective installations, which are used by a number of our customers, the export orders will not be processed immediately but queued and processed one after the other.

• In the Projects → {Selected projects} → Export → Export tasks menu, you can check the status of your export orders.
• After completion of the export, you will be notified automatically by e-mail. Clicking on the link contained in the notification mail will take you directly to the download page.

Detailed Description of Result, Master, and Address Data Export

Selecting the Appropriate File Format

The following formats are available:

Format	Properties
CSV	CSV (Character Separated Values) is an exchange format that is mainly used for exchanging data between different computer programs.
XLS (MS Excel 2003)XLSX (MS Excel 2007)	The data can be directly exported to a file in the XLS or XLSX format used by Microsoft Excel. As a maximum of 256 columns can be inserted into an Excel worksheet, the exported data are automatically distributed on several worksheets, as necessary.Please note: If you allow only numerical entries in an open answer (e.g. by using corresponding plausibility checks or input formats), these details will be exported nevertheless as strings. For evaluation purposes it occasionally makes sense to format these answers as numbers instead, Excel does not automatically make this format though. Please proceed as follows:In older versions of Excel, you mark the corresponding cells first. Then choose in the Format → Cells menu the option “number” on the Numbers tab and confirm with OK.In newer versions of Excel, the function for changing the cell format is hidden under the exclamation mark which you find beside the cells.
Triple-S	For exports in Triple-S, an XML description file and a record file are generated. These files are always output in compressed format, by default as a zip file, unless other specifications were made.Please note: As Triple-S only supports character set ISO-8859-1, Triple-S exports are always in character set ISO-8859-1 even if another character set has been specified. As a result, problems may arise if non-ASCII characters are used. Should this occur, export the closed-ended question results as a workaround with Triple-S and select another format for open-ended answers.
topStud	This export format provides an interface to TopCom’s offline evaluation software TopStud.Please contact TopCom directly at http://www.topcom.de if you have any questions about the TopStud format and the export interface.
XML	XML files can be converted into any other format, or displayed using self-written XSLT-style sheets.
HTML	Address or result data exported as an HTML file have the advantage that they can be immediately opened and viewed in the browser. If you wish to forward survey data, this format has the advantage that it can be opened with an Internet browser without requiring a special program.
SPSS	File format for the SPSS evaluation program.
SPSS Portable file format	Files in this format can be opened by SPSS and other statistics programs, such as Statistica. You can use it if you are editing the exported files in a statistics program other than SPSS.
SAS	File format for the SAS evaluation program.
Fixed Format	In this format, the columns are separated by blank spaces. Fixed Format is employed, for example, if the Quantum program is being used for evaluation.
Quantum	Data files for further evaluation in Quantum. The exported zip folder contains the actual result data and additionally the basic, tab, and axis files.To supplement this feature, the following optional settings are available:You can optionally adjust column widths or have them automatically expanded.The sys missings in the Quantum axis file can optionally be output.The text values can in be output in the Quantum data.The Quantum table names are automatically generated from the internal variable names.Please note: Quantum has limitations regarding the number of axes. If an exported file contains too many axes, you should edit them manually in Quantum.In the case of questions with multiple response lists, note the following: Quantum can only determine a name for the axis if the variable names for the items of a question begin with the same characters. (For example, Quantum can generate the name “f1” from the variable names “f1_1”, “f1_2” and “f1_3”).

EFS can generate export files with up to 2 GB.

Please take into consideration that, due to manufacturers’ specifications, some export formats are subject to certain restrictions:

• A maximum of 256 columns can be inserted into an MS Excel worksheet. If you open an exported CSV file in Microsoft Excel, all further columns will be cut off by Microsoft Excel. You can alternatively use the Excel export function integrated into EFS Survey, which automatically distributes the exported data on several worksheets.
• If an Excel file is opened on a computer running with Windows 7 and Excel 2007, depending on the circumstances, a message may be displayed that notifies you that the file is corrupted. This problem is caused by an extension provided by a third-party supplier. Until further notice, please proceed as follows: Save the file locally. Then, open the saved file, allowing Excel to repair it. Finally, save the file again.
• SPSS export (based on PSPP) truncates string variables after 254 characters until EFS 10.0. Until EFS 10.0, it is therefore recommended that you export the answers separately to open-ended questions in another format (e.g. XLS, CSV) and use a different program for viewing the answers. From EFS 10.1 the new PSPP version 0.8.1 is used. Therefore, when exporting for SPSS from version 14 onwards it is now optionally possible to work without this restriction. To do so, deactivate the new option “Cut text field entries after 254 characters for SPSS export”. Please note that without restriction on the text length, the overall size of the export file can grow significantly, depending on the number of text fields in the questionnaire and the number of participants.
• SPSS will only transfer user-defined missing values for text variables if the variables’ field is no more than eight characters wide.
• SPSS and special features for SPSS evaluation do not support several question types, especially question type 911 and question type 912 (which is available only in the context of special programming).
• Both in Microsoft Excel as well as in SPSS, the file size is restricted by the amount of available RAM.
• Up to Excel 2000, the maximum size of a Microsoft Excel file is 64 MB. As of Excel 2002 the maximum file size is 128 MB.
• The size of editable files is also restricted by the amount of RAM available on the computer in use.
• Microsoft Excel as well as OpenOffice are known to not correctly render or process very long cell contents.
• All columns in Fixed Format export files have at least the size of the missing defined for the data type (e. g. size of 3 for the missing value “-77”).
• When working with the English user interface of Microsoft Excel, errors may occur on opening the CSV files as columns may not be processed automatically. In this case, check Use commas instead of semicolons in CSV exports.

Compressing Files

The following options are available for exporting a file in compressed format:

• No compression
• Zip format: commonly used format on Windows machines
• Tape archive (.tar.gz): commonly used format on UNIX machines. The default setting is “No compression”.

Compression is automatically performed for exports of result data in Triple-S format. If you have made no settings yourself, a zip file will be created.

Selecting an Export Template

The following export templates are available:

• Complete project: All project result data will be exported, including time variables, which provide information on survey processing time.
• Project without timestamps: All project result data will be exported, except time variables.
• Project, open-ended questions only: Only questions with “varchar” type open-ended text fields will be exported.
• Project, closed-ended questions only: Only closed-ended questions (“int” type) will be exported.
• In personalized projects:
  • Participant data for the current project: Only the address data of the participants assigned to the project will be exported.
  • Only if you have the required additional access rights, you can access the combined participant data and result in data export.

For panel surveys in EFS People installations, you have the additional option of exporting Master data for project: Only the master data of the participants assigned to the project will be exported.

In the Export templates menu (until EFS 10.3: Template editor), you can create and save your export templates. This is particularly recommendable if you frequently only export data for a selection of variables: Selective export saves you from having to repeatedly choose variables.

Restricting Export to Individual Panel Groups

For panel surveys in EFS People installations, you have the additional option of either exporting data from the entire panel or restricting the export to individual panel groups by choosing the respective option from the drop-down list in the “Restrict export to” field.

Selecting the Language and Charset

For multilingual projects, you can choose the language in which questionnaire texts are to be exported in the “Language” field. This setting only affects the language of text elements (e.g. questions texts, answer options) that are included in the SPSS export as labels and does not affect exported survey data.

Set the character set of the exported data record in the field “Character set”. This setting affects the entire export data record, i.e. for both the questionnaire texts and any existing open-ended questions.

User-Defined Missing Values in Exported Data

You can define missing values for strings, numbers, calendar data, and timestamps for further processing in SPSS. Here, the following applies:

• SPSS accepts user-defined missing values for calendar data and timestamps.
• Missing values for number fields can be numeric or empty. However, SPSS only accepts numeric values.
• In the case of numeric values, SPSS accepts a maximum of three variables per field. Missing values defined in the questionnaire editor are included in the process.
• SPSS does not accept user-defined missing values for string variables.
• For text fields, different missing values can be entered, depending on whether the fields were not seen (default value: –66) or were seen, but not edited (-99).

You should ensure that no values defined elsewhere in the questionnaire as characteristics for alternative answers are used as missing values.

Removing Line Breaks

Excel and OpenOffice are not able to process and display CSV files containing line breaks properly. If you activate the field “Remove line breaks in values from export file” the line breaks will be removed.

In EFS 8.2 and more recent versions, this feature does not exist anymore, because from EFS 8.2 onwards, export data do not contain line breaks anymore in principle.

Replacing Codes with Labels in Excel Files

If you export result data in XLS or XLSX format (MS Excel), you have the option of replacing numeric codes with labels. To do so, navigate to the “Advanced options” section of the export dialog, and tick the checkbox labeled “Replace numeric codes with their labels”.

Exporting Open Answers Completely for New SPSS Versions

Until EFS 10.0, SPSS export (based on PSPP) truncated string variables after 254 characters. Until EFS 10.0, it is therefore recommended that you export the answers separately to open-ended questions in another format (e.g. XLS, CSV) and use a different program for viewing the answers.

• From EFS 10.1, when exporting for SPSS from version 14 onwards, it is optionally possible to work without this restriction. To do so, deactivate the new option “Cut text field entries after 254 characters for SPSS export”. Please note that without restriction on the text length, the overall size of the export file can grow significantly, depending on the number of text fields in the questionnaire and the number of participants.
• For SPSS versions below 14, the text length restriction must be used to ensure downward compatibility.

Restricting the Range of Exported Data

Restricting the range of data to a specific period

You can restrict the range of data collected during a certain period by making the respective selection in the “Last change from/until” fields.

Restricting the range of data using consecutive participant numbering

By making a respective entry in the “Lowest / Highest consecutive number” fields, you can restrict the range to survey data of a specific block of respondents who have successively completed the questionnaire.

Restricting the range of data to certain disposition codes

If you are authorized to access disposition codes (read right for show_dispcode), you can restrict the export to survey data of respondents with a certain disposition code.

To do so, untick all disposition checkboxes in the “Restrict export to disposition codes” field, for which no survey data are to be exported.

Available are the disposition codes greater than/equal to 20. No result data exist for disposition codes < 20, therefore a result data export for such codes is not advisable. By default, the data of all participants with disposition codes between 21 and 42 will be exported.

Exporting protected questions

If your project contains questions that are marked as “protected” and you have read rights for protect_questions, you can choose between various options when exporting result data:

• Export all questions
• Export “protected” questions only
• Export “unprotected” questions only

Restricting to participant groups

In personalized projects, you can limit the export to specific participant groups, defined by the characteristics of the participant variable u_group.

To do so, when exporting result data, select the appropriate group in the “Restrict to participant groups” field of the “Restrict range of exported data” section.

Selective export

If you have ticked the checkbox for the “Selective export (Please click here, if you want to specify which variable shall be exported)” field, you have the option of performing a selective export, i.e. you can choose from the range of available variables.

Once you have confirmed the selection by clicking on the Export button, the list of possible variables will be displayed and you can choose those desired.

Excel Export with External Variable Names and Meta Information

If you export result data in XLS or XLSX format (MS Excel), you have the option of adding meta information. In this case, additional columns with the following information will be inserted in the export file header:

• 1. Line: Question text
• 2. Line: Plain text description of the variable. Depending on the question type, this is In the case of questions with multiple response lists, the respective answer option is specified.
• 3. Line: external variable name If you want to use this option, tick the checkbox “Display meta information as additional column heading (excel export only)” in the “Advanced options” section.

This feature supports only the default language.

Exporting Participant and Status Codes

The export type “Participant and status codes”, which is available for personalized projects, enables you to determine the disposition code and last access for each survey participant.

The export file contains the following information:

• Participant code: The code used to access the survey.
• Disposition code: The selected disposition codes.
• “Tester” variable: Does the participant have tester status?
• Only with access right for combined participant data and result data export: Last accessed: Date and time of when the survey was last accessed.

The exported data record contains only the participant codes but does not contain respondent-specific information or result data. This will guarantee the participants’ anonymity if, for example, you pass this data record on to the customer who commissioned the project.

Exporting Answers to Open Questions for Quantum

Some evaluation programs, such as Quantum, place special demands on export records for open-ended questions: Answers should not only be separated from the answers to closed-ended questions, but their display should also be different. Whereas in the case of normal export records, consecutive numbers and result variables of a respondent are displayed side by side in the columns of a single row, the individual answers of a respondent should be displayed in rows one above the other.

The “Data (open-end answers only; optimized for column display in Quantum)” data type is available under Projects → {Selected project} → Export, and can be used to create such a record. When downloading, please proceed as follows:

1. Select the “Data (open-ended answers only; formats e.g. SPSS, CSV, Fixed Format, Quantum, MS Excel…)” data type.
2. After that, you can choose the format and make further settings as usual.
3. Confirm by clicking on Export, and download the file as usual.
4. Then open the record in the appropriate evaluation program. The exported record contains three columns:
   • fun: the respondent’s consecutive number
   • var name: the variable of the open text field
   • value: the entry made by the respondent

The above figure shows an example of a record exported in CSV format and opened in Excel.

Please note that the consecutive number is displayed right-aligned. Depending on the program used to open the record, the consecutive number will be positioned right next to the variable name.

Special Functions for SAS Export

The SAS export generates an SPSS Portable file, a text file with labels, and a macro that can be executed in SAS. The macro can be used to label the SPSS Portable file and convert it into an SAS data table.

You only need SAS to edit the export files, SPSS is not required.

Notes on SAS

Since the SAS export is performed using an SPSS Portable file, it is subject to the technical default settings and restrictions of SPSS.

Character set

From EFS 8.2, the SPSS export supports UTF-8 and all other character sets that can be selected in EFS, with the single exception of UTF-16.

Missings

The default settings for missing values are interpreted as follows during SAS export:

• “-77” is defined as a missing in both SPSS and SAS. Therefore, it is represented with a period (“.”) in SAS.
• “-66”, “-99” and “0” are not defined as missings in SAS. Accordingly, they are interpreted and displayed as number values.

Making Default Settings for the Further Processing of the SAS Files

To apply the macro to the other files without any further configuration after the SAS export, you must make the following default settings in the Options → Personal preferences menu on the Common settings tab before exporting the data:

• Standard directory for SAS exports on your computer: This directory path is copied into the SAS macro generated by the EFS Survey. Therefore, the compressed file must be located in this directory for you to apply the macro to it successfully. Default setting: c:\Documents and Settings\user name\Desktop\
• SAS library in which the SAS macro generated by EFS Survey is to store the converted data: The SAS macro will store the data temporarily in this library. You can access and then save the data from there. Default setting: work. export, where “work” is the name of the library and “export” is the user-defined name of the file.

Exporting and Opening SAS Files

Please proceed as follows:

1. In the Export menu, set the desired export options as usual, including the desired file compression (zip or tar). Also, please observe the above note on selecting the character set. After that, initiate the export process.
2. Save the compressed file locally in the directory that you chose under Options → Personal preferences.
3. Open the compressed file. It contains the three files export.por (SPSS Portable file containing the data), export.txt (text file with labels), and macro.sas (Macro).
4. Open the macro by double-clicking it.
5. To execute the macro, click on the Execute icon.
6. The data table will now automatically be stored in the library you specified under Personal preferences.
7. Navigate to the library and open the file.

If the macro is not executed correctly, check whether the right directory path has been set in the macro and whether the export files are located in the right directory

Special Functions for Fixed Format Export

Adjusting Column Widths

The export dialog (Projects → {Selected project} → Export → {Selected data type}) provides two special options in the “Export settings“ area specifically for Fixed Format export, which particularly facilitate further processing of the data in Quantum:

• Adjust column widths to effective size of data: The columns are made as narrow as possible.
• Automatically adjust column widths if missing values are longer than column widths defined in the export template: The export dialog allows you to define missing values yourself or use the default values (-77, -66, 0). If these missing values require more digits than provided for in the respective column, ticking this checkbox will widen the column. If the checkbox is not ticked, the value “-77” will be exported only as “-“, for example.

If you tick the new special checkbox “Show field positions in export template” in the codebook and select the suitable export template, the codebook will show the width, beginning, and end of the columns.

Downloading the Export Template

Another option that was set up specifically for Fixed Format export allows you to download the export template, including the information on the column width you configured yourself, as a CSV file.

To do so, you must first perform export selection as usual. The dialog shown in the figure, in which you can finally download the file, also contains a green bar displaying the here link you can click on to download the CSV file.

If, for a Fixed Format export, you additionally choose to compress the data into a zip file, this zip file will automatically also contain the export template in CSV format.

Excursion: Merging Records in SPSS (Matching)

Under certain conditions, various system files can be merged to create one system file. There are two possible ways of merging:

• Merging cases where variables are identical or almost identical (Add Files).
• Merging variables where cases are identical or almost identical (Match Files).

For the ‘add files’ command, cases are added from one or more records to an existing record (vertical conjunction). For identical variables, syntax in SPSS is as follows:

For the ‘match file’ command, several records can be merged using a key variable (horizontal conjunction).

The prerequisites for successful matching are:

• All files must contain the same number of cases.
• All files must contain a key variable with an identification number (e.g. lfdn).
• Each case must be assigned to a different identification number.
• A certain case must always have the same number in different files.
• All files must be sorted by identification number.

In this case, the consecutive number ‘Lfdn’ is chosen as a key variable, and the records must be sorted by this variable. For an identical number of cases, syntax in SPSS is as follows:

Should problems occur when matching two EFS Survey data files in SPSS format, save the records in POR format (Portable Document) and repeat matching.

What do the Exported Result Data Reveal?

Overview of Variables

Depending on project, project properties, choice of export options and authorization of the user who executes the export, the result data record may contain the following variables:

Internal variable name	External variable name	Description	Meaning
lfdn	lfdn	number	Every participant in the questionnaire will be assigned a consecutive number.
external_lfdn	external_lfdn	external lfdn	If an external survey start is used, external_lfdn is used in the secondary project to store the participants’ ldfn from the primary project. You can download a dedicated documentation for external survey starting in the customer center.If several surveys are linked by external survey start, the variable external_lfdn will contain the lfdn of the primary project in all following surveys.
tester	tester	tester	Indicates whether the participant in question is a tester (not for anonymous projects), “1” being for a tester and “0” for a normal participant.
status	disco de	disposition code	The participants’ disposition code. Please mind that the disposition code is only part of the export file if the exporting user has read rights for show_dispcode.
last_page	lastpage	last page	Indicates the last page sent by the server. I.e. if participants dropped out of a survey, this is usually the last page presented to them.
quality	quality	quality	The respondents’ quality index.
time_to_complete_survey	duration	time to complete the survey	The duration of survey completion (in seconds), i.e. the time elapsed between the respondent’s first and last accessing of the questionnaire.The duration is computed as follows: A record is made of when the respondent first and last accessed the survey. These values can be found in the export record as the variables datetime (SPSS label: datetime of survey start) and lastaccs (SPSS label: date of last access). The difference of these two values is the time the participant took to complete the survey (duration = lastaccs – datetime).If a respondent resumes the questionnaire after a break (disposition codes 23, 32), the duration is = “-1” because a meaningful calculation is not possible.
p_000n	p_000n		URL parameter
c_000n	c_000n		User-defined variables
v_000n	v_000n		Project variables. The project variables are sequentially numbered for each project, beginning with v_1.
md_000n(Only in EFS Panel installations)	md_000n		Master data variables.
remote_host	ext_host	external host	Indicates the domain of the computer via which the participant is connected to the Internet if it has a domain.
remote_addr	ip_addr	remote address	Indicates the IP address of the computer via which the participant is connected to the Internet.
user_agent	browser	browser-id	Indicates the name of the browser in the form in which it was transferred to the EFS server. Examples can be found at http:// de.wikipedia.org/wiki/User_Agent.When filtering is done via browser types, it is advisable to use the “matches regex” condition, which is also new.
http_referer	referer	http-referer	Indicates the Internet page from which the survey was invoked.
device_type(From EFS 10.0 in “Responsive Layout” projects)	device_type	device type	Contains the result of the check for device type, which can optionally be performed at the beginning of the survey:Activated0 = Desktop1 = mobileNot activated: 0
participant_browser(For EFS 10.7 and later versions)	participant_browser	participant browser	States the browser used by the participant, e. g. Internet Explorer
participant_browser_version(For EFS 10.7 and later versions)	participant_browser_version	participant browser version	States the participant‘s browser version, e. g. 9.0
participant_os(For EFS 10.7 and later versions)	participant_os	participant operating system	states the participant‘s computer‘s operating system, e.g. Windows.
participant_device(For EFS 10.7 and later versions)	participant_device	participant device	contains the result of the device type check (optional, may be performed at the start of the survey), e.g. smartphone. Refer directly to the software‘s user interface for a list of all identifiable devices.
participant_brand(For EFS 10.7 and later versions)	participant_brand	participant brand	states the brand of device used, e. g. Apple.
participant_model(For EFS 10.7 and later versions)	participant_model	participant model	stathe tes model of the device used, e. g. iPhone 6.
participant_isbot(For EFS 10.7 and later versions)	participant_isbot	participant is (ro)bot	states whether the participant is a robot or a crawler. Regular participants are assigned the value 0.
participant_continent(For EFS 10.7 and later versions)	participant_continent	participant continent	states the participant‘s current location (continent), e. g. Europe
participant_country(For EFS 10.7 and later versions)	participant_country	participant country	states the participant‘s current location (country, e.g. Great Britain
participant_region(For EFS 10.7 and later versions)	participant_region	participant region	states the participant‘s current location (region), e.g. Lancashire
participant_city(For EFS 10.7 and later versions)	participant_city	participant city	states the participant‘s current location (city), e. g. Lancaster
quota	quota	quota	Contains the ID of the allocated quota (i.e. the quota selected via allocation mode).
quota_assignment	quota_assignment	quota_assignment	Contains the IDs of all quotas in which the respective participant has been counted.
page_history	page_history	page history	Indicates the sequence of invoked pages. The page ID (pgid) displayed in the questionnaire editor beside the individual pages is used.The current state of questionnaire processing is minutely recorded in the page history, i.e. the variable page_history will show you the movements of the participants within the questionnaire up to the last viewed page.Please note: If a real Back button is activated in the form, and a participant leaves a page using this Back button, this page will be removed from page history.
hflip	hflip	hflip	Variable for horizontal flipping:0 = not flipped1 = flipped
vflip	vflip	vflip	Variable for vertical flipping:0 = not flipped1 = flipped
javascript	javascript	jscript	Contains the result of the check for JavaScript, which can optionally be performed at the beginning of the survey:0 = JavaScript switched offOther = Version number (e.g. jscript = “10” for JavaScript 1.0, jscript = “15” for JavaScript 1.5)
flash	flash	flash	Contains the result of the check for the Adobe Flash plugin, which can optionally be performed at the beginning of the survey and is required for the Flash question types:0 = Flash plugin, not availableOther=Versionnumber(e.g.flash=“800”forFlashplugin8.0, flash = “900” for Flash plugin 9.0)
seesion_id	session_id	session id	Indicates the session ID the participant received during the survey.
language	language	language	The language in which the respondent has seen the questionnaire.The numbers correspond to the language ID (identification number) of the languages used in the project.With the wildcard #language# you can display the language ID in the questionnaire or templates. This is particularly important for language-dependent layouts and special programming.
datacleaning	cleaned	datacleaning	Indicates if the individual record has been cleaned:0= uncleaned data records and records of incomplete interviews which are excluded from the cleaning process1 = cleaned2 = checked but did not require cleaning
dt_begin	ats	absolute timestamp	Indicates the time in seconds elapsed since 01/01/1970 until the request of the first page of the questionnaire).
dt_begin	datetime	datetime of survey start	Date and time of survey start, i.e. the invoking of the first page of the questionnaire.
date_of_last_access	date_of_last_access	date of last access	Date and time of the last access to the survey.
date_of_first_mail	date_of_first_mail	date of first mail	Date and time of the first invitation mail sent to the participant. The variable is used for all project types which include invitation processes, i.e. personalized projects, panel surveys, and master data surveys. In anonymous projects, it is not filled.Please mind: date_of_first_access is updated upon resumption.
sid	sample_id	sid	Only in panel and master data surveys : ID of the sample that the panelists belong in.For the SPSS export, the codes are labeled with the sample titles.
dt_send_pag_000n	rts_000nFor pgid > 99999: ts_000n	relative timestamp for page xxxx	Time variables of standard pages and mixed-external pages. These indicate the time in seconds elapsed from the ats until submission of the respective page.If the PGID (page ID = installation-specific ID of a questionnaire page or a dynamic element) is bigger than 99.999, the name of the variable is changed to ts_xxx, to prevent problems caused by the length of the variable name when exporting for SPSS.
rnd_pg_PGID	rnd_pg_PGID	random page selection order	Random selection.For each random selection block in the questionnaire, a variable rndPGID is created. PGID is the ID of the random selection element.In this variable, the PGIDs of the selected pages are stored, separated by commas.
rnd_pg_PGID_x	rnd_pg_PGID_x	random rotation selection order	Random order.For each random order block in the questionnaire, variables rnd_pg_PGID_NR are created. PGID is the ID of the random order element, NR is the number of randomized subpages.Each of these variables contains the PGID of that subpage which has been placed on the respective position in the current cycle.Example: The PGID of the random order element is 123, and the sub-pages have PGID 124, 125, and 126. In the questionnaire, they are displayed to a participant in the following order: 126, 124, 125. Then, you will find the following data in the export file: r123_1 = 126, r123_2 = 124, r123_3 = 125
tic	tic	survey ticket id	tic variable used in external survey start. The tic variable is always 32 characters long. This cannot be configured.
s_000n(From EFS 10.5)	s_000n	s_000n	Scoring variables.

Overview of Variable Preallocations

Preallocation value	Meaning
-77	Project variables of the v_000 type, with the exception of variables that refer to text fields: The participant did not see the question, for example, because they dropped out or because the relevant page or question was not displayed due to routing.Master data for EFS People installations: Preallocation for master data of the md_xxx and m_xxx type.
0	Project variables of the v_000 type, with the exception of variables that refer to text fields: The participant saw the respective question, but did not respond to it.Participant administration variables of the “text field” type: Preallocation if the variable was not filled.Participantadministrationvariablesofthe“selectbox”type:Preallocationof those variables that are available per default and set to “Please select”. For example, the standard variables u_gender, u_language and u_group.
-66	Project variables of the v_000 type that refer to text fields: The participant did not see the respective question, because it was hidden.
-99	Project variables of the v_000 type that refer to text fields: The participant saw the respective question, but did not respond to it.

You can specify the missing values in the export data records yourself.

EFS Survey Variables

Overview of the Various Variable Types

The locations in the database, in which answers given by the respondents, participant administration data or system information regarding the course of the survey are saved, are identified using variables. In defining routing, e.g. via a filter or when importing saved contents via wildcards you can access the database contents via these variables. A range of variable types are used in EFS surveys:

• Project variables (v_000n): The results of the survey are saved in these variables. They are automatically created when new questions are created.
• URL parameters (p_000n): These variables allow you to pass variables to a survey from outside. They are created and configured manually.
• User-defined variables c_000n: These variables are used to store interim results. They are created and configured manually.
• Participant variables (u_000n): Information regarding survey participants is stored in these variables. A set of frequently used variables is available by default and additional variables may also be created.
• System variables: The variables are used to store meta-information regarding participants and the course of the interviewing process. System variables are automatically generated when a project is created.
• For EFS People installations only: Participant data of the panelists (u_000n). These variables are preset by the EFS People and are not configurable.
• For EFS People installations only: Master data (m_000n). Fundamental panelist properties determined by the panel administrator, which are stored independently of the specific project and which are permanently available.
• For EFS People installations only: Panel system data. The variables are used to store meta-information regarding the panelists.

Maximum Available Number of Variables

Up to 7,000 variables are available per project.

• This value is the sum of all variables in the project, not only the sum of the project variables.
• The maximum number of variables that are available in a specific project depends on various factors, such as e.g. type and size of the variables created, and may be lower than 7,000.

In an EFS People installation, up to at most 2,800 master data variables can be used.

The complexity of a project affects its performance. For example, the more complex the routing structures, number of participants, or number of variables, the more the project will be slowed down.

Tip: Compile the project as usual before taking it into the field and delete any redundant variables.

Generating and Editing Project Variables and Codes

Project variables (v_000n) will be created automatically when you create a question.

• In the case of single response questions, only one variable per question is required for each and the various answer options are allocated codes.
• In the case of multiple response questions, one variable is created for each answer option. Whether the respondent has selected an option is recorded via the coding: 0 = not specified, 1 = specified.
• For matrix questions, one variable matches each answer option, and the scale characteristics are encoded (exception: question type 362 with checkboxes and question type 363 with text fields).
• Entries in open text fields are similarly recorded in a variable.

The variable names are assigned automatically for each project, beginning with v_1 and names cannot be edited. If required, however, you can allocate an external variable name. You can see the internal project variable names when working in the questionnaire editor depending on the question type

• in the “Answer options” area in the “Variable name” columns
• in the case of questions with an open entry field in the “Question options” area.

The codes are also assigned automatically but can be edited in the case of single response and matrix questions. You can view and edit the codings in the questionnaire editor

• for single response questions in the “Code” column in the “Answer options” section.
• for matrix questions in the “Code” column in the “Scale options” section.

All project variables and their codings are listed in the codebook.

In a live questionnaire, please never delete a page, question or answer option, or change a code retroactively. Otherwise already stored information will be lost or it will no longer be possible to interpret it correctly. Filters and other features that reference variables and codes might also be damaged. Please do the clean-up before launching the survey, then compile and check again if all questionnaire features are working properly.

Preset and User-Defined Names for Project Variables

If variable names are displayed within the EFS Survey the automatically preset, so-called internal variables are used. You can of course allocate alternative names which will be used on export instead of the internal names and then be available for external evaluation.

• Internal variable names: These are the variable names that are automatically allocated by the system when creating a question and are used within EFS. These have the format v_000n and cannot be changed.
• External variable names: These names can be changed optionally by the user and are then used on export instead of the internal variable names.
  • External variable names may have up to 64 characters.
  • The term “comment” should not be used as an external variable name (“comment” is a key term in PSPP, the program used for the SPSS export).

Please note that the user-defined name type is used for data export only. Control functions such as filter and trigger definitions cannot be addressed using the external variable names and wildcard use is not an option either.

Viewing and Editing User-Defined Variable Names in the Questionnaire Editor

Internal variable names are always displayed in the question view. In addition, you can also display and edit external variable names, if required. Depending on the question type you will find the respective field

• in the “Question options” area for questions with a single response list or open entry field.
• in the “Answer options” area in a table column that is usually hidden in the case of multiple response and matrix questions. Click on the arrow icon in the “Variable name” column to display the “External varname” column.

If you cannot find the field described above click the Question options icon and enable “External varname”.

Options for Centralized Editing of Variable Names

The codebook contains a range of functions that enable effective centralized editing of user-defined variable names in the “Edit variable names” section.

Editing variable names

When the option “ Edit variable names directly in this form” is selected and then confirmed with the Save settings button, entry fields are displayed in which you can edit the external variable names. After entering the new names, click on the Save variable names button.

Changing variable names according to a preset algorithm

You can allocate a unique label to the external variable names of a project by a predefined scheme.

Available options are:

• Consecutive internal variable name (v_x): Default in new projects
• User-defined prefix consecutive item number
• User-defined prefix consecutive question number_consecutive item number (per question)
• User-defined prefix consecutive question number|consecutive item number (per question)

Please proceed as follows:

1. In the codebook click the Select algorithm for creation of variable names link in the “Create new variable names by selected algorithm” field. This opens the Projects → {Selected project} → Project properties menu.
2. Set the desired labeling option in the select box.
3. Enter the desired prefix in the “Algorithm for creating the external variable names” field. The default preset is “v_”.
4. Switch back to the Codebook.
5. Activate “Create new variable names in accordance with selected algorithm”.
6. Confirm by clicking on Save settings.

Systematically naming variables in dynamic answer blocks

The “Rename variables in dynamic answer blocks” option allows you to automatically change the names of variables, for example, in a list or a loop in such a way that the link to the original variable can be read from the name. If, for example, a dynamic list is incorporated into a question, all variables that were generated via the dynamic list will have the name v_sourcevariablea once renaming is complete. If the questionnaire contains a second question that draws on the same dynamic list, then the corresponding variables will be named v_sourcevariableb.

Editing variable names externally

You can export external variable names, edit them outside EFS, and then re-import them:

1. The “Export variable names for external editing” will create a CSV file containing the variable’s information, which you can load onto your workstation. You can specify which character set is to be used.
2. When editing you enter the new external variable name in the “New varname” column.
3. The “Import externally edited variable names” option allows you to re-import the externally edited list in CSV format into EFS Survey. Here, too, the correct character set must be specified.

Undoing changes

Changes to external variable names can be undone applying the “Restore original variable names” options.

Defining Export Templates

You can define your templates based on existing default export templates. This can be useful, for example, if you frequently need only specific excerpts from the result data.

Please note: When copying projects, user-defined export templates are not included.

Overview of Existing Export Templates

The Export → Export templates menu (until EFS 10.3: Template editor) contains a list of the templates that have been set up for the respective project and the functions for creating new templates.

• The default templates of the “Static template” type cannot be changed or deleted. Even though it is possible to download these templates, the modified templates must then be uploaded under a different name, in a newly created user-defined template.
• Templates of the “User-defined template” type can be edited and deleted.

The following icons are in the “Action” column:

• Upload definitions for template “…”: This function is only available for user-defined templates you have created. You can use it to upload an edited definition file.
• Download template as CSV file: The downloaded file has the .csv file type.
• Download template as Excel file: The downloaded file has the .xls or .xlsx file typeand can be edited in MS Excel.
• Save as a new template: You can save the selected template as a new template, allowing you to download and edit it afterward.
• View template: The export template will open in a table.
• Delete template.

Viewing User-defined Templates and Sorting Their Contents

Clicking on the name of a template or the View template icon will display its content in a table that cannot be edited.

It contains the following information for each variable of the template:

Column	Meaning
Activated	If the variable is activated (Yes = 1), it will be exported in the data record. If it is not activated (No = 0), it will not be exported.With the template shown above, you would only export those variables from the address record that contains the participants’ postal addresses (first name, name, street, zip code, city, and country).
Position	The position of the column in the exported data record.
Width	The width of the column.
Alignment	The alignment of the data for Fixed Format and Quantum export.
Ext. varname	The SPSS label and header in Excel.
Description	The description is used for XML and Triple-S export.
Quantum table name	The table name used for Quantum export. This name is automatically generated and cannot be edited.
Question type	The question type is displayed to improve editability and structure. It cannot be edited.
Int. varname	The EFS Survey variable name.
Data type	The type of data that can be entered into the respective database field.

For user-defined templates, you will find that there are two additional buttons:

• Sort fields by position: Sort the rows by the content of the “Position” column.
• Shrink column widths to width of actual data: This function is required for Fixed Format and Quantum exports.

Creating a User-defined Template

To define a new template, you must first select the appropriate basic template: This template should at least contain all of the data you wish to export later on. Select the basic template in the “Create a new template” section, and confirm by clicking on Create (alternatively, you can also click on the Save as new template icon).

In the next window, enter the name of the template and confirm by clicking on Save as a new template. The new template of the “User-defined template” type will be created and displayed in the list of templates.

Downloading and Uploading Export Templates and Editing Them Externally

To edit an export template, you can download it and edit it externally. Please proceed as follows:

1. Click on the Download template as CSV file or Download template as Excel file icon to download the template to your hard disk.
2. Open the file in a suitable program, e.g. in MS Excel. Edit the file according to the rules presented below in the section titled “Editing a user-defined template”, and then save it. In the example shown, some variables are deactivated: “u_group”, “u_email” and “u_www” have already been deactivated.
3. Click on the Upload definitions for template “…” icon. Locate the definition file on your hard disk, and then confirm by clicking on Upload. Open the export template, and check the result. In the example shown, all variables after “u_group” were deactivated. You can use the buttons labeled Sort fields by position and Shrink column widths to the width of actual data to edit the template further.

Editing a User-defined Template

When editing the template definition, you should pay attention to the following rules:

• A variable for export must be specified for each row, and each row must consist of seven values separated by tabs.
• You should not change the order of values.
• Deleting the variables from the list will not delete them from the export data record. The “active” column determines whether a variable is to be exported.
• It is not possible to add variables.
• Duplicate definitions of a variable are not permitted.
• Variable column positions (pos+width) may not overlap.
• The position and width of the columns for the individual variables must be such that they will not overlap.

You can click on the Sort fields by position button to have the variables arranged by their position. This arrangement will be saved and can then only be changed by downloading the template again and editing it manually.

Clicking on the Shrink column widths to the width of the actual data button allows you to optimize the export template for Fixed Format and Quantum exports.

In personalized projects, you can change the sequence of participant data in the export dataset via participant administration.

Data Cleaning

If a respondent uses the “Back” button in the questionnaire or that of the browser while completing a questionnaire, it is possible that they will pass the same filter question more than once, giving different answers and then being routed into another filter branch, where they will answer different questions.

Example: Somebody answers the question of whether they drive a car with “Yes” and is subsequently asked all car-related questions. Then, they go back to the questionnaire and state they do not drive a car. A filter lets them skip all car-related questions now. When the respondent completes the survey, they will have stated they do not drive a car. Nonetheless, all car-related variables will contain values.

In the past, such irrelevant data had to be manually cleaned from the result data. With the “Data cleaning” function, this cleaning process has been automated.

Before you start

Please note:

• Data cleaning only considers completed interviews, i.e. records with status 31 or 32.
• The following routing types are supported:
  • “Filter” and “Random rotation” branches.
  • Trigger types that affect routing via changes in the survey results. The function should, however, not be used in projects involving page triggers: In this case, the routing that the respondent took erroneously or for test purposes cannot be unambiguously identified.
  • Action pages.
  • Depot questions.
  • EFS-side Back buttons. Please note: that browser-side Back buttons are not fully supported. If a respondent skips back several pages using the browser Back button first and then skips forward several pages using the browser Forward button ignoring the system warning, these steps cannot be tracked and stored correctly. I.e. pages might be missing in page history, though valid data from these pages exist.
• “Random rotation” branches and loops are not supported.
• Data cleaning should not be applied to surveys that are prefilled with values. I.e. it should not be combined with the options “Transfer master data values to survey variables” → “Always pre-fill”, “Allow multiple participations, pre-fill with values of last participation” and import of specific answers from other projects.

Activating Automatic Data Cleaning

Activating data cleaning before the beginning of the field phase

In the Projects module, click on the desired project, and then choose the Project Properties menu item. On the tab General Options, tick the checkbox in the “Enable data cleaning” field. Then, confirm by clicking on Submit. The Display link allows you to switch to the Data cleaning submenu.

Triggering Data Cleaning for the Entire Project

The documentation of all changes to the data as well as functions for restoring all or part of the original data are located in the data cleaning log. There are two ways of accessing this:

• Click on the Display link in the Project Properties menu.
• The second way is via the Statistics → Data cleaning menu.

You are automatically taken to the General tab.

The “General Information” area shows whether data cleaning has already been performed and how many records were processed.

• Status data cleaning in this project: Data cleaning is activated from the Project Properties menu.
• Number of cleaned records: The records that were changed during data cleaning.
• Number of data records that were checked but did not require cleaning: The records that would not be / are not affected in the event of data cleaning.
• Number of uncleaned records:
  • If data cleaning has not taken place, this value will contain the uncleaned records.
  • After data cleaning has taken place, all records that could not be checked and cleaned because the interviews were not completed remain in this field.
  • If further questionnaires were filled out after a manual data cleaning, this value will be the total of all uncleaned records, as well as all records that were not checked during the manual data cleaning because the interviews were not completed.

In the “Data cleaning for the whole project” area, you can trigger or undo data cleaning:

• If you select “Clean all data” and then confirm by clicking on Execute, the cleaning operation will be triggered.
• If you select “Recover all data” and then confirm by clicking on Execute, the original state will be restored.

Please note that data cleaning only takes completed interviews into consideration, i.e. records with status 31 or 32.

Viewing Details and Editing Records Manually

The Extended tab provides detailed documentation of all changes to the data as well as functions for both editing individual records and restoring all or part of the original data.

Display Options

• Number of datasets per page: If you enter the corresponding value and then con- firm by clicking on Apply, you can change the number of records displayed on one page.
• Display only datasets with a particular variable: This search function identifies all records containing the given string. After entering the string, you can trigger the search by clicking on Apply.
• Display routemap: Ticking the checkbox and then clicking on Apply will show the so-called routemap in the log. The routemap is a comma-separated page list reflecting the participant’s valid route through the survey (see the “Example” section below). It is extracted from the page history (phistory is the variable used in the export record, page history is the label used in the SPSS export record).

Interpreting the entries in the data cleaning log

• The data cleaning log contains the following information and functions:
• Cons. no.: The respondent’s consecutive number in both the survey table and the export record. The table contains only the records of those respondents for whom data cleaning might be required.
• Cleaned data: Those data from the complete record that were / would be changed during cleaning.
• Original: The original state of those data from the complete record that were / would be changed by cleaning.
• Routemap: This can be shown and hidden using the “Display routemap” function in the “Display options” area.
• Cleaned: yes/no.

Editing individual records

You have the option of targeting individual records for cleaning or for restoring the original state. To do so, tick the respective checkbox in the “Execute data cleaning” or “Recover data” column, and confirm by clicking on Save.

Identifying Cleaned Data in the Export Record

The export record provides information if an individual record was cleaned: It is stored in the variable cleaned (labeling in the SPSS export record: “datacleaning”) which can have the following properties:

• 0 = uncleaned data records and records of incomplete interviews which are excluded from the cleaning process
• 1 = cleaned
• 2 = checked but did not require cleaning

Quality Correction

The conscientiousness with which survey participants fill in questionnaires is critical for the quality of the results of online surveys. The results from survey participants who merely click very quickly through the questionnaire, for example, to get a quick overview of the survey theme or on completion to participate in a prize draw, are of little value for further analysis in the end. The quality correction program helps you to recognize participants who simply “clicked through” by their answer behavior, to mark these data records, and if desired to delete them.

Response times as quality criterion

The response times of a user are the key criteria needed for indexing the quality of survey participants. The time taken by each participant to complete a particular page of a survey can be calculated. First of all an index is formed for each participant that gives information on his mean processing time for a questionnaire page. Please note here: The time required by a participant to complete an entire survey is not a factor in the calculation. Depending on the direction of the questionnaire the number of pages that the participant sees can also vary tremendously.

Subsequently, a separate calculation for each page is made, which shows where the survey participant is about the processing time of the media. The median survey time of 100 participants corresponds to the average survey duration of the 50th and the 51st survey participants when these have been sorted by survey time.

Determining the quality index for individual users

To determine the “quality” of a participant in a survey, his processing time is set about the average processing time of the entire sample. The relationship can be expressed as a number that is stored in the variable “quality”. For example, a value of 0.5 signifies that the corresponding user required exactly the average time for processing the questionnaire pages. A value of 0.25 signifies that the respondent needed only half as long as the average processing time.

Triggering Quality Correction

You can start the program that calculates the quality index under Projects → {Selected project} → Statistics → Quality correction by clicking on the button Execute. It does not make any changes to the actual survey data, rather it expands the data record to include the variable “quality”.

Quality correction should not be triggered when large surveys are being conducted simultaneously in the installation.

Viewing the Quality Index in the Export Data Record

The quality index of a participant will be stored in the variable “quality”. This is contained within the export data record, and therefore you can identify the data records of participants with critical values and where necessary delete them before making an evaluation.

General Tips and Tricks on Exporting Data

The following applies to all exports:

Export duration

The exported data are not created in advance, they are created according to the settings you made the moment you click on the Export button. Depending on the desired content, selected format, and number of records, the export can therefore take a considerable amount of time.

First, save, then open the file!

Once you create the file after having made all settings, a dialog window will open, in which you can choose whether you wish to save the file on your local PC or open it directly.

Please always save the file on your local PC. This procedure has several advantages:

• The file only needs to be created once. Newly creating the file each time would cause an unnecessary load on the server.
• Depending on the browser used, you may not be able to open the actual application and specify the file type when opening the file directly. Instead, the browser may attempt to automatically open the file itself. However, it may be the case that the browser’s file type recognition was incorrectly implemented, i.e. the browser does not identify the file type correctly, cannot open the file, and produces an error message.

Characters which cannot be exported

An effective export process into a variety of formats requires a few rather marginal modifications of the result data:

• Backslashes are removed.
• The special characters \n (Newline), \t (Horizontal tab), \r (Carriage return), and ” (Double quote) are removed as well.
• NULL values are replaced by an empty string.

These modifications affect all result data exports including the panel exports.

The CSV export is preffered to the Excel export in order to execute high volume data exports at a greater performance rate.

FAQ

What is the maximum number of variables available per project?

Up to 7,000 variables are available per project. However, the actual number may be lower depending on various factors such as variable types and sizes.

Can I export data from incomplete surveys?

Yes, you can export data from incomplete surveys. However, data cleaning only takes completed interviews (status 31 or 32) into consideration.

How can I identify cleaned data in the export record?

Cleaned data is identified by the “cleaned” variable in the export record. It can have values of 0 (uncleaned), 1 (cleaned), or 2 (checked but did not require cleaning).