This tutorial describes how to import data stored in an Excel (XLS or XLSX) or comma-delimited (CSV) file 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:
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.
Now the data will appear in SPSS. Here is an example of how sample data appear in SPSS once the data have been imported:
After your data file has successfully been imported, you'll want to save the SPSS datafile by following these steps:
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.
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.
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:
In the "Are variable names included at the top of your file" area, click Yes or No. Then click Next.
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.
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. You also might be able to see what the delimiter is in the first step when the data are displayed. Most datasets will have only one delimiter, but it is possible to have more than one. It is important to know what the delimiter for the dataset is. If you choose the wrong delimiter your data will read into SPSS incorrectly.
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 State_Residency:
Because SPSS thinks that spaces and commas were used as delimiters, it thinks that the spaces between "In" and "state" denote separate variables, and hence, SPSS interprets the word "state" as a reading for the adjacent variable (here, variable Siblings). Clearning the check box next to Space will correct this, and the change will be reflected in the preview.
Next you will need to select the text qualifier. A text qualifier is the character that is placed at the beginning and end of a string value. For example, if you have a string variable Gender that takes on the values of “Male” or “Female” in the dataset, those values might be represented in a number of possible ways. For example, the values might appear without a text qualifier (e.g., Male, Female), with a single quote qualifier (e.g., ‘Male’, ‘Female’), or with a double quote qualifier (e.g., “Male”, “Female”). (Note that not all datasets are formatted so that strings are contained within single- or double-quotes; for example, the sample data files do not use quotes around the values of the string variable "state_residency".) Again, if you don’t know what the text qualifier is, check the dataset documentation or with someone who is familiar with the dataset. You can also look at the data preview in the first window of the wizard to see what text qualifiers, if any, are associated with the values for your string variables. In the "What is the text qualifier" area, select the appropriate choice from the radio button options: None, Single quote, Double quote, Other [insert text].
Click Next when you are finished.
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 StudentID 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 Numeric. You can change the format of the variable by selecting a different format from the drop down menu. 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.
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.