Home > Data Analysis > Importing data into SPSS Statistics (part 2)

Importing data into SPSS Statistics (part 2)

SPSS logoThere are many ways to bring your data into IBM SPSS Statistics, for whatever manner of analysis or reorganisation you wish to perform. Here are a few approaches to consider, with some of their relative merits and shortcomings. Please read Part 1 of this blog post before proceeding, which covers opening an existing data source in SPSS Statistics format and typing or pasting in data.

Approach 2: Read text data directly from other formats

You can open data files created in other applications, from the File -> Open menu or the File -> Read Text Data menu. (If you spot any difference between these two, please comment below.) The file type choices include:

  • Text (*.txt, *.dat, *.csv) — tab, comma or otherwise-delimited
  • Excel (*.xls, *.xlsx, *.xlsm) — one sheet only
  • SAS (*.sas7bdat or others) — one data set
  • Stata (*.dta)

The plain text or Excel document may have variable names in the first row, but only the first row. It may instead have no variable names at all. It is worth preparing your source data file and checking that it has no surprises for SPSS like a variable type changing from 2 decimal places to no decimal places to text within the same column.

Preparing an Excel document

Excel original data layoutThis example uses UK carbon emissions data across difference streams from 1997 to 2011, starting in an Excel document with several sheets and some formatting.

  • The numerical data has 9 decimal places but is formatted to show none.
  • The variable names are split over two rows and contain space characters.
  • The data does not start in the top-left corner.

Excel file with prepared data layoutThe images to the right show how the data has been copied to a new workbook with one worksheet (tab).

  • The variable names are in the first row only, with no spaces or punctuation (although underscores and dots would be okay).
  • The data starts from the first column (which contains an identifier “year”), and from the second row.

Example: reading text directly from Excel (quick)

SPSS: CO2 import from ExcelIn, SPSS, choose the File -> Read Text Data menu and select your prepared Excel document. You can choose if the file has variable names in the first row or now, choose which sheet to read from, and optionally specify a cell range and maximum column character width for string (text) columns.

SPSS file imported from ExcelAfter clicking on OK, the data will immediately appear in SPSS (see right). The variable types were guessed by SPSS based on the content of the first row of actual data, and the number of decimal places is set to 1 for numerical data. Although you can change the number of decimal places in the Variable View tab, the data past the first place is lost. The identifier variable “year” has had one decimal place added too, which should be removed too.

This method does not allow you to change the variable type or properties, so consider the longer approach outlined in Part 3, going via a plain text file.

This post continues with Part 3.


IBM SPSS Statistics version 20 was used in this blog post, but the methods should apply to older and newer versions too. The data used in these examples comes from UK’s carbon footprint, 1997-2011, retrieved 3 April 2014.

See also: Research Financial: Using plain text files in Excel


Categories: Data Analysis Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.