SPSS Statistics

 View Only
Expand all | Collapse all

SPSS v29 OMS to xlsx is empty

  • 1.  SPSS v29 OMS to xlsx is empty

    Posted Wed August 23, 2023 09:42 AM

    Hi,

    I'm calling an SPSS syntax from Python 3.10 on Max OSX Ventura 13.5. I have successfully written the results to an Excel file for a few years but following upgrades, this seemed to hang without producing an error message. After careful isolation, I am able to write a correct output to text, but the exact same syntax, just changing the output format to xlsx, produces an empty file (but does produce it so the writing permissions seem good). The computation hangs for much longer than the actual complexity suggests.

    Here is my code, where SEX is a variable for the gender of survey respondents. I would be very thankful for any suggestion.

    spss.Submit('''
    OMS
    /SELECT TABLES
    /DESTINATION FORMAT = XLSX
    OUTFILE = '/Users/my_path/my_results.xlsx'.
    FREQUENCIES SEX.
    OMSEND.
    ''')


    ------------------------------
    Sébastien Muller
    ------------------------------


  • 2.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Wed August 23, 2023 09:49 AM

    That code runs find for me, but I am on Windows.  I have 29.0.1 installed.  IIRC, there were some issues in 29.0.0 on Mac with Excel, so I suggest installing the 29.0.1 fixpack to see if that resolves the issue.



    ------------------------------
    Jon Peck
    ------------------------------



  • 3.  RE: SPSS v29 OMS to xlsx is empty

    Posted Fri August 25, 2023 03:42 AM
    Edited by Sébastien Muller Fri August 25, 2023 03:43 AM

    Thank you so much, @Jon Peck. I'm sorry I did not specify that I was running v29.0.1 all along. Or is there a fixpack on top of that?

    I thought today of isolating further, writing the same directly in the syntax editor (without the python call). And the problem is the same: I get a correct output file as .txt, but an empty one as .xlsx (but a file nevertheless, so the OMS-command does work to some extent). It also makes it a broader problem, not only for those of us calling a syntax from python.

    Still thankful for any suggestion.



    ------------------------------
    Sébastien Muller
    ------------------------------



  • 4.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Fri August 25, 2023 09:06 AM
     The export code would be the same whether coming from Python or direct syntax.  I'm not a Mac person.  Perhaps someone else could assist further.  Sorry.

    --





  • 5.  RE: SPSS v29 OMS to xlsx is empty

    Posted Mon August 28, 2023 09:27 AM

    Hi Sébastien, thanks for reporting this. It apparently affects all .xlsx exports on the MacOS platform. we have filed defect number #7185 for this issue and have marked it with high priority. 

    In the meantime and until a fix is published I recommend you export to .xls format, which can also be opened by Excel.

    Regards,



    ------------------------------
    Curtis Browning
    SPSS Statistics Architect
    ------------------------------



  • 6.  RE: SPSS v29 OMS to xlsx is empty

    Posted Mon August 28, 2023 10:48 AM

    Thank you, @Curtis Browning for picking that up.

    I got the suggestion to use EXPORT OUTPUT, which works well, only you can't call it for batch processing as it relies on an active Viewer window being open. And this is really what I need to do, by hundreds.

    I tried .xls just now, pasting the syntax directly from the Utilities > OMS control panel, to be certain it would be correct, but no luck either. A file of zero bytes is created, though, with the name and location I specified for my output. What I noticed today for the first time is that the output Viewer hangs showing "Running OMSEND..." in the bottom frame of the Viewer. If that is of any value to you.

    I am still thankful for any follow up. Have a great day on.



    ------------------------------
    Sébastien Muller
    ------------------------------



  • 7.  RE: SPSS v29 OMS to xlsx is empty

    Posted Mon August 28, 2023 11:39 AM
    Edited by Curtis Browning Mon August 28, 2023 11:41 AM

    Hi Sébastien, that is strange that .xls also fails for you. Although I was able to reproduce the problem with .xlsx, I was not able to reproduce with the older .xls format. 

    Can you try a test please: Instead of using Python and OMS can you try to export to a .xls file (not .xlsx) from the output viewer directly? That should work as it uses a wholly different code pathway than the exports to .xlsx. 

    FREQUENCIES SEX.

    Then use the UI to export to the same location as before.

    Best,



    ------------------------------
    Curtis Browning
    SPSS Statistics Architect
    ------------------------------



  • 8.  RE: SPSS v29 OMS to xlsx is empty

    Posted Mon August 28, 2023 12:22 PM

    I was doing it from the syntax window directly, not from python, when I said OMS to xls failed in post number 6.

    Also I successfully produce output to xls and xlsx using either export from the output window, or running the syntax OUTPUT EXPORT after having created whatever statistics in the ouput Viewer.

    I also produce successully an output to .txt using OMS.

    Thank you for your help so far.



    ------------------------------
    Sébastien Muller
    ------------------------------



  • 9.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Mon August 28, 2023 12:48 PM

    Perhaps a hotfix will be issued for the Excel export problem via OMS, but here are a few thoughts on alternatives.

    First, if you are processing and exporting a lot of files and using Python code, you might consider using external mode if you are not already.  It could create an invisible Viewer window where OUTPUT EXPORT would work.  One problem with OUTPUT EXPORT, though, is that via syntax, you don't have much control over what is actually exported from the Viewer.  But, the SPSSINC MODIFY OUTPUT extension command comes with a customoutputfunctions.py file that can help.  It contains a function, excelexport, that selects tables based on OMS table types and exports to Excel files either as separate files or separate tabs.  It actually uses EXPORT OUTPUT to do the exporting, so it allows all the options that OUTPUT EXPORT has but adds the table selection type(s) and other selection capabilities of SPSSINC MODIFY TABLES.



    ------------------------------
    Jon Peck
    ------------------------------



  • 10.  RE: SPSS v29 OMS to xlsx is empty

    Posted Tue August 29, 2023 08:47 AM

    Thank you, @Jon Peck, your reply gives me hope.

    I am not much concerned by the choice of what I export from the Viewer. I tested OUTPUT EXPORT in a syntax window and it produces a perfectly fine xlsx file for my post-processing.

    My concern is with the batch processing and the requirement of "open Viewer document" ("The OUTPUT EXPORT command requires an open Viewer document. Execution of this command stops." is the error message I get). Therefore I am excited about your mentionning "external mode".

    However I can't find much information about how I would access that mode. Can you point me in the right direction please?



    ------------------------------
    Sébastien Muller
    ------------------------------



  • 11.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Tue August 29, 2023 09:21 AM
    There is a section in the Python reference document on external mode.  I use a commercial Python IDE, Wing, for that, which is vastly superior to IDLE.  It requires getting the IDE connected to use the Python installed with Statistics.  With Wing, that's easy, but the way to do that would vary with the IDE used.

    Then you would just call the spss.StartSPSS and Spss.StartClient apis and run code as usual, ending with StopClient calls.

    External mode can be a lot faster than the regular mode due to not having the overhead of the user interface, but the biggest benefit to me is that I can write and debug Python code easily that way.

    --





  • 12.  RE: SPSS v29 OMS to xlsx is empty

    Posted Tue August 29, 2023 10:00 AM
    Edited by Sébastien Muller Tue August 29, 2023 10:02 AM

    Thank you, Jon. I think I follow you. I have been using Pycharm for a few years. That's how I used to spss.Submit a syntax and get the output in xlsx. But the python running was an independent one.

    Now I just made Pycharm use the python from the SPSS installation (Applications/IBS SPSS Statistics/Resources/Pyhton3/bin/python3) to run the code. I run into the same issue of needing an open Viewer document (from OUTPUT EXPORT). It doesn't seem to create an invisible Viewer. Or should I use a special line of syntax to do that?

    Just to make sure, I reverted to OMS with xlsx and it still produces an empty file, while OMS with txt produces a nice and complete file.



    ------------------------------
    Sébastien Muller
    ------------------------------



  • 13.  RE: SPSS v29 OMS to xlsx is empty

    Posted Tue August 29, 2023 10:11 AM
    Here is a log of my python window. I guess the first column are timestamps, I took only the last crosstab. Then some export process seems to start and it just hangs there. (This is when using OMS with xlsx). It does not produce an error message and it does not exit the execution of the python code.
    log of operations


    ------------------------------
    Sébastien Muller
    ------------------------------



  • 14.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Tue August 29, 2023 11:28 AM
    I'll get back to my computer this afternoon and will look into it further.  I expected the StartClient call to attach to a running, visible SPSS session.

    p.s. Pycharm does seem to be well regarded, and I know people use it with SPSS.






  • 15.  RE: SPSS v29 OMS to xlsx is empty

    IBM Champion
    Posted Tue August 29, 2023 02:01 PM
    I guess when I use external mode and need to work with Viewer output, I start the job in the regular syntax window and then import a module with remote debugging set up so I can step through it in Wing.  In pure external mode, I would capture Excel with OMS, but that is what isn't working for you.

    Have you tried Production Mode?  It includes the option of producing the output directly to Excel files.  Since it includes formatting, it probably isn't using the OMS route, so try a production mode job with the output 

    image.png

    --





  • 16.  RE: SPSS v29 OMS to xlsx is empty

    Posted Fri September 01, 2023 02:40 AM

    Thank you again @Jon Peck, and sorry for the delay.

    I wasn't familiar with production mode, but it seems straight forward after a bit of trying. So it works. I am able to produce an output in xlsx format by using the GUI to create and run the job.

    It seems I must open the sav file within the syntax, it wasn't good enough to apply whatever operation to 'dataset1' even if it was open at the moment. It seems production mode opens another instance of spss so the one where my sav file was open was not visible to the new one.

    So the workflow would be (I'm thinking as we speak):

    1. make python write a syntax for each country (change one line of GET FILE + correct path)
    2. make python write a job file calling the syntax above (change the path to output for each country)
    3. emulate a commando line from python calling the job file
    4. repeat

    Does it sound anything like what you're doing? Am I making it too complicated (because I don't understand the workflow so well?)

    Thankful for any feedback. Best regards.



    ------------------------------
    Sébastien Muller
    ------------------------------