Spreadsheets (GET TRANSLATE command)

A spreadsheet file suitable for this program should be arranged so that each row represents a case and each column, a variable.

  • By default, the new active dataset contains all rows and up to 256 columns from Lotus 1-2-3, Symphony, or Excel, or up to 255 columns from Multiplan.
  • By default, GET TRANSLATE uses the column letters as variable names in the active dataset.
  • The first row of a spreadsheet or specified range may contain field labels immediately followed by rows of data. These names can be transferred as IBM® SPSS® Statistics variable names. See the topic FIELDNAMES Subcommand (GET TRANSLATE command) for more information.
  • The current value of a formula is translated to the active dataset.
  • Blank, ERR, and NA values in 1-2-3 and Symphony and error values such as #N/A in Excel are translated as system-missing values in the active dataset.
  • Hidden columns and cells in 1-2-3 Release 2 and Symphony files are translated and copied into the active dataset.
  • Column width and format type are transferred to the dictionary of the active dataset.
  • The format type is assigned from values in the first data row. By default, the first data row is row 1. If RANGE is specified, the first data row is the first row in the range. If FIELDNAMES is specified, the first data row follows immediately after the single row containing field names.
  • If a cell in the first data row is empty, the variable is assigned the global default format from the spreadsheet.

The formats from 1-2-3, Symphony, Excel, and Multiplan are translated as follows:

  • If a string is encountered in a column with numeric format, it is converted to the system-missing value in the active dataset.
  • If a numeric value is encountered in a column with string format, it is converted to a blank in the active dataset.
  • Blank lines are translated as cases containing the system-missing value for numeric variables and blanks for string variables.
  • 1-2-3 and Symphony date and time indicators (shown at the bottom of the screen) are not transferred from WKS, WK1, or SYM files.