Exporting Variable Labels to Excel

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Exporting Variable Labels to Excel

Salbod
I want to copy 200 variables labels from an SPSS variable view to 200 rows in
an Excel file. Most of the labels get multiple rows when I paste to Excel.
Is there a way to copy, one to one, between SPSS and Excel?

Any suggestions are most welcome. Thank you.

Stay safe, Steve Salbod (Pace University)






--
Sent from: http://spssx-discussion.1045642.n5.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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting Variable Labels to Excel

Bruce Weaver
Administrator
Steve, did you see this thread?  The approach shown there might give you what
you want.

http://spssx-discussion.1045642.n5.nabble.com/Exporting-var-names-as-Excel-columns-and-value-labels-as-Excel-drop-down-lists-td5739331.html




Salbod wrote

> I want to copy 200 variables labels from an SPSS variable view to 200 rows
> in
> an Excel file. Most of the labels get multiple rows when I paste to Excel.
> Is there a way to copy, one to one, between SPSS and Excel?
>
> Any suggestions are most welcome. Thank you.
>
> Stay safe, Steve Salbod (Pace University)
>
>
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
Sent from: http://spssx-discussion.1045642.n5.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
--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.
Reply | Threaded
Open this post in threaded view
|

Re: Exporting Variable Labels to Excel

Jon Peck
A simple way to  do this is to run DISPLAY DICT; then hide the unwanted columns, and then select the Variable Information table and export to Excel.

DISPLAY DICT.
SPSSINC MODIFY TABLES SUBTYPE='Variable Information'
SELECT=0 2 3 4 5 6 7 8 DIMENSION= COLUMNS
PROCESS = ALL HIDE=TRUE.
/STYLES  APPLYTO=BOTH.

MODIFY TABLES is an extension command that you can install via Extensions > Extension Hub if you don't already have it.
With a select specification, the OUTPUT EXPORT dialog doesn't provide a Paste button, but you can do that part interactively or use the SPSSINC MODIFY OUTPUT extension command with the supplied export tables to Excel custom function.

On Wed, Jul 29, 2020 at 7:06 PM Bruce Weaver <[hidden email]> wrote:
Steve, did you see this thread?  The approach shown there might give you what
you want.

http://spssx-discussion.1045642.n5.nabble.com/Exporting-var-names-as-Excel-columns-and-value-labels-as-Excel-drop-down-lists-td5739331.html




Salbod wrote
> I want to copy 200 variables labels from an SPSS variable view to 200 rows
> in
> an Excel file. Most of the labels get multiple rows when I paste to Excel.
> Is there a way to copy, one to one, between SPSS and Excel?
>
> Any suggestions are most welcome. Thank you.
>
> Stay safe, Steve Salbod (Pace University)
>
>
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
Sent from: http://spssx-discussion.1045642.n5.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


--
Jon K Peck
[hidden email]

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting Variable Labels to Excel

Salbod
Hi Jon,

Thank you. Your solution worked but left me puzzled.

The presenting problem arose because I used Qualtrics to downloaded an SPSS
file; that file had variable labels with line breaks. executing your code, I
was able to paste the variable name and variable label to an Excel file. At
that point, I was able to delete the line breaks (replace with Ctrl+j). Your
code worked perfectly, but I had no idea what it was doing. Is there
documentation available for SPSSINC MODIFY TABLES? I can see using this
procedure in the future.

Thank you, Steve





--
Sent from: http://spssx-discussion.1045642.n5.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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting Variable Labels to Excel

Jon Peck
I like to keep the smoke and mirrors working.

Extension commands such as SPSSINC MODIFY OUTPUT are not documented in the CSR. The dialog box help (Utilities > Modify Output Titles in this case), explains most of the command features.  Syntax help is available by pressing F1 on an instance of the command or running the command with the Help subcommand, e.g.,
SPSSINC MODIFY OUTPUT /HELP.

This command and several extensions can accept little Python plugin functions to extend their capabilities.  MODIFY OUTPUT includes the file customoutputfunctions.py that contains a few such functions.  Those functions are documented in the .py file, which you can open in a plain text editor.  Users can also write their own plugin functions if they know a little Python.  For the excelexport function, this is the doc in the file.

