Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 39 Β· Interface and Data

Importing Data into SPSS from Excel, CSV and Text Files

Import data into SPSS from Excel, CSV and text files: dialog options, the Text Import Wizard, GET DATA syntax, data type guessing, dates, and how to fix common import problems.

Most real datasets do not start life in SPSS. They arrive as Excel workbooks, CSV exports from survey tools, text files from other programs or database extracts. Importing is where many analyses quietly go wrong: a column of numbers becomes text, dates turn into strange numbers or a header row becomes a case. This lesson shows the import paths and a checklist for verifying what came in.

The import menu

Current versions group the import options under File > Import Data:

  • Database (ODBC; New Query or Edit Query),
  • Excel...,
  • CSV Data...,
  • Text Data... (opens the Text Import Wizard),
  • plus entries for SAS, Stata and other formats.

You can also use File > Open > Data... and set Files of type: SPSS, Excel (*.xls, *.xlsx, *.xlsm), CSV, text, SAS, Stata and more. For Excel files it opens an import dialog; for text files it starts the wizard.

Importing from Excel

Choose File > Open > Data..., select Excel under Files of type, and pick the file. The Opening Excel Data Source dialog has these options:

  • Read variable names from the first row of data: tick it if row 1 holds headings. The headings become variable names (SPSS fixes illegal ones, e.g. spaces become underscores).
  • Worksheet: choose the sheet to import. Each import reads one sheet.
  • Range: restrict to cells such as A1:H200, or a named range.
  • Ignore hidden rows and columns.

After OK, the data appear in Data View as an unsaved dataset. The equivalent syntax:

TEXT
GET DATA
  /TYPE=XLSX
  /FILE='C:\data\sales.xlsx'
  /SHEET=name 'Orders'
  /CELLRANGE=FULL
  /READNAMES=ON.
DATASET NAME orders WINDOW=FRONT.

Prepare the sheet first

Excel is flexible, SPSS is not. Before importing, tidy the sheet:

  1. One header row, one variable per column, no merged cells.
  2. One case per row, no blank rows, no subtotals or notes below the data.
  3. Consistent data: a column must hold either numbers or text, never both ("n/a" in a numeric column turns the whole column to string).
  4. Real Excel dates, not text such as 2025.03.01.
  5. No formulas that return errors (#DIV/0!).
Python Stand-in: guess variable types like an import does

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the age column: one n/a turns the whole column into text. In SPSS you would see an A-type variable that cannot be averaged. The cure is to clean the source (empty cell or a code such as 99 that you declare missing) or to convert afterwards.

Importing CSV and text files

A CSV file is plain text with a separator between values. File > Import Data > CSV Data... opens a dialog where you check the delimiter, the header, text qualifier and the decimal symbol. File > Import Data > Text Data... opens the Text Import Wizard, which has six steps:

  1. Predefined format? (usually No).
  2. How are your variables arranged? Delimited (a character separates values) or Fixed width (columns at fixed positions). Also: Are variable names included at the top of your file?
  3. Where does the first case start, how are cases represented (each line a case, or a set number of variables per case), and how many cases to import (all, first N or a random percentage).
  4. Delimiters: comma, tab, semicolon, space or others; the text qualifier (usually the double quote), and the preview.
  5. Variable names and data formats: click a column in the preview to set its Name and Data Format (Numeric, String, Date, Dollar...).
  6. Finish: save the format as a text file for reuse, Paste the syntax instead of running, or just Finish.

The pasted syntax is a GET DATA /TYPE=TXT command. Save it with your project so the import is repeatable:

TEXT
GET DATA
  /TYPE=TXT
  /FILE='C:\data\survey.csv'
  /ENCODING='UTF8'
  /DELIMITERS=","
  /QUALIFIER='"'
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=2
  /VARIABLES=
    id F4.0
    name A20
    age F3.0
    joined EDATE10
    score F5.1.

Format codes: F4.0 numeric width 4 with 0 decimals, A20 string of 20, EDATE10 a date such as 01.03.2025 (day, month, year), ADATE10 a US-style date (mm/dd/yyyy), DATE11 like 01-MAR-2025, SDATE10 yyyy/mm/dd.

Three classic traps

Decimal and list separators. In some locales a comma is the decimal mark and a semicolon the list separator. If 3,5 imports as text, set the decimal symbol (comma) and delimiter (semicolon).

Encoding. A CSV with accents (Γ©, ΓΌ) must be imported with the right encoding, usually UTF-8; otherwise names look garbled. Set it in the import dialog or /ENCODING='UTF8'.

Leading zeros. A ZIP code 02134 loses its zero if read as numeric. Read it as String (or Restricted Number).

Python Stand-in: delimiter and decimal comma

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Opening other statistical formats

SPSS can also read SAS (.sas7bdat), Stata (.dta), dBase and others, usually with labels carried over. For databases use File > Import Data > Database > New Query, pick a data source, choose tables and fields, and optionally edit the SQL. Large tables should be filtered in the query instead of loaded entirely.

After importing: a checklist

Never analyze straight after an import. Run this sequence:

  1. Check number of cases and variables (the status bar or File > Display Data File Information > Working File).
  2. Open Variable View: are numeric columns numeric? Are dates dates? Are strings the right width (truncated names are a sign it is too small)?
  3. Scroll Data View: does the first row look right, or is the header row now a case?
  4. Run Frequencies on key categorical variables to spot spelling variants (Male, male, M).
  5. Run Descriptives on numeric variables to spot impossible values (age 999).
  6. Set labels, value labels, missing codes and Measure in Variable View.
  7. Save as .sav immediately (see saving, exporting and file types).
Python Stand-in: a post-import sanity check

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Importing a sheet with a title row above the headings, so the title becomes the variable names.
  • A single stray text value that makes a numeric column a string.
  • Reading dates as strings, then being unable to compute durations.
  • Opening a large CSV in Excel first, which silently reformats IDs and dates, and then importing the damaged file.
  • Not saving the import syntax, so you cannot repeat it when the source file is updated.

Check your understanding

Quick check Β· 4 questions

of 4 answered You scored / 4 Β· perfect!

  1. 1. A numeric column in Excel contains one cell with the text 'n/a'. What does SPSS usually do?

    Mixed content makes the column text. Clean the source or declare a numeric missing code.
  2. 2. Which syntax command reads an Excel workbook?

    GET DATA with /TYPE=XLSX (or XLS) and /FILE reads an Excel file.
  3. 3. Why should you read ZIP codes such as 02134 as strings?

    Numeric storage treats 02134 as 2134. A string keeps the zero.
  4. 4. In the Text Import Wizard, what does the text qualifier do?

    A value like "Smith, John" is kept in one cell because the quotes protect the comma.

Summary

  • Import via File > Import Data or File > Open > Data; Excel uses one sheet at a time and optional header names.
  • CSV and text files go through the dialog or the six-step Text Import Wizard; paste the GET DATA syntax to repeat it.
  • Watch for mixed columns, decimal and list separators, encoding and leading zeros.
  • Always check cases, variable types, dates and plausible values right after import, then save a .sav.

Next: Value Labels and Missing Values