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

Prior to 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} → ExportExport 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

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 FormatCells 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 by cut off by Microsoft Excel. You can alternatively use the Excel export function integrated in 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 which 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 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 number of participations.

  • 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 context of special programmings).

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

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

page572image10144048

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 OptionsPersonal 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 OptionsPersonal 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:

add files file='c:\syntax\spss_1.sav' /file='c:\syntax\spss_2.sav'. save outfile='c:\syntax\Final_add.sav'.

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:

match files file='c:\syntax\spss_1.sav' /file='c:\syntax\spss_2.sav'. /by lfdn. save outfile='c:\syntax\Final_match.sav'. execute.

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

Internal variable name

External variable name