Importing spreadsheet data into SPSS

Steve Simon

1999-09-20

Categories: Blog post Tags: Data management SPSS software

*Dear Professor Mean

Dear Stumped,

Nothing works right the first time

Here are the four general steps that I recommend for someone who is importing Excel data.

  1. Close the Excel file before you try to import in SPSS.
  2. Arrange the data in a rectangular grid
  3. Don’t mix strings and numbers.
  4. Put descriptive names in your first row.

Rectangular grid

A rectangular gird is a systematic layout of your data so that that the intersection of every column and row contains a single number. The data should start in the first row of the spreadsheet

Be sure to delete any rows of your spreadsheet that contains summary data like totals or means. You don’t want SPSS to think that this summary row is just another row of data.

Don’t mix strings and numbers.

A mixture of strings and numbers in a single column will confuse SPSS. SPSS uses the first value that it sees in a column to decide if that column should be stored using string

Here’s an example of a mixture of strings and numbers “1”

Provide brief descriptive names

SPSS can use the first row of your spreadsheet as variable names

The name has to be one word with no blanks. You can use the underscore symbol “_” or the dot to simulate blanks. You can also use MixedCapitalization to simulate blanks.

Avoid special symbols (other than the underscore and dot). Symbols like the dash (-) and the slash (/) cause problems because they imply some sort of arithmetic operation.

A variable name like “Mother’s Age” causes problems because it includes a special symbol (the apostrophe) and it has a blank. If you tried to use this name

It takes some creativity to describe a variable well with only eight characters. Do the best you can. Remember that you can always add a lengthy variable label later that has blanks

Close the Excel file

SPSS cannot import data from a spreadsheet that is currently open in Excel. Excel marks that spreadsheet as “in use” and SPSS avoids importing anything with such a mark. This actually provides an extra level of security

Here is an Excel spreadsheet with data from a breast feeding study. I have already arranged the data in a rectangular grid and placed brief descriptive names in the first row..

Make sure that Excel is closed before you try to import in SPSS. SPSS is very jealous. It will not want to open your data file if it knows that some other software is currently using it. SPSS will warn you about a “sharing violation”.

Open SPSS and select FILE | OPEN from the menu. Here is the SPSS dialog box that you will see. Click on the down arrow in the FILE OF TYPE field and select the EXCEL (*.XLS) option. Find your file on the proper drive and folder of your computer.

When you click on the OPEN button

Check if you got the correct number of variables (columns) and cases (rows). A common problem is that SPSS will sometimes import a bunch of extra blank rows. You can delete the blank rows manually.

Here is what the SPSS data window looks like. We are now ready to do things like adjusting the number of decimal places displayed and adding documentation.

Summary

If you want to import Microsoft Excel data into SPSS

  1. Close the Excel file
  2. Arrange the data in a rectangular grid
  3. Don’t mix strings and numbers.
  4. Put descriptive names in your first row.

Once you have done this

You can find an earlier version of this page on my original website.