Original Message:
Sent: 8/29/2023 10:11:00 AM
From: Sébastien Muller
Subject: RE: SPSS v29 OMS to xlsx is empty
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.
------------------------------
Sébastien Muller
------------------------------
Original Message:
Sent: Tue August 29, 2023 10:00 AM
From: Sébastien Muller
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Tue August 29, 2023 09:20 AM
From: Jon Peck
Subject: SPSS v29 OMS to xlsx is empty
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.
--
Original Message:
Sent: 8/29/2023 8:47:00 AM
From: Sébastien Muller
Subject: RE: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Mon August 28, 2023 12:48 PM
From: Jon Peck
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Mon August 28, 2023 11:39 AM
From: Curtis Browning
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Mon August 28, 2023 10:47 AM
From: Sébastien Muller
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Mon August 28, 2023 09:26 AM
From: Curtis Browning
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Fri August 25, 2023 03:42 AM
From: Sébastien Muller
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Wed August 23, 2023 09:49 AM
From: Jon Peck
Subject: SPSS v29 OMS to xlsx is empty
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
Original Message:
Sent: Tue August 22, 2023 11:03 AM
From: Sébastien Muller
Subject: SPSS v29 OMS to xlsx is empty
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
------------------------------