Uploading multiple Excel sheets using Excel 2010

[Yvonne Montejano]

Hello folks,

I've got a script that I use to upload multiple spreadsheets that are in
one Excel workbook onto SPSS.  The only problem is that I have to save
all the Excel workbooks from .xlsx to .xls before running the program,
which is a pain because these workbooks are constantly being updated.

Can someone help by telling me if there's some SPSS patch, etc that I
can download so I don't have to change the Excel version to an earlier
one?  Here is what I am using:

Excel: 2010
SPSS: v18
Python: 2.6

Here is the script (and I have changed the extension below from xls to
xlsx, but it doesn't work):

BEGIN PROGRAM.
import spss, spssaux, xlrd

wb = xlrd.open_workbook('R:/Grant Reporting
fy2010/4Q2010/Temp/Supervisions_SAP.xls')
counties = wb.sheet_names()
sheetCount = wb.nsheets

spss.Submit("title 'Number at: %s.'" %(sheetCount, ))

getcmds = " "
getcmd = """GET DATA /TYPE=XLS
 /FILE='R:/Grant Reporting fy2010/4Q2010/Temp/Supervisions_SAP.xls'
 /SHEET=name '%s'.

ALTER TYPE PIDNumber ReferralNumber (N7.0).
ALTER TYPE PIDNumber ReferralNumber (A7).
ALTER TYPE SupervisionBeginDate  (ADATE10).
ALTER TYPE FundingSource (A2).
ALTER TYPE Name FundingSourceDescription (A50).
ALTER TYPE Blended  TC TL TU TX OtherMoney (F1.0).
exe.
dataset name data%s.\n"""
addcmd = "add files "
restcmd = """
select if HQCountyNumber <> " ".
exe.
dataset close all.
"""

for k in range(0,sheetCount):
 spss.Submit("title 'Number at: %s.'" %(counties[k], ))
 getcmds += getcmd %(counties[k], counties[k])
 addcmd += "\n /file = data%s"  % (counties[k],)
spss.Submit(getcmds + addcmd + "." + restcmd)

dataVars = spssaux.GetVariableNamesList()
nbVar = spss.GetVariableCount()

for i in range (0,nbVar-1):
        if dataVars[i].count('V') > 0:
                spss.Submit("DELETE VARIABLES %s." %(dataVars[i],))

END PROGRAM.


Thanks in advance for your help!
C. Yvonne Montejano
Research Specialist
Texas Juvenile Probation Commission
tel [phone]
fax [phone]

=====================
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

[Jon K Peck]

Last time I looked, xlrd did not support the newer Excel formats.  Changing the file extensions will not help.

Here is a little program that will work if you know how many sheets are in a file.

begin program.
import spss, spssaux
for i in range(3):
  spssaux.OpenDataFile("filespec.xlsx", dataset="ds"+str(i),
     filetype="xlsx", sheetid =i+1)
end program.

This works with V19 and should work with 18, too.  If you don't know the number of sheets but know the maximum, just wrap in a try block and quit when you get an error.

HTH,


Jon Peck
Senior Software Engineer, IBM
[hidden email]
312-651-3435



From:        Yvonne Montejano <[hidden email]>
To:        [hidden email]
Date:        10/07/2010 08:48 AM
Subject:        [SPSSX-L] Uploading multiple Excel sheets using Excel 2010
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello folks,

I've got a script that I use to upload multiple spreadsheets that are in
one Excel workbook onto SPSS.  The only problem is that I have to save
all the Excel workbooks from .xlsx to .xls before running the program,
which is a pain because these workbooks are constantly being updated.

Can someone help by telling me if there's some SPSS patch, etc that I
can download so I don't have to change the Excel version to an earlier
one?  Here is what I am using:

Excel: 2010
SPSS: v18
Python: 2.6

Here is the script (and I have changed the extension below from xls to
xlsx, but it doesn't work):

BEGIN PROGRAM.
import spss, spssaux, xlrd

wb = xlrd.open_workbook('R:/Grant Reporting
fy2010/4Q2010/Temp/Supervisions_SAP.xls')
counties = wb.sheet_names()
sheetCount = wb.nsheets

spss.Submit("title 'Number at: %s.'" %(sheetCount, ))

getcmds = " "
getcmd = """GET DATA /TYPE=XLS
/FILE='R:/Grant Reporting fy2010/4Q2010/Temp/Supervisions_SAP.xls'
/SHEET=name '%s'.

ALTER TYPE PIDNumber ReferralNumber (N7.0).
ALTER TYPE PIDNumber ReferralNumber (A7).
ALTER TYPE SupervisionBeginDate  (ADATE10).
ALTER TYPE FundingSource (A2).
ALTER TYPE Name FundingSourceDescription (A50).
ALTER TYPE Blended  TC TL TU TX OtherMoney (F1.0).
exe.
dataset name data%s.\n"""
addcmd = "add files "
restcmd = """
select if HQCountyNumber <> " ".
exe.
dataset close all.
"""

for k in range(0,sheetCount):
spss.Submit("title 'Number at: %s.'" %(counties[k], ))
getcmds += getcmd %(counties[k], counties[k])
addcmd += "\n /file = data%s"  % (counties[k],)
spss.Submit(getcmds + addcmd + "." + restcmd)

dataVars = spssaux.GetVariableNamesList()
nbVar = spss.GetVariableCount()

for i in range (0,nbVar-1):
       if dataVars[i].count('V') > 0:
               spss.Submit("DELETE VARIABLES %s." %(dataVars[i],))

END PROGRAM.


Thanks in advance for your help!
C. Yvonne Montejano
Research Specialist
Texas Juvenile Probation Commission
tel [phone]
fax [phone]

=====================
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

[kelvidpang]

Hi,

What if I want to write additional code to determine the number of sheets in the workbook?

Appreciate the reply.