This translation is older than the original page and might be outdated. See what has changed.
Translations of this page:
 

Data Import in Microsoft Excel

This chapter describes the data import in Microsoft Excel for downloading the data in CSV format with the default settings (Download Data: CSV).

Information: Please note that older versions in Excel can only import 255 variables. More variables are truncated. If necessary use a newer program version or OpenOffice Calc.

Information: This describtion addresses Microsoft Excel 2000 (version 9) and 2002/XP (version 10). Other program versions may use other descriptions.

Open File

In the simplest case the CSV-file can be opened by double-clicking.

Problems while Importing

When opening the file, different problems might occur depending on the used Excel version – Hereinafter the most important nominees.

All Values in the First Column

After opening the file, you see only a long queue of values, which are not displayed in separate columns/variables.

Fehlerhafter Import in Excel

In most cases, it is sufficient to change the Variable delimiter before downloading the file. For current versions a tabulator is set by default. The older Excel 2000 possibly can better deal with a semicolon.

Daten für Excel 2000 herunterladen

If the data still are not separated in individual columns, you have to start Excel and then open the file from the menu.

  • Start Excel
  • FileOpen
  • Change File Typ to “Text Files (*.prn,*.txt,*.csv)”
  • Select the CSV-File → Open
  • OK

Auswahl der Datei in Excel

There is a Date within the Data instead of a Decimal Number

Excel tries to read out “the best” of the data. For example, if a participant has indicated that he works 20.5 hours per week, then it is interpreted by Ecxel as May 20.

Fehlerhafter Import in Excel

However, we atually would like to have a decimal number for calculating. To achieve this, we only have to inform Excel which file typ the column should have.

Step 1: First open Excel and then the file within Excel (see above)

Step 2: When opening CSV-files in Excel, Excel displays further options. Here, you can specify which format you want for individual columns.

In order to import decimal numbers correctly according to the American notation, you have to specify that a dot (.) is the decimal mark in Excel 2000.

Datentyp einzelner Spalten festlegen

Excel imports Formulas instead of Texts / #NAME?

If you use open text fields, some participants may use hyphens (-) to list several points. Excel misinterprets this as a minus sign and wants to calculate. Since the participants did not deposit a “proper formula”, only the error message #NAME? appears.

Fehlerhafter Import in Excel

Once again, you have to specify the file typ of the column manually. This time to “Text”.

Änderung des Spaltentyps auf Text

There are only ########## in the STARTED Column

When displaying sharp signs (######) Excel only wants to inform that the column is too narrow to show its entire content. You can simply drag the column wider. Alternatively, you can click with the right mouse button onto corresponding column header, select Column Width and enter a larger value.

Spaltenbreite mittels Kontextmenü ändern

The Data/Columns Are Moved

If you use a older Excel version it may occur that the data are moved apparenty after their import. There are missing data in the end of the previous line.

Verschobene Daten in Excel

Usually this happens when a participant has entered a text with a line breack (Return) and your Excel cannot cope with this yet.

The solution to this problem is that SoSci Survey replaces all line breaks by the abbreviation <br>. For this purpose you have to deactivate the option Keep line breaks in text when downloading the data.

Multiline Text Inputs are incomplete

Sometimes Excel only displays the first line for multiline text inputs. Normally the rest of the text is just hidden:

  • Increase the total line height to see the complete text
  • Select the respective line and press the key F2

File is not loaded completely

Older versions of Excel support only 255 columns (variables). While loading a message can be displayed that the data are not loaded completely – additionally a part of the variables are missing.

In this case only a newer version of Excel or OpenOffice helps along.

en/results/import-excel.txt · Last modified: 24.11.2013 21:50 by verajane
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
Driven by DokuWiki