# This function exports selected items to Excel.
# Examples:

# Export a custom table from the most recent command to a new Excel file.
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=PRECEDING
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls')".

# Export all the custom tables in the Viewer to separate sheets named table1, table2, ...
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=ALL
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
#  sheet='table#',action='CreateWorksheet')".

# Export all custom tables to separate files named extest1,xls, extest2, ...
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=ALL
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest#.xls')

# If charts, models, or tree images are included in the selection, they will go to separate files
# with base name filename_sheetname modified as with other items by use of # in either part.

# This function differs from the OUTPUT EXPORT command in being able to select items
# via the criteria of the command and in how the output files are structured.  Also, it cannot
# put graphical items into the target spreadsheet.  They are written to separate files

And, if you are determined to go deeper, you can read the source code.

On Sat, Aug 8, 2020 at 10:13 AM Salbod <[hidden email]> wrote:
Hi Jon,

Thank you. Your solution worked but left me puzzled.

The presenting problem arose because I used Qualtrics to downloaded an SPSS
file; that file had variable labels with line breaks. executing your code, I
was able to paste the variable name and variable label to an Excel file. At
that point, I was able to delete the line breaks (replace with Ctrl+j). Your
code worked perfectly, but I had no idea what it was doing. Is there
documentation available for SPSSINC MODIFY TABLES? I can see using this
procedure in the future.

Thank you, Steve





--
Sent from: http://spssx-discussion.1045642.n5.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


--
Jon K Peck
[hidden email]

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting Variable Labels to Excel

Jon Peck
I wrote about MODIFY OUTPUT, but I see the message referred to SPSSINC MODIFY TABLES.  Much the same story, though, and it also has a file of custom plugin functions in this case named customstylefunctions.py.

On Sat, Aug 8, 2020 at 11:55 AM Jon Peck <[hidden email]> wrote:
I like to keep the smoke and mirrors working.

Extension commands such as SPSSINC MODIFY OUTPUT are not documented in the CSR. The dialog box help (Utilities > Modify Output Titles in this case), explains most of the command features.  Syntax help is available by pressing F1 on an instance of the command or running the command with the Help subcommand, e.g.,
SPSSINC MODIFY OUTPUT /HELP.

This command and several extensions can accept little Python plugin functions to extend their capabilities.  MODIFY OUTPUT includes the file customoutputfunctions.py that contains a few such functions.  Those functions are documented in the .py file, which you can open in a plain text editor.  Users can also write their own plugin functions if they know a little Python.  For the excelexport function, this is the doc in the file.

# This function exports selected items to Excel.
# Examples:

# Export a custom table from the most recent command to a new Excel file.
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=PRECEDING
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls')".

# Export all the custom tables in the Viewer to separate sheets named table1, table2, ...
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=ALL
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
#  sheet='table#',action='CreateWorksheet')".

# Export all custom tables to separate files named extest1,xls, extest2, ...
#SPSSINC MODIFY OUTPUT TABLES
#/IF SUBTYPE="'Custom Table'" PROCESS=ALL
#/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest#.xls')

# If charts, models, or tree images are included in the selection, they will go to separate files
# with base name filename_sheetname modified as with other items by use of # in either part.

# This function differs from the OUTPUT EXPORT command in being able to select items
# via the criteria of the command and in how the output files are structured.  Also, it cannot
# put graphical items into the target spreadsheet.  They are written to separate files

And, if you are determined to go deeper, you can read the source code.

On Sat, Aug 8, 2020 at 10:13 AM Salbod <[hidden email]> wrote:
Hi Jon,

Thank you. Your solution worked but left me puzzled.

The presenting problem arose because I used Qualtrics to downloaded an SPSS
file; that file had variable labels with line breaks. executing your code, I
was able to paste the variable name and variable label to an Excel file. At
that point, I was able to delete the line breaks (replace with Ctrl+j). Your
code worked perfectly, but I had no idea what it was doing. Is there
documentation available for SPSSINC MODIFY TABLES? I can see using this
procedure in the future.

Thank you, Steve





--
Sent from: http://spssx-discussion.1045642.n5.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


--
Jon K Peck
[hidden email]



--
Jon K Peck
[hidden email]

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