Script Or Python

[Riya]

Hey SPSS Listers,

I want to know the scope of Script and Python. Which tool has more scope : Script/Python?
I got to know Script was adapted from Visual Basic. Which tool has more integration with Excel and PPT?
Is this possible to call excel macro within SPSS syntax window?

Which tool can perform the following functions efficiently?
- Formatting in excel (Eg, Bordering, Numbering etc)
- Running an excel macro within SPSS session
- Insering excel functions

Let me tell you my background :
I am very good with Excel and VBA. The repetitive task i perform daily is running statistical analysis and save the output or data file to excel. And then run excel macro to make the report nice formatted.

Why save spss data or output file to excel?
Main Deliverables to clients : Excel Tabular, Powerpoint presentation

[ChetMeinzer]

I have a repo of how to take spss files and format them in Excel using python here
https://github.com/chetstar/spss.git    (please feel free to help me with this code!)

the lib xlrd xlwt explain more options.

I don't want to ever open microsoft again.  Python learning helps more in the long run than Macro's, but the proprietary licens keeps you from actually editing xl outside of xl.  

for example, here is my code to recreate data and pass to xls as csv


import os, errno
import spss
import csv
import xlwt
import time
from datetime import date
today = str(date.today())


def xls(out, sheet=None):

    spss.Submit(r"""
    SAVE TRANSLATE OUTFILE= '%s.csv'
    /TYPE=CSV /ENCODING='Locale'
    /MAP /REPLACE /FIELDNAMES
    /CELLS=VALUES.
    """ % (out+" "+today))
   
    DATA=list()
    with open(out+" "+today+'.csv') as f:
        reader=csv.reader(f)
        headings = f.next().split(",")
        for row in reader:
            DATA.append(row)
    print headings
    wb = xlwt.Workbook()
    ws = wb.add_sheet(sheet)

# Add headings with styling and froszen first row
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')

    rowx = 0
    ws.set_panes_frozen(True) # frozen headings instead of split panes
    ws.set_horz_split_pos(rowx+1) # in general, freeze after last heading row
    ws.set_remove_splits(True) # if user does unfreeze, don't leave a split there
    for colx, value in enumerate(headings):
        ws.write(rowx, colx, value, heading_xf)

    for i, row in enumerate(DATA):
        for j, col in enumerate(row):
            ws.write(i+1, j, col)
#ws.col(1).width = 256 * max([len(row[0]) for row in DATA])
    wb.save(out+" "+today+'.xls')
 
    os.remove(out+" "+today+".csv")

    # try:
        # os.remove(filespec)
    # except OSError, e: # this would be "except OSError as e:" in python 3.x
        # if e.errno != errno.ENOENT: # errno.ENOENT = no such file or directory
            # raise # re-raise exception if a different error occured

       
# def CSV(filespec=None):
    # import spss
    # spss.Submit(r"""
    # SAVE TRANSLATE OUTFILE= '%s'
    # /TYPE=CSV /ENCODING='Locale'
    # /MAP /REPLACE /FIELDNAMES
    # /CELLS=VALUES.
    # """ % (filespec))

[David Marso]

"but the proprietary license keeps you from actually editing xl outside of xl.  "

???  Not sure what this means.  Please clarify.  
If I were to hack the file format and build my own Super Excel Editor you are implying that M$ can send men in black suits and sunglasses to my door to collect booty?  GOOD LUCK MR. BILL!

ChetMeinzer wrote

I have a repo of how to take spss files and format them in Excel using python here
https://github.com/chetstar/spss.git    (please feel free to help me with this code!)

the lib xlrd xlwt explain more options.

I don't want to ever open microsoft again.  Python learning helps more in the long run than Macro's, but the proprietary licens keeps you from actually editing xl outside of xl.  

for example, here is my code to recreate data and pass to xls as csv


import os, errno
import spss
import csv
import xlwt
import time
from datetime import date
today = str(date.today())


def xls(out, sheet=None):

    spss.Submit(r"""
    SAVE TRANSLATE OUTFILE= '%s.csv'
    /TYPE=CSV /ENCODING='Locale'
    /MAP /REPLACE /FIELDNAMES
    /CELLS=VALUES.
    """ % (out+" "+today))
   
    DATA=list()
    with open(out+" "+today+'.csv') as f:
        reader=csv.reader(f)
        headings = f.next().split(",")
        for row in reader:
            DATA.append(row)
    print headings
    wb = xlwt.Workbook()
    ws = wb.add_sheet(sheet)

