Reading Excel Data

Rather than typing all of your data directly into the Data Editor, you can read data from applications such as Microsoft Excel. You can also read column headings as variable names.

1. From the menus choose:

   File > Import Data > Excel

2. Go to the Samples\English folder and select demo.xlsx.

   The Read Excel File dialog displays a preview of the data file. The contents of the first sheet in the file are displayed. If the file has multiple sheets, you can select the sheet from the list.

   You can see that some of the string values for Gender have leading spaces. Some of the values for MaritalStatus are displayed as periods (.).

   

3. Make sure Read variable names from the first row of data is selected. If the column headings do not conform to variable name rules, they are converted to valid variable names. The original column headings are saved as variable labels.
4. Select Remove leading spaces from string values.
5. Deselect Percentage of values that determine data type.
   

   The string value "no answer" is now displayed in the cells that were system-missing. If there is no percentage of values parameter and the column contains a mix of data type, the variable is read as a string data type. All values are preserved, but numeric values are treated as string values.

6. Select (check) Percentage of values that determine data type to treat MaritalStatus as a numeric variable.
7. Click OK to read the Excel file.

The data now appear in the Data Editor, with the column headings used as variable names. Since variable names can't contain spaces, the spaces from the original column headings are removed. For example, the column heading"Marital Status" is converted to the variable MaritalStatus. The original column heading is retained as a variable label.

Next