SPSS Tutorials: 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.

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.
• Missing values for string or numeric variables have blank (empty) cells, or an appropriate predetermined missing value code (such as -999).

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:

1. Click File > Open > Data. The Open Data window will appear.

   

2. 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, “Excel” is selected as the file type, so only Excel files in the current folder are visible.

3. If you are using SPSS Version 25, the Read Excel File window will appear.

   

   • In the Worksheet dropdown menu, 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.
   • If your variable names are in the first row of data, select the Read variable names from the first row of data check box.
   • The options Remove leading spaces from string values and Remove trailing spaces from string values only affect variables that are imported as strings. The former will remove any whitespace characters that appear at the start of the string, and the latter will remove any whitespace characters at the end of the string. This is optional, but is often convenient, since leading and trailing spaces can appear invisible to the user, but cause SPSS to think that otherwise identical strings are distinct.
   • You may also specify the range of rows/columns to import 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.

4. If you are using SPSS version 24 or earlier, you will instead see the Opening Excel Data Source window:

   

   The meaning of Read variable names from the first row of data checkbox, the Worksheet dropdown, and the Range box are the same as above. The Maximum width for string columns option determines how wide a string variable should be; it is suggested to keep the default value unless you have a reason for altering it.

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 /TYPE=XLS.

The most important lines in this code are /FILE='' and /SHEET=name ''.

• The /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.

• The /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 /SHEET=name 'Name-of-Sheet' with /SHEET=index 1.)

It's only possible to import 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.

Data stored in text files have extensions such as *.txt, *.dat, or *.csv. These types of data files are simple to create and are not tied to a proprietary software, so they are a popular choice for data files. While many computers will automatically open these file types in a spreadsheet software like Microsoft Excel, they can be opened and edited using any text editor program.

Importing text files into SPSS is slightly different than importing data in Excel spreadsheets. There are several different patterns used to delineate the start and end of a particular variable, and SPSS must know what pattern to follow in order to read the data correctly.

In general, there are two patterns that SPSS recognizes:

Delimited data: Each observation is delimited, or separated, by a particular character. Common characters used for delimiters include commas, tabs, and whitespace.

ID,Age,Gender
A001,41,F
A009,36,M
C321,27,F

Fixed-width data: Rather than using delimiters between observations, the values of the variables are aligned vertically, so that a given variable always begins in a certain column position. In the below example, ID always begins in column 1; Age always begins in column 10; and Gender always begins in column 16.

ID       Age   Gender
A001R    41    F
Z009     36    M
C321BC   27    F

Files with the extension *.txt are called text files. This file type can contain fixed-width or delimited data. A common variation for *.txt files is tab-delimited data; that is, each observation is separated by a tab (created using the Tab key on the keyboard). However, *.txt files do not always use tabs as delimiters -- in fact, *.txt files can use any character as a delimiter, including commas.

Files with the extension *.csv are called comma-delimited files; in this type of file, the observations are delimited by a comma. Traditionally, the first row of a CSV file contains the variable names (separated by a comma), and the first row of data begins on the second line. Missing values are denoted using adjacent delimiters.

The process for importing any of these file types is virtually identical in SPSS, so here, we will demonstrate using only a CSV file.

Importing a CSV File (SPSS 23 and later)

To import a CSV data file into SPSS, begin by clicking File > Open > Data. In the Open Data window, change Files of type to "CSV (*.csv)". Locate your file and click on it to select it, then click OK. This will start the Text Import Wizard process, which will walk you through the process of specifying how SPSS should read and interpret the data file.

Step 1 of 6

This 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. (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.) In most cases there will not be a predefined format.

If your data matches a predefined format, click Yes and then browse for and upload the file that defines the format.

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 arranged, so that it understands where one column ends and the next begins. For text files, there are two types of "arrangements": delimited and fixed width. If you are importing a CSV file, you have delimited data. You will also need to tell SPSS if the datafile contains variable names. For CSV files, variable names are typically included on the first line of the data file, before the data begins; however, some datafiles do not include variable names.

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.

If necessary, choose the symbol used to denote decimals. Then click Next.

Step 3 of 6

We now need to tell SPSS what row our data begins on, and how many rows should be read. For CSV files, the first row typically contains the variable names, and the data values begin on line 2. However, you can choose to skip over certain lines if necessary. (One example where this occurs is in Qualtrics survey data output to CSV: The second row frequently contains variable labels, and oftentimes there may be a third row containing import IDs, and the data actually begins on line 4.) Lastly, if you only want to import a selection of cases -- for example, the first 1000 cases, or a random sample of 10% of the cases -- you can opt to do so on this screen.

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.

The options Remove leading spaces from string values and Removing trailing spaces from string values were added in SPSS version 25. If you are using SPSS version 24 or earlier, you will not see these options on this window.

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 what criteria it uses to "guess" what format to use. In SPSS versions 24 and earlier, the default format chosen for a given variable is based on the values present in the first 200 records. In SPSS version 25, the default format chosen is based on 95% of the cases.

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 change the variable name or alter its 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 variable formats; this ensures that you control exactly how each variable was read.

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 will import data with the same structure and format in the future. You can also choose to save the syntax, which is SPSS’s 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.

Shortcut for Importing CSV Files (SPSS 25)

In any version of SPSS, you can open a text or CSV file by using File > Open > Data. Prior to SPSS version 25, you could alternatively use File > Read Text Data to begin importing a text or CSV file; this functioned identically to File > Open Data. In SPSS version 25, Read Text Data has been removed from the File menu, and replaced with the File > Import Data submenu.

Clicking File > Import Data > CSV will open the Read CSV File window:

This prompt attempts to simplify the import process, so certain actions (like renaming variables before importing, or manually specifying variable formats) are removed from this screen. If you need this level of control, you can click the Advanced Options button; this will open the Text Import Wizard (described in the previous section).

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:

1. In the active data window, click File > Save As. The Save Data As window will appear.
2. Choose the directory where you want the file to be saved.
3. 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.
4. When you are finished, click Save.