Thank you for all your help and patience. I now have working syntax for pulling specific tables from SPSS into Excel. I did do a little band-aid....the initial output would leave out the first table, whether or not I used action='Create Worksheet' or action='ModifyWorksheet'. To get around this I realized if I write an initial table (any table), I could then just use action='Modifyworksheet' at the same startingCell='A3' to write in the table I actually want, with the remaining tables falling into place without issue (except for formatting peculiarities). This is the code used:
Basically, I write 6 tables to get the five I need. I don't care about the weird formatting; my Excel SPSS processor just grabs the data it needs into new properly formatted tables. If you have any thoughts on the missing first tables, I'd be interested to streamline the syntax, but it appears to be working as is now.
Again, thank you for all your help to get through this. I anticipate much time savings on the back end!
Original Message:
Sent: Fri June 21, 2024 09:58 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
Don't specify a starting cell if you use AddRows. It should automatically append after the last existing row.
--
Original Message:
Sent: 6/21/2024 8:28:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
I just tried to put 5 mixed tables on a sheet and 3 of them made it. Two did not. The startingCell control doesn't seem to work beyond the first table. Also, the formatting of cells is a bit funky....earlier attempts had black fill in the cells. I've attached an image of what Excel looks like, and the syntax that got there:
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Model Dimension'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport (file='d:/spss/extest1.xls', sheet='mixedresults', action='CreateWorksheet', location='OverwriteAtCellRef', startingCell='A3')".
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Information Criteria'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport (file='d:/spss/extest1.xls', sheet='mixedresults', action='ModifyWorksheet', location='AddRows', startingCell='A15')".
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Intraclass Correlation Coefficients'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport (file='d:/spss/extest1.xls', sheet='mixedresults', action='ModifyWorksheet', location='AddRows', startingCell='A25')".
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="Estimates of Fixed Effects'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport (file='d:/spss/extest1.xls', sheet='mixedresults', action='ModifyWorksheet', location='AddRows', startingCell='A35')".
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="Estimates of Covariance Parameters'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport (file='d:/spss/extest1.xls', sheet='mixedresults', action='ModifyWorksheet', location='AddRows', startingCell='A45')".
Anything that stands out here that could improve the Excel Results? I will keep testing different variations.
------------------------------
Mark Sloan
Original Message:
Sent: Fri June 21, 2024 07:48 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
That's exactly backwards. location='OverwriteAtCellRef', startingCell='A50' would overwrite every table if using the same file. What I suggested was
location = "AddRows" in the same file.
That should append each table to the bottom of the same sheet.
--
Original Message:
Sent: 6/21/2024 7:40:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
Reading through again, I think you mean the following, attempting to adapt to my situation:
/CUSTOM FUNCTION="cutomoutputfunctions.excelexport (file='d:/spss/extest.xls, sheet='mixedmodels', action='ModifyWorksheet', location='OverwriteAtCellRef', startingCell='A50')" <---no image format wanted
Is this the right idea? I just keep using the same filename to add on more and more tables?
------------------------------
Mark Sloan
Original Message:
Sent: Fri June 21, 2024 07:25 PM
From: Mark Sloan
Subject: Controlling What gets Exported to Excel
I am so sorry, but this has me more confused. Is there a good tutorial resource on how to build up an Excel sheet with multiple tables on it using SPSSINC MODIFY OUTPUT? I've spend a lot of time on the web today trying to figure this out, and even reviewed a couple of help pdfs from SPSS, but nothing seems to address this adequately.
------------------------------
Mark Sloan
Original Message:
Sent: Fri June 21, 2024 07:17 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
The full set of options for that function is this. These work the same as the OUTPUT EXPORT options you see on the Export menu
parameters:
file - filespec for output (required)
sheet - sheetname. Default is "Sheet"
action - "CreateWorkbook" | "Create Worksheet" | "ModifyWorksheet"
location - "OverwriteAtCellRef" | "AddColumns" | "AddRows"
startingCell - starting cell if location is overwrite. Default is "A1"
image - image format - "jpg" | "png" | "tiff" | "eps" | "emf" | "bmp"
--
Original Message:
Sent: 6/21/2024 7:09:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
Thank you for the quick reply. Floundering without this help. I don't understand your last sentence. What I want is to basically replicate most of the tables produced by the MIXED command (not all), and do this 6 times above each other on ONE Excel worksheet. Basically this without writing over each other:
MODEL 1
'Model Dimension'
'Information Criteria'
'Intraclass Correlation Coefficients'
'Parameter Estimates'
'Covariance Parameter Estimates'
MODEL 2
(same as above)
MODEL 3 etc.
------------------------------
Mark Sloan
Original Message:
Sent: Fri June 21, 2024 06:47 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
From the function help ...
Example
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Custom Table'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
sheet='table#',action='CreateWorksheet')".
Use # in the file or sheet string to insert a sequential number on each
save operation to avoid overwriting.
--
Original Message:
Sent: 6/21/2024 6:12:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
I think PRECEDING was the problem. That said, I am trying to write multiple tables (Mixed model tables), but they are overwriting each other. I'm working to resolve, but if you catch this any help is appreciated.
------------------------------
Mark Sloan
Original Message:
Sent: Fri June 21, 2024 02:44 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
I'm tied up today, but MODIFY OUTPUT has been around for years and seems to be pretty reliable. Remember that by default it looks only at the output from the immediately preceding procedure unless you specify ALL.
I would need more information to figure this out. If you want to send details, use my gmail address (
jkpeck@gmail.com)
--
Original Message:
Sent: 6/21/2024 1:15:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
After much testing, I am beginning to believe MODIFY OUTPUT only partially works, and it may be buggy. I was able to consistently get "Descriptive Statistics" to save into an Excel file for a while, but now my working syntax no longer works. I tried other object types/subtypes such as "Notes" (worked), and "Model Dimensions" from MIXED models (doesn't work). After repeating syntax a few times, nothing works, not even the previously successful "Descriptive Statistics".
I tried creating the syntax from the menu version, and while had an initial success, it also failed to save after a few tries.
I think this is the answer to what I need but I can't get it work consistently or properly. I tried some of the other options, and was also unsuccessful. If there are other options to send to Excel only certain tables and titles, it is greatly appreciated!
------------------------------
Mark Sloan
Original Message:
Sent: Thu June 20, 2024 10:56 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
It's SPSSINC MODIFY OUTPUT. If you search for MODIFY OUTPUT on the Extension Hub, you will find it. If you already got it installed, you won't see it on the EH list unless you check Installed at the bottom. Check all three boxes.
Once it is installed, it will appear as Utilities > Modify Output Titles.
You would specify the OMS type for the table you want. You don't need to also specify the command name in this case.
SPSS allows literal strings to be either single or double quoted. The CUSTOM subcommand used both types, because it needs quotes within quotes. (There are other ways to do this, too.)
You can use this to get multiple tables if needed, but let's start with the simple case of just looking at the preceding command output.
SHOW ALL will not display information about extensions. If you need to manage them, you can install STATS EXTENSION REPORT, which will then show up on the Extensions menu. It will give you a comprehensive list of installed extensions, and it can show what extensions are not installed or where an update is available.
--
Original Message:
Sent: 6/20/2024 10:44:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
I was unfortunately unable to get anything to work tonight. First, I used the Extensions > Extension Hub... to download all extensions related to the word search: "SPSSINC". I have many extensions now loaded to get the right one, but not sure which actually contains "SPSSINC MODIFY INPUT" or "customoutputfunctions.excelexport".
I also opened a new classic Syntax Editor, and ran this syntax:
show all.
I got the System Settings, License components, Versions, and System Variables, but unfortunately nothing that indicated if "SPSSINC MODIFY OUTPUT" or "customoutputfunctions.excelexport" were loaded.
When I ran the example Python extension, it would not save into the various folders I tried to save to. The Output editor did not indicate there were any errors.
I think this is what I want, if I can just get it to work:
SPSSINC MODIFY OUTPUT MODELS TITLES
/IF SUBTYPE="'Mixed'" <----or /IF COMMAND="'Mixed'" ?
PROCESS=PRECEDING
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='d:/SPSS-RESULTS-REPOSITORY/mytable.xlsx')".
and to add in a subcommand for TITLES that contain the word "EXECUTE".
Should Mixed have both single and double quotes together? Mixed is the OMS Command Identifier associated with all the tables of interest. Any further ideas?
BTW, I am running SPSS 29.
------------------------------
Mark Sloan
Original Message:
Sent: Thu June 20, 2024 03:20 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
Extension commands such as MODIFY OUTPUT are written in Python or R, but the user only sees their syntax, which looks just like regular built-in SPSS commands. No Python or R knowledge is required to use them except for some that allow custom Python functions to be plugged in.
Here is an example.
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Descriptive Statistics'"
PROCESS=PRECEDING
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/mytable.xlsx')".
Other parameters for the function and examples can be found in the customoutputfunctions.py module installed with this extension command.
--
Original Message:
Sent: 6/20/2024 2:38:00 PM
From: Mark Sloan
Subject: RE: Controlling What gets Exported to Excel
It appears SPSSINC MODIFY INPUT is a Python code? Would you have/know of any sample routines that might allow control over items to export to Excel? The last sample routines were extremely helpful and I could not have proceeded without them. Thank you!
------------------------------
Mark Sloan
Original Message:
Sent: Thu June 20, 2024 01:46 PM
From: Jon Peck
Subject: Controlling What gets Exported to Excel
I never use WORKBOOK model, but WORKBOOK EXPORT appears to support the VISIBLE mode for selecting the output to export. So if you go to Edit > Options and mark the output types you want to have not visible by default, that would suppress the things you don't want, I suppose.
If that doesn't work, just using the regular mode would give you control over the items exported, including based on visibility. If you use the SPSSINC MODIFY OUTPUT extension command in that mode, you can select the table types you want exported based on their OMS types using the customoutput.excelexport. function installed with that command.
--
Original Message:
Sent: 6/20/2024 1:15:00 PM
From: Mark Sloan
Subject: Controlling What gets Exported to Excel
I have successfully developed a consolidated MIXED syntax that processes a series of models, creates graphs/plots to assess assumptions, and presents model output before and after removal of extreme outliers. My next need is to export model-only output to EXCEL (3 before and 3 after MIXED models) with no extraneous tables or graphs. To this end I am trying to use the WORKBOOK EXPORT function to control what gets exported to Excel:
* Export Workbook.
WORKBOOK EXPORT
/CONTENTS EXPORT=VISIBLE LAYERS=VISIBLE MODELVIEWS=VISIBLE
/XLSM DOCUMENTFILE='D:\SPSS-RESULTS\test-output-delete-later.xlsm'
OPERATION=CREATESHEET
LOCATION=LASTCOLUMN NOTESCAPTIONS=YES.
Here is what I want to suppress: entire syntax up front (hide this), Notes, Model Descriptions, Graphs/Plots. The above syntax successfully suppresses Notes and Model Descriptions, but not syntax (too long) and the graphs/plots. Are their additional subcommands that do this, or is OMS the only way? 'Utilities > OMS Control Panel' appears to only produce XML output, and I need .xlsm-based output.
An alternative is to manually-select each of the tables from each model: right-click "Export..." and select "Objects to Export > Selected" with Type > Excel...., and filename. This roughly does what I am trying to do, but I need to automate this in syntax.
Thanks!
------------------------------
Mark Sloan
------------------------------