Reading Excel Tabs From Spreadsheet

[Jason Schoeneberger]

Hi All,

 

I searched the archives and couldn’t find anything related to my problem, but I apologize if this has been covered.

 

I have an Excel spreadsheet provided by a vendor that has a separate tab for each school’s data.  There are about 30 tabs in the spreadsheet.  I have a macro created that will read each tab and combine every school’s data into one comprehensive SPSS file.  However, to do this I have to create the list of Excel tabs manually to feed into my macro.

 

Is there a way for SPSS to create a listing of the Excel tabs in an automated form?

 

Thanks,

 

Jason Schoeneberger

[Albert-Jan Roskam]

Hi Jason!

You don't need VBA if you use the Python script below. You do need to download the xlrd and xlwt modules(*). Mail me if you want the .py file with proper indentation. The script first creates a single-sheet merged xls file, then imports it into spss. It creates an additional column with the sheet=school name.

Btw, I don't know the row number limits of Excel. It used to be around 65000, I think, but it might have increased in newer versions. If you have that many rows (in total) the program might fail.

Question: it would be neater to read the xls and then build a sav file from scratch using cursors. Is that possible? Or would it require that all variable types be defined a priori?

Cheers!!
Albert-Jan

(*) see:
http://pypi.python.org/pypi/xlrd
http://pypi.python.org/pypi/xlwt

BEGIN PROGRAM.

from xlrd import *
from xlwt import *
import spss

inbook  = open_workbook("d:/temp/schools.xls")
isheets = inbook.nsheets

outbook = Workbook()
osheets = outbook.add_sheet("merged", cell_overwrite_ok=True)
out_file= "d:/temp/mergedschools.xls"
rowcnt  = -1
for shx in range(isheets):
        sh = inbook.sheet_by_index(shx)
        for rx in range(sh.nrows):
            rowcnt += 1
            for cx in range(sh.ncols):
                if sh.row_types(rx)[cx] and sh.row_values(rx)[cx]:
                    if shx == 0:
                        osheets.write(rx, cx, sh.cell_value(rx, cx))
                        osheets.write(rx, sh.ncols, inbook.sheet_names()[shx])
                    else:
                        osheets.write(rowcnt, cx, sh.cell_value(rx, cx))
                        osheets.write(rowcnt, sh.ncols, inbook.sheet_names()[shx])
outbook.save(out_file)

spss.Submit("""get data /type=xls /file= '%s' /sheet=name 'merged' """ % out_file + \
            """/cellrange=full /readnames=off /assumedstrwidth=32767.""" )
spss.Submit("save outfile = '%s'." % (out_file[:-4] + ".sav"))


END PROGRAM.






--- On Thu, 3/5/09, Jason Schoeneberger <[hidden email]> wrote:

> From: Jason Schoeneberger <[hidden email]>
> Subject: Reading Excel Tabs From Spreadsheet
> To: [hidden email]
> Date: Thursday, March 5, 2009, 2:54 PM
> Hi All,
>
>
>
> I searched the archives and couldn't find anything
> related to my problem,
> but I apologize if this has been covered.
>
>
>
> I have an Excel spreadsheet provided by a vendor that has a
> separate tab for
> each school's data.  There are about 30 tabs in the
> spreadsheet.  I have a
> macro created that will read each tab and combine every
> school's data into
> one comprehensive SPSS file.  However, to do this I have to
> create the list
> of Excel tabs manually to feed into my macro.
>
>
>
> Is there a way for SPSS to create a listing of the Excel
> tabs in an
> automated form?
>
>
>
> Thanks,
>
>
>
> Jason Schoeneberger

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

[Trejtowicz, Mariusz]

 

Hi Jason,

 

You can do it very elegantly using Python, but you need (except SPSS' Python plugin) xlrd extension for Python (you can dowload it from: http://pypi.python.org/pypi/xlrd/0.5.2 ).

 

An exemplary code for accessing (and printing out to output) all sheets' names:

 

 

begin program python .
import xlrd
workbook = xlrd.open_workbook("D:/Temp/example.xls")
shnames = workbook.sheet_names()
for sh in shnames:
 print sh.encode("ascii")
end program .

 

Best regards,

Mariusz Trejtowicz

 

 

 

 

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jason Schoeneberger
Sent: Thursday, March 05, 2009 2:54 PM
To: [hidden email]
Subject: Reading Excel Tabs From Spreadsheet

Hi All,

 

I searched the archives and couldn’t find anything related to my problem, but I apologize if this has been covered.

 

I have an Excel spreadsheet provided by a vendor that has a separate tab for each school’s data.  There are about 30 tabs in the spreadsheet.  I have a macro created that will read each tab and combine every school’s data into one comprehensive SPSS file.  However, to do this I have to create the list of Excel tabs manually to feed into my macro.

 

Is there a way for SPSS to create a listing of the Excel tabs in an automated form?

 

Thanks,

 

Jason Schoeneberger

[alexwin]

Hi Jason,

the easiest way to do that is to use SPSS script. Select File > New > Script from the main menu and paste the following:

Sub Main
  Set App = CreateObject("Excel.Application")
  Set W = App.Workbooks.Open("c:\temp\example.xls")
  For Each S In W.Sheets
    Debug.Print S.Name
  Next
End Sub

Edit Excel file name, click the last line on the left margin to set a breakpoint (this will allow to see debug.print text) and press F5 to run

> I have an Excel spreadsheet provided by a vendor that has a separate tab for each school's data.  There are about 30 tabs in the spreadsheet.  I have a macro created that will read each tab and combine every school's data into one comprehensive SPSS file.  However, to do this I have to create the list of Excel tabs manually to feed into my macro. Is there a way for SPSS to create a listing of the Excel tabs in an automated form?

Thanks,

> Jason Schoeneberger

HTH,

Dr. Alexander Vinogradov, Associate Professor
Sociology and Psychology Faculty
National Taras Shevchenko University
Ukraine



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