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.

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

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

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

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.

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
• File → Open
• Change File Typ to “Text Files (*.prn,*.txt,*.csv)”
• Select the CSV-File → Open
• OK

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.

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.

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.

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

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.

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.

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.

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

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.