Search code examples
excelimportspss

How to Import Data from excel to SPSS

Sorry if the title is ambiguous or repetitive, but my question is kinda different.

I know how to import data file form excel to SPSS, and by default SPSS reads variable name from excel first row.

My question is that, in SPSS can we specify from which row to read variable names instead of only first row (From 2nd row or third).

For eg:

Excel file I am having have variable names in 3rd row and data from 4th row onward. And when I am importing in SPSS, I only get an option to read variable name from first row (that is vacant in excel). I want to specify that SPSS reads variable name from 3rd row not first row.

---------------xxx----------------

By Directly I mean, when I import through SPSS shortcuts and not via syntax editor.

Syntax used by me (Giving me Error):

GET DATA /TYPE=XLSX
  /FILE='C:\Users\nikhil.gupta\Desktop\Jaspercolin_Barometer_Study_Excel_07Mar15_595Comp.xlsx'
  /SHEET= name 'RID to QIName'
  /CELLRANGE= RANGE 'A6:JB601'
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
DATASET NAME DataSet20 WINDOW=FRONT.

And Syntax when I do the import via SPSS Shortcut(Working):

GET DATA /TYPE=XLSX
  /FILE='C:\Users\nikhil.gupta\Desktop\Jaspercolin_Barometer_Study_Excel_07Mar15_595Comp.xlsx'
  /SHEET=name 'RID to QIName'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
DATASET NAME DataSet1 WINDOW=FRONT.

Error I am getting: 

Error.  Command name: GET DATA 
(2063) Error reading information for sheet. 
* Sheet Name: "RID to QIName" 
Execution of this command stops.

Its reading sheet name When I import via SPSS, but doesn't when I do the same via syntax editor. I guess error points to the same condition, isn't it?

Solution

  • Yes. So when you import data via GUI File-->Open-->Data-->Select Excel Data file

    You will be presented with the following GUI:

    (EDIT: Image upload doesn't seem to be working?!)

    https://www.dropbox.com/s/ni2jdjs36lxwb16/SOImportExcelToSPSSRangeGUI.bmp?dl=0

    enter image description here

    Where you can see the "Range" option which allows you to manually enter a continuous range to select the data from in the Excel file.

    So below is the syntax that would be generated:

    GET DATA /TYPE=XLSX 
  /FILE='C:\Users\JigneshSutar\Documents\ImportExcelToSPSS.xlsx' 
  /SHEET=name 'Sheet1' 
  /CELLRANGE=range 'A2:V129' 
  /READNAMES=on 
  /ASSUMEDSTRWIDTH=32767.

    If not manually selecting a range then SPSS automatically reads the used range in excel and imports that range only. The syntax used for that is CELLRANGE=FULL