reading multiple excel sheets in version 21

[Art Kendall]

I have XLSX files that have multiple sheets.
The sheet names look like
1997
1997 other stuff
1998
1998 other stuff
1999
1999 other stuff
...
2011 other stuff.

I would like to create two files.  Each file would have a new variable
refer_year added to each case that says which year it refers to.
The first file would be action.sav.  It would stack (concatenate) all of
the records from sheets that had just a year as the sheet name.
The second file would be people.sav.  It would stack all of the records
from sheets that have both year and other in the sheet name


I am trying to adapt some python code from Albert-Jan.
but get this error
> >Error # 6890.  Command name: BEGIN PROGRAM
> >Configuration file spssdxcfg.ini is invalid.
> >Execution of this command stops.
> Configration file spssdxcfg.ini is invalid because the LIB_NAME is NULL.
This is the syntax for the sheets with just the year as the sheet name.

BEGIN PROGRAM.
import spss
getcmds = ""
getcmd = """GET DATA /TYPE=XLSX
    /FILE='C:\project\whatever.xlsx
    /SHEET=name '%02d'
    /CELLRANGE=full
    /READNAMES=on
    /ASSUMEDSTRWIDTH=32767.
string name (a4).
compute name = '%02d'.
exe.
dataset name data_%02d.\n"""
addcmd = "add files "
restcmd = """
exe.
dataset close all.
save outfile = 'c:\project\actions.sav'.
"""

for year in range(1997, 2011+1):
     y = year % 100
     getcmds += getcmd % (y, y, y)
     addcmd += "\n  /file = data_%02d" % y
spss.Submit(getcmd + addcmd + "." + restcmd)
END PROGRAM.


--
Art Kendall
Social Research Consultants

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi Art,

 

You can find spssdxcfg.ini in the spss installation folder. When you install the plugin(s) some stuff is appended to the ini-file (e.g. where Python and/or R is located). The error means that the plugin(s) are not (correctly) installed.

That is, the LIBNAME part cannot extracted from the ini file (ie, is NULL), maybe because the ini file does not exist altogether. I recently experienced a similar thing, but it was related to Symantec Bubble and I won't bore you with that unless you're also using that admin tool.

 

One point about the code: I'd use a triple-quoted raw string r""""<code>""" as the syntax contains backslashes that might be interpreted as nonprintable symbols (e.g. \t for tab in d:\temp)

 

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

From: Art Kendall <[hidden email]>
To: [hidden email]
Sent: Wednesday, August 15, 2012 5:59 PM
Subject: [SPSSX-L] reading multiple excel sheets in version 21

I have XLSX files that have multiple sheets.
The sheet names look like
1997
1997 other stuff
1998
1998 other stuff
1999
1999 other stuff
...
2011 other stuff.

I would like to create two files.  Each file would have a new variable
refer_year added to each case that says which year it refers to.
The first file would be action.sav.  It would stack (concatenate) all of
the records from sheets that had just a year as the sheet name.
The second file would be people.sav.  It would stack all of the records
from sheets that have both year and other in the sheet name


I am trying to adapt some python code from Albert-Jan.
but get this error
> >Error # 6890.  Command name: BEGIN PROGRAM
> >Configuration file spssdxcfg.ini is invalid.
> >Execution of this command stops.
> Configration file spssdxcfg.ini is invalid because the LIB_NAME is NULL.
This is the syntax for the sheets with just the year as the sheet name.

BEGIN PROGRAM.
import spss
getcmds = ""
getcmd = """GET DATA /TYPE=XLSX
  /FILE='C:\project\whatever.xlsx
  /SHEET=name '%02d'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
string name (a4).
compute name = '%02d'.
exe.
dataset name data_%02d.\n"""
addcmd = "add files "
restcmd = """
exe.
dataset close all.
save outfile = 'c:\project\actions.sav'.
"""

for year in range(1997, 2011+1):
    y = year % 100
    getcmds += getcmd % (y, y, y)
    addcmd += "\n  /file = data_%02d" % y
spss.Submit(getcmd + addcmd + "." + restcmd)
END PROGRAM.


--
Art Kendall
Social Research Consultants

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD