Export

In the Projects module, various options for exporting project-related records can be found under the Export menu item of each individual 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).

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.

Detailed Description of Result, Master and Address Data Export

Selecting the Appropriate File Format

The following formats are available:

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”.

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, with the exception of 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 current project: Only the address data of the participants assigned to the project will be exported.

  • Only if you have the required additional access right, you can access the combined participant data and result 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.

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).

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 mind that without restriction of 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 num- ber of participations.

• 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 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 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 which 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.

Exporting Participant and Status Codes

The export type “Participant and status codes”, which is 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.

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:

   • lfdn: the respondent’s consecutive number

   • varname: the variable of the open text field

   • value: the entry made by the respondent

Open

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

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.

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 which 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

In order 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 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 con- verted 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.

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 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.

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.

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).

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:

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: