Pasting SPSS tables in Excel - incorrect column label positioning

[Mark Lenel]

Hi everyone,
 
I have a problem with pasting SPSS tables into Excel.
 
Within a single table I have a combination of single-variable column
headers (e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and
nested-variable column headers (e.g. 'Wave1 > Men').  Therefore the
total column requires only one row for the column label, whereas the
other columns would require 2 or more rows for the column labels.  The
CTABLES command might include the following subcommand:
 
/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])
 
However, when I paste the table from SPSS output into Excel, the
position of all the nested column labels goes completely wrong; they
begin to appear in the earlier, single-variable colums.  Oddly enough,
whilst this error appears in v15, it does not happen in v12, suggesting
to me it's something to do with the way the information is copied to the
clipboard in the first place.
 
Has anyone come across this before, any suggestions as to why it's
happening, or any solutions to the problem?
 
Many thanks in advance,
 
Mark
 

[Peck, Jon]

This problem occurs with certain types of table structures.  You can evade it by using the SaxBasic script in the Excel Export item that you can download from SPSS Developer Central (www.spss.com/devcentral) instead of the built-in export.  (Click the Downloads link on the main page to find it.)  This script, which raises a dialog similar to the built-in export dialog, fixes the table-structure problem and offers a few extra features.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Mark Lenel
Sent: Thursday, September 27, 2007 5:54 AM
To: [hidden email]
Subject: [SPSSX-L] Pasting SPSS tables in Excel - incorrect column label positioning

Hi everyone,

I have a problem with pasting SPSS tables into Excel.

Within a single table I have a combination of single-variable column
headers (e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and
nested-variable column headers (e.g. 'Wave1 > Men').  Therefore the
total column requires only one row for the column label, whereas the
other columns would require 2 or more rows for the column labels.  The
CTABLES command might include the following subcommand:

/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])

However, when I paste the table from SPSS output into Excel, the
position of all the nested column labels goes completely wrong; they
begin to appear in the earlier, single-variable colums.  Oddly enough,
whilst this error appears in v15, it does not happen in v12, suggesting
to me it's something to do with the way the information is copied to the
clipboard in the first place.

Has anyone come across this before, any suggestions as to why it's
happening, or any solutions to the problem?

Many thanks in advance,

Mark

[Luca Meyer]

I had the same issue a while ago. I do not why it was occuring but I found
out a solution by exporting the tables into HTM and from there into EXCEL -
but I recall that any decimal after the first digit was automatically
rounded while doing so.

HTH,
Luca

Mr. Luca MEYER
Market research, data analysis & more
www.lucameyer.com - Tel: [phone]


-----Messaggio originale-----
Da: SPSSX(r) Discussion [mailto:[hidden email]] Per conto di Mark
Lenel
Inviato: giovedì 27 settembre 2007 12.54
A: [hidden email]
Oggetto: Pasting SPSS tables in Excel - incorrect column label positioning

Hi everyone,
 
I have a problem with pasting SPSS tables into Excel.
 
Within a single table I have a combination of single-variable column headers
(e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and nested-variable
column headers (e.g. 'Wave1 > Men').  Therefore the total column requires
only one row for the column label, whereas the other columns would require 2
or more rows for the column labels.  The CTABLES command might include the
following subcommand:
 
/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])
 
However, when I paste the table from SPSS output into Excel, the position of
all the nested column labels goes completely wrong; they begin to appear in
the earlier, single-variable colums.  Oddly enough, whilst this error
appears in v15, it does not happen in v12, suggesting to me it's something
to do with the way the information is copied to the clipboard in the first
place.
 
Has anyone come across this before, any suggestions as to why it's
happening, or any solutions to the problem?
 
Many thanks in advance,
 
Mark
 

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.31/1031 - Release Date: 26/09/2007
12.12
 

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.31/1031 - Release Date: 26/09/2007
12.12
 

[ViAnn Beadle]

The offset issue probably is a problem caused by cell spanning not being
handled correctly on copy. Excel doesn't see the span to insert a space
cell.

When you export to HTML, numbers are just text as it appears in the table
cell. When you export to Excel numbers are floating-point binary and retain
their precision. You're intermediate format in HTML lost all the precision.

Copied numbers on the clipboard are stored in both text format and floating
point format and the destination application picks up its preferred format.
You'll see all the formats that the destination application can read by
using Paste Special in the destination application.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Luca Meyer
Sent: Thursday, September 27, 2007 11:41 AM
To: [hidden email]
Subject: R: Pasting SPSS tables in Excel - incorrect column label
positioning

I had the same issue a while ago. I do not why it was occuring but I found
out a solution by exporting the tables into HTM and from there into EXCEL -
but I recall that any decimal after the first digit was automatically
rounded while doing so.

-----Messaggio originale-----

Hi everyone,

I have a problem with pasting SPSS tables into Excel.

