SPSSX Discussion

Error when merging multiple Excel spreadsheets into 1 SPSS dataset

classic Classic list List threaded Threaded
♦
♦
 2 messages Options
Yvonne Montejano
Reply | Threaded
Open this post in threaded view
♦
♦
|

Error when merging multiple Excel spreadsheets into 1 SPSS dataset

Yvonne Montejano
Hello list,

From this listserv, I obtained a python script for merging multiple
Excel spreadsheets into one dataset.  Another discussion thread on the
same subject advised to download the xlrd & xlwt modules, which I did.
I am currently using SPSS v18 and the Python extension (2.6.2) that
comes with V18.

The script runs great for the most part, except it doesn't save my
output to the correct file destination.  In fact, it doesn't save it at
all.  Any help is greatly appreciated.  Following is the script and the
error message I get in the output.

C. Yvonne Montejano
Research Specialist
Texas Juvenile Probation Commission
tel (512) 424-6695
fax (512) 424-6717



OUTPUT CLOSE all.
DATASET CLOSE all.

/*Supervisions*/.
BEGIN PROGRAM.
import spss, spssaux, xlrd

wb = xlrd.open_workbook('R:/Grant Reporting
fy2010/3Q2010/3_ImportedToAccess/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/3Q2010/3_ImportedToAccess/Supervisions_SAP.xls"
 /SHEET=name '%s'.

ALTER TYPE HQCountyNumber (N3.0).
ALTER TYPE HQCountyNumber (A3).
ALTER TYPE PIDNumber (N7.0).
ALTER TYPE PIDNumber (A7).
ALTER TYPE ReferralNumber (N7.0).
ALTER TYPE ReferralNumber (A7).
ALTER TYPE SupervisionBeginDate SupervisionEndDate (ADATE10).
ALTER TYPE SupervisionType (A4).
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],))

spss.Submit("save outfile = 'R:\Grant Reporting
fy2010\3Q2010\3_ImportedToAccess\Supervisions_SAP.sav'.")
END PROGRAM.


Here is the error:
Traceback (most recent call last):
  File "<string>", line 37, in <module>
  File "C:\Python26\lib\site-packages\spss180\spss\spss.py", line 1283,
in Submit
    raise SpssError,error
spss.errMsg.SpssError: [errLevel 3] Serious error.

=====================
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
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Error when merging multiple Excel spreadsheets into 1 SPSS dataset

Jon K Peck

One problem, at least, is that you reference an output location like this:
'R:\Grant Reporting fy2010\3Q2010\3_ImportedToAccess\Supervisions_SAP.sav'
Certain characters in this are interpreted as escape sequences standing for other characters.  \3, in particular, stands for the character with code 3.  That happens to be an ascii character known as ETX.

What you intended was a directory separator followed by the character 3.

To avoid this, you can use forward slashes or you can precede the whole string by "r" as in
r"save outfile = 'R:\Grant Reporting
fy2010\3Q2010\3_ImportedToAccess\Supervisions_SAP.sav'."
which means not to interpret character sequences as escape sequences.

Note that if the line breaks are as they appear in the email, you also have a line wrapping problem.  If you use """ instead of ", you can continue a literal onto separate lines, but you still have to respect the SPSS literal wrapping rules.

To sum it up, try this form:
spss.Submit(r"""save outfile =
'R:\Grant Reportingfy2010\3Q2010\3_ImportedToAccess\Supervisions_SAP.sav'.""")

where the sequence inside single quotes is all on one line.

HTH,

Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435


From: Yvonne Montejano <[hidden email]>
To: [hidden email]
Date: 07/21/2010 11:34 AM
Subject: [SPSSX-L] Error when merging multiple Excel spreadsheets into 1              SPSS dataset
Sent by: "SPSSX(r) Discussion" <[hidden email]>




Hello list,

From this listserv, I obtained a python script for merging multiple
Excel spreadsheets into one dataset.  Another discussion thread on the
same subject advised to download the xlrd & xlwt modules, which I did.
I am currently using SPSS v18 and the Python extension (2.6.2) that
comes with V18.

The script runs great for the most part, except it doesn't save my
output to the correct file destination.  In fact, it doesn't save it at
all.  Any help is greatly appreciated.  Following is the script and the
error message I get in the output.

C. Yvonne Montejano
Research Specialist
Texas Juvenile Probation Commission
tel (512) 424-6695
fax (512) 424-6717



OUTPUT CLOSE all.
DATASET CLOSE all.

/*Supervisions*/.
BEGIN PROGRAM.
import spss, spssaux, xlrd

wb = xlrd.open_workbook('R:/Grant Reporting
fy2010/3Q2010/3_ImportedToAccess/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/3Q2010/3_ImportedToAccess/Supervisions_SAP.xls"
/SHEET=name '%s'.

ALTER TYPE HQCountyNumber (N3.0).
ALTER TYPE HQCountyNumber (A3).
ALTER TYPE PIDNumber (N7.0).
ALTER TYPE PIDNumber (A7).
ALTER TYPE ReferralNumber (N7.0).
ALTER TYPE ReferralNumber (A7).
ALTER TYPE SupervisionBeginDate SupervisionEndDate (ADATE10).
ALTER TYPE SupervisionType (A4).
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],))

spss.Submit("save outfile = 'R:\Grant Reporting
fy2010\3Q2010\3_ImportedToAccess\Supervisions_SAP.sav'.")
END PROGRAM.


Here is the error:
Traceback (most recent call last):
 File "<string>", line 37, in <module>
 File "C:\Python26\lib\site-packages\spss180\spss\spss.py", line 1283,
in Submit
   raise SpssError,error
spss.errMsg.SpssError: [errLevel 3] Serious error.

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

Free forum by Nabble Edit this page