# Add headings with styling and froszen first row
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')

    rowx = 0
    ws.set_panes_frozen(True) # frozen headings instead of split panes
    ws.set_horz_split_pos(rowx+1) # in general, freeze after last heading row
    ws.set_remove_splits(True) # if user does unfreeze, don't leave a split there
    for colx, value in enumerate(headings):
        ws.write(rowx, colx, value, heading_xf)

    for i, row in enumerate(DATA):
        for j, col in enumerate(row):
            ws.write(i+1, j, col)
#ws.col(1).width = 256 * max([len(row[0]) for row in DATA])
    wb.save(out+" "+today+'.xls')
 
    os.remove(out+" "+today+".csv")

    # try:
        # os.remove(filespec)
    # except OSError, e: # this would be "except OSError as e:" in python 3.x
        # if e.errno != errno.ENOENT: # errno.ENOENT = no such file or directory
            # raise # re-raise exception if a different error occured

       
# def CSV(filespec=None):
    # import spss
    # spss.Submit(r"""
    # SAVE TRANSLATE OUTFILE= '%s'
    # /TYPE=CSV /ENCODING='Locale'
    # /MAP /REPLACE /FIELDNAMES
    # /CELLS=VALUES.
    # """ % (filespec))

[Jon K Peck]

Python programmability and scripting can do a lot more than Basic in Statistics, but Python is more difficult to integrate with MS Office functionality.  You need a third party library such as xlrd to manipulate Excel objects from Python, and it may not support all Excel versions, while using the Basic included with Statistics, you can use Office automation from Statistics as well as manipulating Statistics objects.  So if all you need to do is automate various functions with Excel and you already know VBA, there isn't a compelling reason to switch to Python even though Python is a much better language overall.

However, another approach would be to do the formatting on the SPSS Viewer objects before exporting to Excel or Ppt.  Python scripting code or the Python based SPSSINC MODIFY TABLES and SPSSINC MODIFY OUTPUT extension commands can often do the desired formatting and manipulation without the need to write elaborate Basic or Python scripts for this.

You can find a link to all the Python programmability and scripting documentation on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral) in the Important Bookmarks section if you want to read about this before installing the Python Essentials.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        Riya <[hidden email]>
To:        [hidden email],
Date:        03/22/2013 10:12 AM
Subject:        [SPSSX-L] Script Or Python
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hey SPSS Listers,

I want to know the scope of Script and Python. Which tool has more scope :
Script/Python?
I got to know Script was adapted from Visual Basic. Which tool has more
integration with Excel and PPT?
Is this possible to call excel macro within SPSS syntax window?

Which tool can perform the following functions efficiently?
- Formatting in excel (Eg, Bordering, Numbering etc)
- Running an excel macro within SPSS session
- Insering excel functions

Let me tell you my background :
I am very good with Excel and VBA. The repetitive task i perform daily is
running statistical analysis and save the output or data file to excel. And
then run excel macro to make the report nice formatted.

Why save spss data or output file to excel?
Main Deliverables to clients : Excel Tabular, Powerpoint presentation




--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Script-Or-Python-tp5718973.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[David Marso]

NOTE:  
You can also drive 'Statistics/SPSS' from Excel.
I used to do it for a living .
I don't have time to research the changes to make it work from 21 at the moment but the meat
From Excel, add a reference to the appropriate Statistics type libs
is Set someObject=CreateObject ("I don't know the name of the current string to put here","")

---

Jon K Peck wrote

Python programmability and scripting can do a lot more than Basic in
Statistics, but Python is more difficult to integrate with MS Office
functionality.  You need a third party library such as xlrd to manipulate
Excel objects from Python, and it may not support all Excel versions,
while using the Basic included with Statistics, you can use Office
automation from Statistics as well as manipulating Statistics objects.  So
if all you need to do is automate various functions with Excel and you
already know VBA, there isn't a compelling reason to switch to Python even
though Python is a much better language overall.

However, another approach would be to do the formatting on the SPSS Viewer
objects before exporting to Excel or Ppt.  Python scripting code or the
Python based SPSSINC MODIFY TABLES and SPSSINC MODIFY OUTPUT extension
commands can often do the desired formatting and manipulation without the
need to write elaborate Basic or Python scripts for this.

You can find a link to all the Python programmability and scripting
documentation on the SPSS Community website (
www.ibm.com/developerworks/spssdevcentral) in the Important Bookmarks
section if you want to read about this before installing the Python
Essentials.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:   Riya <[hidden email]>
To:     [hidden email],
Date:   03/22/2013 10:12 AM
Subject:        [SPSSX-L] Script Or Python
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



Hey SPSS Listers,