Within a single table I have a combination of single-variable column headers
(e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and nested-variable
column headers (e.g. 'Wave1 > Men').  Therefore the total column requires
only one row for the column label, whereas the other columns would require 2
or more rows for the column labels.  The CTABLES command might include the
following subcommand:

/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])

However, when I paste the table from SPSS output into Excel, the position of
all the nested column labels goes completely wrong; they begin to appear in
the earlier, single-variable colums.  Oddly enough, whilst this error
appears in v15, it does not happen in v12, suggesting to me it's something
to do with the way the information is copied to the clipboard in the first
place.

[Raffe, Sydelle, SSA]

I drag my output tables to excel which seems to preserve the table structure.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]]On Behalf Of
Peck, Jon
Sent: Thursday, September 27, 2007 5:08 AM
To: [hidden email]
Subject: Re: Pasting SPSS tables in Excel - incorrect column label
positioning


This problem occurs with certain types of table structures.  You can evade it by using the SaxBasic script in the Excel Export item that you can download from SPSS Developer Central (www.spss.com/devcentral) instead of the built-in export.  (Click the Downloads link on the main page to find it.)  This script, which raises a dialog similar to the built-in export dialog, fixes the table-structure problem and offers a few extra features.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Mark Lenel
Sent: Thursday, September 27, 2007 5:54 AM
To: [hidden email]
Subject: [SPSSX-L] Pasting SPSS tables in Excel - incorrect column label positioning

Hi everyone,

I have a problem with pasting SPSS tables into Excel.

Within a single table I have a combination of single-variable column
headers (e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and
nested-variable column headers (e.g. 'Wave1 > Men').  Therefore the
total column requires only one row for the column label, whereas the
other columns would require 2 or more rows for the column labels.  The
CTABLES command might include the following subcommand:

/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])

However, when I paste the table from SPSS output into Excel, the
position of all the nested column labels goes completely wrong; they
begin to appear in the earlier, single-variable colums.  Oddly enough,
whilst this error appears in v15, it does not happen in v12, suggesting
to me it's something to do with the way the information is copied to the
clipboard in the first place.

Has anyone come across this before, any suggestions as to why it's
happening, or any solutions to the problem?

Many thanks in advance,

Mark

[ViAnn Beadle]

Dragging is a shortcut for copy/paste. YMMV depending upon:

1--the structure of the table in SPSS
2--the version of SPSS you are using
3--the version of Excel/Word/whatever you are using

For example, I am running SPSS 16 and Excel 2007 on Windows Vista. I cannot drag a table from the output viewer directly into Excel. I can, however, copy a table and paste it into Excel. If my table has spanning, as do many tables in SPSS, sometimes I lose columns or rows. If I paste plain text I get the missing row or column back but then lose the floating point precision. I can even copy the table as picture into Excel but then it's useless to do anything else with it there.

I'll also get slightly different results depending upon whether I copied the cells in activated table or the entire table in the viewer.

Inter-application support of the clipboard is really dicey--export is bit more predictable.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Raffe, Sydelle, SSA
Sent: Thursday, September 27, 2007 1:01 PM
To: [hidden email]
Subject: Re: Pasting SPSS tables in Excel - incorrect column label positioning

I drag my output tables to excel which seems to preserve the table structure.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]]On Behalf Of
Peck, Jon
Sent: Thursday, September 27, 2007 5:08 AM
To: [hidden email]
Subject: Re: Pasting SPSS tables in Excel - incorrect column label
positioning


This problem occurs with certain types of table structures.  You can evade it by using the SaxBasic script in the Excel Export item that you can download from SPSS Developer Central (www.spss.com/devcentral) instead of the built-in export.  (Click the Downloads link on the main page to find it.)  This script, which raises a dialog similar to the built-in export dialog, fixes the table-structure problem and offers a few extra features.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Mark Lenel
Sent: Thursday, September 27, 2007 5:54 AM
To: [hidden email]
Subject: [SPSSX-L] Pasting SPSS tables in Excel - incorrect column label positioning

Hi everyone,

I have a problem with pasting SPSS tables into Excel.

Within a single table I have a combination of single-variable column
headers (e.g. 'Total all waves', 'Wave 1', 'Wave 2', etc.) and
nested-variable column headers (e.g. 'Wave1 > Men').  Therefore the
total column requires only one row for the column label, whereas the
other columns would require 2 or more rows for the column labels.  The
CTABLES command might include the following subcommand:

/TABLE q7d_1 [C][COLPCT]  BY Total [C] + SurveyWave [C] + SurveyWave [C]
> (Gender [C] + AgeGroup [C])

However, when I paste the table from SPSS output into Excel, the
position of all the nested column labels goes completely wrong; they
begin to appear in the earlier, single-variable colums.  Oddly enough,
whilst this error appears in v15, it does not happen in v12, suggesting
to me it's something to do with the way the information is copied to the
clipboard in the first place.

Has anyone come across this before, any suggestions as to why it's
happening, or any solutions to the problem?

Many thanks in advance,

Mark