How to...
Moving Excel data into SPSS
Moving Excel data into SPSS
Excel has very powerful data manipulation facilities and some basic statistical routines, but you might wish to move data from Excel into other statistics packages. Following the instructions below will enable SPSS to read almost any Excel spreadsheet.
What can I transfer?
An SPSS data set consists of a rectangular table in which each case (subject) is a row, and each variable is a column with a unique name. You can transfer any rectangular area from Excel, either the whole spreadsheet or a range, such as A1:K14, provided that it does not have more than 256 columns. If this area contains only data, then SPSS will use the standard Excel column names, A, B, … Z, AA, BB, … from the specified area.
You can tell SPSS that the top row of the area contains the names of the variables. SPSS then uses those names, providing that they conform to the SPSS naming rules.
Briefly, these are:
- That names are no more than eight characters long, which consist of letters, digits, the symbols @ # $ . or _ the first of which must be a letter or @ sign.
- Names should not end with a period (.) or underscore (_).
- You may not use the names ALL, AND, BY, EQ, GE, GT, LE, LT, NE, NOT, OR, TO, or WITH. If SPSS encounters names which are in breach of these conventions, it will try to alter them to make them conform. Names which are too long will be shortened, and GE, for example, would be changed to GE$.
- Any fields with blank names will be dropped.
SPSS will examine the first row of data, and decide whether each value is alphabetic or numeric. If a column begins with a numeric, then subsequent alphabetic codes will be treated as missing values, and empty cells will also be treated as missing values.
If the first value in a column is missing, SPSS will not know whether the column is to be numeric or alphabetic, so you may wish to enter a value into the first cell as a guide, and delete it after the sheet has been converted.
All cells with the selected range will be converted, so blank rows create rows with all values ‘missing’, and if you have created column means or totals, these will become part of the data. The SPSS file will end with the last row of the spreadsheet which is not entirely empty.
SPSS cannot convert a formula, so you may need to convert them to values within Excel. First select and copy the formulas, then from the Home tab, Clipboard Group, click on the arrow of the Paste Icon and select Paste Values to convert them to values.
How do I transfer data?
If your data does not conform to the above requirements, you may wish to edit your spreadsheet within Excel before transferring it.
Once you have saved the data in Excel format, you will have to start SPSS and go to the File menu, select Open and then Data. You will now see a dialogue box which is quite similar to the one for opening files in Excel. You will notice a list box for File Type. This assumes that you are going to open an SPSS file, of type *.sav. Open this list and scroll through to select Excel input of type *.xls. Now you can change the drive and directory, if you wish, until you can see and select the name of the file that you want to load.
In the Options box you can check Read Variable Names, if the first row of your spreadsheet has names that you want copied into SPSS. You may also enter a range, e.g. A1:K14, into the range box.
Then click on OK.
Your file should be read. Now open the output window. This reports how SPSS has interpreted the data, and any problems that it encountered. Errors may be corrected within SPSS, or by returning to Excel, modifying the spreadsheet, and transferring the file again.
>
created on 2010-01-01 by Gill Powell
last updated on 2010-08-27 by Gillian Powell