I want to know the scope of Script and Python. Which tool has more scope :
Script/Python?
I got to know Script was adapted from Visual Basic. Which tool has more
integration with Excel and PPT?
Is this possible to call excel macro within SPSS syntax window?

Which tool can perform the following functions efficiently?
- Formatting in excel (Eg, Bordering, Numbering etc)
- Running an excel macro within SPSS session
- Insering excel functions

Let me tell you my background :
I am very good with Excel and VBA. The repetitive task i perform daily is
running statistical analysis and save the output or data file to excel.
And
then run excel macro to make the report nice formatted.

Why save spss data or output file to excel?
Main Deliverables to clients : Excel Tabular, Powerpoint presentation




--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Script-Or-Python-tp5718973.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

Subject: Re: [SPSSX-L] Script Or Python

>
>
>I have a repo of how to take spss files and format them in Excel using python
>here
>https://github.com/chetstar/spss.git    (please feel free to help me with
>this code!)
>
>Hello, why are you not using SAVE TRANSLATE ... /TYPE=xls (or xlsx)? Did you consider what happens if the data are big in terms of (a) number of cases (no longer fits in RAM) (b) number of variables (> 255 vars)?
>
>If the purpose is to add lot of formatting: How about this cute program? ;-)
>
>
>from random import choice, randint
>import xlwt
>import spss, spssaux
>
>def makeStyles():
>

 colours = ['aqua', 'black', 'blue', 'blue_gray', 'bright_green',
'brown', 'coral', 'cyan_ega', 'dark_blue', 'dark_blue_ega',
'dark_green',
>      'dark_green_ega', 'dark_purple', 'dark_red',
'dark_red_ega', 'dark_teal', 'dark_yellow', 'gold', 'gray_ega',
'gray25', 'gray40',
>      'gray50', 'gray80', 'green', 'ice_blue',
'indigo', 'ivory', 'lavender', 'light_blue', 'light_green',
'light_orange', 'light_turquoise',
>      'light_yellow', 'lime',
'magenta_ega', 'ocean_blue', 'olive_ega', 'olive_green', 'orange',
'pale_blue', 'periwinkle', 'pink', 'plum',
>      'purple_ega', 'red', 'rose', 'sea_green', 'silver_ega',
 'sky_blue', 'tan', 'teal', 'teal_ega', 'turquoise', 'violet', 'white', 'yellow']
>
 patterns = ['no_fill', 'none', 'solid', 'solid_fill', 'solid_pattern',
'fine_dots', 'alt_bars', 'sparse_dots', 'thick_horz_bands',
>
'thick_vert_bands', 'thick_backward_diag', 'thick_forward_diag',
'big_spots', 'bricks', 'thin_horz_bands', 'thin_vert_bands',
>      'thin_backward_diag', 'thin_forward_diag', 'squares', 'diamonds']
>    borders = ['no_line', 'thin', 'medium', 'dashed', 'dotted', 'thick', 'double', 'hair', 'medium_dashed', 'thin_dash_dotted',
>      'medium_dash_dotted', 'thin_dash_dot_dotted', 'medium_dash_dot_dotted', 'slanted_medium_dash_dotted']
>    styles = []
>    for i in range(len(colours) * len(patterns) * len(borders)):
>
 colour1, colour2, colour3, pattern, border = choice(colours),
choice(colours), choice(colours), choice(patterns), choice(borders)
>
 easxfCmd = 'font: name Arial, color-index %(colour1)s; borders: left
%(border)s, right %(border)s, top %(border)s, bottom %(border)s;
pattern: pattern %(pattern)s, fore_colour %(colour3)s;'

>      styles.append(xlwt.easyxf(easxfCmd % locals()))
>    return styles
>
>def managerize(savFile):
>    spssaux.OpenDataFile(savFile)
>    allStyles = makeStyles()
>    wb = xlwt.Workbook()
>    ws = wb.add_sheet('managerized')
>    MAXSTYLES = 4094
>    styles = [choice(allStyles) for i in range(MAXSTYLES)]
>    vnames = spssaux.GetVariableNamesList()
>    for colno, vname in enumerate(vnames):
>        ws.write(0, colno, vname, choice(styles))
>        dataCursor = spss.Cursor()
>        for rowno in

 range(spss.GetCaseCount()):

>            value = dataCursor.fetchone()
>            ws.write(rowno+1, colno, value[0], choice(styles))
>        dataCursor.close()
>    xlsFile = savFile[:-4] + ".xls"
>    wb.save(xlsFile)
>    print "Done! Managerized file: '%s'" % xlsFile
>
>if __name__ == "__main__":
>    savFile = "c:/program files/spss/employee data.sav"
>    managerize(savFile)
>

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