SPSS Tutorials Importing Data into SPSS
This tutorial describes how to import data stored in an Excel (XLS or XLSX) or comma-delimited (CSV) file into SPSS.
Importing Data into SPSS
If you already have data that are in an SPSS file format (file extension “.sav”), you can simply open that file to begin working with your data in SPSS. However, if you have data stored in other types of files, such as an Excel spreadsheet or a text file, you will need to instruct SPSS how to read the file and then save it in the SPSS file format (“.sav”). Below, we will cover how to import data from two common types of files: Excel files and text files.
Importing Data from an Excel File
To import data from an Excel spreadsheet into SPSS, first make sure your Excel spreadsheet is formatted according to these criteria:
- The spreadsheet should have a single row of variable names across the top of the spreadsheet in the first row.
- Variable names should include ordinary letters, numbers, and underscores (e.g., Gender, Grad_Date, Test_1) and not include special characters (e.g., "Graduation Date" would not be a valid variable name because it contains a space).
- The data should begin in the first column, second row (beneath the variable names row) of the spreadsheet.
- Anything that is not part of the data itself (e.g., extra text, labels, graphs, Pivot Tables) should be removed.
Here is an example of what properly formatted data looks like in Excel 2010:
Once the data in your Excel file is formatted properly it can be imported into SPSS by following these steps:
Click File > Open > Data. The Open Data window will appear.
In the Files of type list select Excel (*.xls, *.xlsx, *.xlsm) to specify that your data are in an Excel file. If you do not specify the type of file that you wish to open, your file will not appear in the list of available files. Locate and click on your file. The file name will appear in the File name field. Click Open.
In the screenshot example above, we have selected “Excel” as our file type, located the “My Data” file in which our Excel sheet is located, and selected the Excel file “Sample Data.xlsx.” Now we are ready to click “Open.”
The Opening Excel Data Source window will appear.
- If your variable names are in the first row of data, select the Read variable names from the first row of data check box.
- In the Worksheet list, select the sheet (from your Excel workbook) that contains your data. (If you have not assigned names to the sheets in your Excel workbook, the labels you see here will usually be Sheet1, Sheet2, Sheet3, etc.) You can only import one sheet from your Excel file at a time.
- You may also specify the range and maximum width for string columns if you wish. It is suggested to keep the default value unless you have a reason for altering it.
- Click OK when you are finished.
Now the data will appear in SPSS. Here is an example of how sample data appear in SPSS once the data have been imported:
Importing Excel Files using Syntax
Alternatively, you can import Excel files into SPSS using syntax, instead of using the dropdown menus. The general syntax is:
GET DATA /TYPE=XLSX /FILE='C:\path\to\file.xlsx' /SHEET=name 'Name-of-Sheet' /CELLRANGE=full /READNAMES=on /ASSUMEDSTRWIDTH=32767. EXECUTE. DATASET NAME DataSetExcel WINDOW=FRONT.
Note: If you are importing an *.xlsx file, use
/TYPE=XLSX. If you are reading an *.xls file, use
The most important lines in this code are
/FILE=''line tells SPSS the exact location of the file on your computer (which goes between the quotation marks). The path should use backslashes (\), and should include the full name of the file (including its extension) after the last backslash.
/SHEET=name ''line tells SPSS which sheet of the workbook to read in. Even if your workbook only has a single sheet, you must still tell SPSS which sheet you want to read in. (If you want to refer to a sheet by its position instead of its name -- e.g., read in the first sheet -- simply replace the line
It's only possible to read in one sheet at a time, even if there are multiple sheets in your workbook. If you want to import more than one sheet, you will need to read in each sheet individually.
Importing Data from a Text File
Data stored in text files have extensions such as *.txt, *.dat, or *.csv. Files with the extension *.csv are called comma-delimited files; in this type of file, the observations are separated (or delimited) by a comma. Files with the extension *.txt are called text files, and typically contain tab-delimited data; that is, each observation is separated by a tab (created using Tab on the keyboard). (Note that it's not mandatory that *.txt files use tabs as delimiters -- in fact, *.txt files can use any character as a delimiter, including commas.)
To open text data in SPSS, begin by clicking File > Read Text Data. The Open Data window appears. In the Files of type list, the option "Text (*.txt, *.dat, *.csv)” is selected by default. Locate your file and click on it to select it, then click OK.
The Text Import Wizard window appears:
The window provides a preview of the data in your text file. The first step is to indicate whether the data matches a predefined format, which would be a format saved from a previous text file imported with the Text Import Wizard. In most cases there will not be a predefined format. Follow these steps in the Import Wizard to import the text data.
Step 1 of 6
If your data matches a predefined format, click Yes and then browse for and upload the file that defines the format. (This would be the case if you had already imported a text data file into SPSS in the past that was formatted exactly the same way, and had chosen to save the import format during the last step of the Text Import Wizard.)
If your data do not match a predefined format, click No, then click Next.
Step 2 of 6
If your data did not match a predefined format you will need to tell SPSS how your data is formatted.
In the "How are your variables arranged" area, click the radio button that matches your data's format:
- Delimited: Variable values are delimited (or separated) in the file by a special character, such as a comma or a tab.
- Fixed width: Variables are aligned in fixed width columns.
In the "Are variable names included at the top of your file" area, click Yes or No. Then click Next.
Step 3 of 6
Click the radio buttons, and as necessary the values, that best describe your data for each of the following headings. Click Next when you are finished.
Step 4 of 6
In the "Which delimiters appear between variables" area, select the check box that reflects the delimiter used in your data. The delimiter is what is used to separate values from each other within the data. The options include Tab, Space, Comma, Semicolon, Other. If the text file is a .csv file, then the delimiter is a comma. If you do not know which delimiter is used in the text file, refer to documentation that is associated with your data or ask someone who knows how the data file was created.
SPSS attempts to guess what delimiters your file is using. For example, when using the import wizard to import the sample data in *.csv format, SPSS guesses that both commas and spaces were used as delimiters. Notice how that affects the parsing of variable Major:
Because SPSS thinks that both spaces and commas were used as delimiters, it thinks that the spaces occurring in the names of the majors delimit observations for subsequent variables, and hence, SPSS interprets individual words as readings for the adjacent variable(s) (here, variables Height, Weight, and Smoking). Clearing the check box next to Space will correct this, and the change will be reflected in the preview.
Notice, however, that there was still at least one case where the observation for Major was incorrectly split across two columns. Notice how the value has double quotes (") before the first word and after the last word. These quotes are being used as the text qualifiers; that is, they indicate where the observation starts and ends. To correct this, we need to change the text qualifier option to Double quote.
Click Next when you are finished.
Step 5 of 6
This step allows you to specify the format for each variable in the data file. In the "Specifications for variable(s) selected in the data preview" area, SPSS explains that the default format selected for each variable is based on values that are present in the first 200 records.
In the Data Preview area, SPSS displays a preview of how your data will appear in SPSS once the import is complete. You can select any of the variable names (columns) in order to alter the data format. For example, in the example above we have selected the variable bday in the Data Preview area. This variable name now appears in the Variable name area above, and the Data format is also displayed—which, in this example, is Date/Time. You can change the format of the variable by selecting a different format from the drop down menu; here, we have selected mm/dd/yyyy to match the formatting of the observations in this column. It is important to check every variable to make sure the format and length is correct rather than relying on SPSS to correctly identify the parameters.
Step 6 of 6
In the final step, you can choose to save the file format that you just defined for the current data file in case you need to use it for future file importations. You can also choose to save the syntax, which is SPSS’ text-based command language. Saving the syntax would allow you to format future data files in the same way without having to manually select each of the options in the Import Wizard again.
A preview of your data appears at the bottom of the dialog box. If you are satisfied with the way the preview looks, click Finish to finalize the import. Now your data should appear in SPSS in Data View window. It is a good idea to save your newly imported data as an SPSS file (extension “.sav”) so that you can easily open the file in SPSS in the future.
Saving Imported Data in SPSS Format
After your data file has successfully been imported, you'll want to save the the result as an SPSS data file (*.sav format) by following these steps:
- In the active data window, click File > Save As. The Save Data As window will appear.
- Choose the directory where you want the file to be saved.
- Type a name for your file in the File name field.
- Note that the Save as type list indicates that the file will be saved as an “SPSS Statistics (*.sav)” file. You may also select other file types for use in other statistical software programs (e.g., SAS or Stata, etc.).
- If you wish to save only certain variables in your data set, click Variables and select the variables you wish to keep in your saved data file. Click Continue.
- If you wish to protect your data file with a password, select the Encrypt file with password check box.
- When you are finished, click Save.