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.

  1. From the menus choose:

    File > Import Data > Excel..., or drag an existing Excel file directly into an open instance of IBM SPSS Statistics.

  2. 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.
    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.
  3. Click OK.