Reading Excel files
Complete the steps to import excel files to IBM® SPSS® Statistics. From the Read Excel file dialog box, choose appropriate options to import excel files in a customized manner.
This topic applies to Excel 95 and later files. To read Excel 4 or earlier versions, see the topic Reading Older Excel Files and Other Spreadsheets.
- From the menus choose:
IBM SPSS Statistics.
, or drag an existing Excel file directly into an open instance of - Select the appropriate import settings.
- Worksheet
- Excel files can contain multiple worksheets. By default, the Data Editor reads the first worksheet. To read a different worksheet, select the worksheet from the list.
- Range
- You can also read a range of cells. Use the same method for specifying cell ranges as you would in Excel. For example: A1:D10.
- Are variable names included at the top of your file
- You can read variable names from the first row of the file or the first row of the defined
range. Values that don't conform to variable naming rules are converted to valid variable names, and
the original names are used as variable labels.
- Select Yes to specify the row number that contains variable names.
Specify the row number in you file that contains variable names. Rows before the specified row
number are ignored. The default value of row number is 1.
For example, if you provide the value 4 in the Row number that contains variable names field, rows 1, 2 , and 3 are ignored. Values in row 4 are set as variable names and data starting from row 5 is read.
- Select No if no variable names are added in the first row of your file. A default set of variable names are allotted to your data.
- Select Yes to specify the row number that contains variable names.
Specify the row number in you file that contains variable names. Rows before the specified row
number are ignored. The default value of row number is 1.
- Percentage of values that determine data type
- The data type for each variable is determined by the percentage of values that conform to the
same format.
- The value must be greater than 50.
- The denominator used to determine the percentage is the number of non-blank values for each variable.
- If no consistent format is used by the specified percentage of values, the variable is assigned the string data type.
- For variables that are assigned a numeric format (including date and time formats) based on the percentage value, values that do not conform to that format are assigned the system-missing value.
- Ignore hidden rows and columns
- Hidden rows and columns in the Excel file are not included. This option is available only for Excel 2007 and later files (XLSX, XLSM).
- Remove leading spaces from string values
- Any blank spaces at the beginning of string values are removed.
- Remove trailing spaces from string values
- Blank spaces at the end of the string values are removed. This setting affects the calculation of the defined width of string variables.
- Click OK.